有条件地平均excel数据列 [英] conditionally average excel data columns

问题描述

我有一个健康项目电子表格，其中列出了患者正在服用的症状和药物。
这些实际上是列，但是我不知道如何在堆栈溢出中写一个列。

 症状drug1 drug2 
 7 150 0 
 6 150 0 
 7 150 0 
 6 150 0 
 2 75 25 
 3 75 25 
 2 75 25 
 3 75 25 
  

我需要帮助编写一个能够使症状平均所有日子的公式6级及以上，症状水平低于3级。然后将其应用到药物柱天。例如，在上述情况下，看起来像服用更多的药物1和更少的药物2时，症状水平更高。现在我手动地通过标记日期来手动执行。

由于症状可以由药物价值的最后x天确定，我希望药物价值也是X天的平均值。

解决方案

如果我正确理解你的话那么这绝对是可能的。以下是我能够创建的截图：

这给你每种药物的平均值，只有当它是> = 或< = 指定的症状级别。

公式如下， code> ENTER 打字后，您必须点击 CTRL + SHIFT + ENTER ：

  = AVERAGE（IF（$ B $ 1：$ I $ 1< = 3，B2：I2））
  

这是Excel中的样子：

如您所见，公式仅对满足要求的数据进行平均。对于这种条件函数的其他用途，可以替换逻辑测试（数字，字符串，日期等），函数也可以（ sum ，平均， min ， max ， count 等）

如果您尝试这样做，最后使用 #VALUE ，那么您跳过了重要的步骤！

输入公式后，HIT CTRL + SHIFT + ENTER p>

通过点击该键组合Excel知道您的表单中的数据应该是单元格或范围的数组，而不是单个单元格或单个范围。

查看此链接以获取有关Excel数组公式的更多信息

I have a health project spreadsheet that has a column of a symptoms and drugs a patient is taking. These are actually columns but I don't know how to write a column in stack overflow.

symptom   drug1    drug2
7         150       0
6         150       0
7         150       0
6         150       0
2         75        25
3         75        25
2         75        25
3         75        25

I need help writing a formula that will average all the days with a symptom level 6 and above vs all the days with a symptom level of 3 and below. Then apply it to the drug column days. For instance in the case above it looks like the symptom level is higher when taking more of drug1 and less of drug 2. Right now I am doing this by hand by marking the days manually.

Since the symptom may be determined by the last x days of the drug value I'd like the drug value to be the average of X days back also. I'm also doing this manually by going a few days back each time I mark the days.

解决方案

If I understand you correctly, then this is absolutely possible. Below is a screenshot of what I was able to create:

This gives you the averages for the amount of each drug, only when it is >= or <= the specified symptom level.

The formula is as follows, but instead of hitting ENTER after typing, you must hit CTRL + SHIFT + ENTER:

=AVERAGE(IF($B$1:$I$1<=3,B2:I2))

Here is what it looks like in Excel:

As you can see, the formula only averages the data that meet the requirement. For other uses of this conditional functions, the logic test can be replaced (numbers, strings, dates, etc) and the function can also(sum, average, min, max, count, etc.)

If you try this and end up with #VALUE then you skipped the important step!

After typing the formula, HIT CTRL+SHIFT+ENTER

By hitting that key combo Excel knows that the data in your form should be an array of either cells or ranges, instead of individual cells or individual ranges.

Check out this link for more info about Excel Array Formulas

这篇关于有条件地平均excel数据列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！