Hi

I am having a problem with excel not displaying a calculated result correctly in the cell. I am pretty sure I have the formula correct but excel is giving an incorrect answer. I am calculating the average of range of data greater than a certain value and less than another.

When I check the formula in the formula assistant it gives the correct result but displays a zero/incorrect result in the spreadsheet. See the example below.

A B C D E
1 5 0 5 12
2 6 5 10 0
3 7 10 15 0
4 8
5 9
6 10
7 11
8 12
9 13
10 14
11 15
12 16
13 17
14 18
15 19

The formula is =AVERAGE(IF(($A$1:$A$15$C1)*($A$1:$A$15<=$D1),$B$ 1:$B$15))

See the attached snap shot of the formula assistant. The correct result is 7 but it displaying 12 in the spreadsheet.

Any ideas, or have I made a mistake somewhere?

Hi,

Am Fri, 24 Oct 2014 16:07:54 +0100 schrieb JWEV:

=AVERAGE(IF(($A$1:$A$15$C1)*($A$1:$A$15<=$D1),$B$ 1:$B$15))

this formula is an array formula to insert with CTRL+Shift+Enter
Then you get the correct result


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks Claus, sorted the problem out 100%. You saved me a lot of frustration. Just don't quite understand why you have to use Ctrl+Shift+Enter for array formulas?