conditionally average excel data columns

Question

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.

Answer 1

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

Answer 2

The formula you are looking for is AVERAGEIF I think.

Something like this:

=AVERAGEIF(R2C1:R9C1,">=6",R2C2:R9C2)
=AVERAGEIF($A$2:$A$9,">=6",$B$2:$B$9)