Question: In Microsoft Excel 2003/XP/2000/97, I want to average the values in column E when the corresponding value in column D is between 5 and 9.
How can I do this?
Answer: You can do this with an array formula.
Let's look at an example.
In cell B1, we want to average the values in column E when the value in column D is between 5 and 9. To do this, we've created the following array formula:
=SUM((D2:D8>4)*(D2:D8<10)*(E2:E8))/SUM((D2:D8>4)*(D2:D8<10))
When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:
{=SUM((D2:D8>4)*(D2:D8<10)*(E2:E8))/SUM((D2:D8>4)*(D2:D8<10))}
The first part of the formula SUM((D2:D8>4)*(D2:D8<10)*(E2:E8)) will sum all of the values in column E when the corresponding value in column D is between 5 and 9.
The second part of the formula SUM((D2:D8>4)*(D2:D8<10)) will count the number of values in column D that are between 5 and 9.
When you divide the two, it gives us an average of the values in column E when the value in column D is between 5 and 9.
