average(if not working properly

[Gaurav]

Hi All,

I have this formula {=AVERAGE(IF($A$10:$A$5066=A6,B$10:B$5066))}. As far as
my knowledge goes, i do not see anything wrong with the formula but why is
calculating a different average than the normal
=AVERAGE(number1,number2,number3....) formula?

as per the current figures I have the result as 230.21 but when i calculate
the same manually with the normal average formula, it gives me 278.17. I
figured its not working properly when i entered 1 in one of the cells and
the average went up instead of going down.

Help required.

Thanks
Gaurav

 

[ryguy7272]

Use this:
=IF(A10:A15=A6,AVERAGE(B10:B15),"")
Ctrl+Shift+Enter


To see how Excel handles calculations, functions, etc. click on Tools >
Formula Auditing > Evaluate Formula


Regards,
Ryan---

 

[Jim Cone]

Your formula only averages numbers in Column B where
the adjacent value in Column A is equal to the value in Cell A6.
'--

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Gaurav"
wrote in message
Hi All,
I have this formula {=AVERAGE(IF($A$10:$A$5066=A6,B$10:B$5066))}. As far as
my knowledge goes, i do not see anything wrong with the formula but why is
calculating a different average than the normal
=AVERAGE(number1,number2,number3....) formula?

as per the current figures I have the result as 230.21 but when i calculate
the same manually with the normal average formula, it gives me 278.17. I
figured its not working properly when i entered 1 in one of the cells and
the average went up instead of going down.
Help required.
Thanks
Gaurav

 

[Gaurav]

it gives me a blank.

Use this:
=IF(A10:A15=A6,AVERAGE(B10:B15),"")
Ctrl+Shift+Enter


To see how Excel handles calculations, functions, etc. click on Tools >
Formula Auditing > Evaluate Formula


Regards,
Ryan---

 

[Gaurav]

Yes Jim, but it is not giving me the correct figure.

 

[ryguy7272]

I think you have to play with the range a little; change the
references...when I did it it I changes the range from your example of 5066
rows to a tiny 15 rows (just to make it a little easier to work with).
=IF(A10:A5066=A6,AVERAGE(B10:B5066),"")

Try it again.

Regards,
Ryan--

 

[Jim Cone]

Try checking the results with this formula...
=SUMIF(A10:A5066,A6,B10:B5066)/COUNTIF(A10:A5066,A6)
(not an array formula)

'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Gaurav"
wrote in message
Yes Jim, but it is not giving me the correct figure.

 

[Gaurav]

okay....this is weird...this formula is giving me a different figure now.
why is this happening??