average(if not working properly
 

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

average(if not working properly
 

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---

--
RyGuy


"Gaurav" wrote:

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

average(if not working properly
 

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

average(if not working properly
 

it gives me a blank.


"ryguy7272" wrote in message
...
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---

--
RyGuy


"Gaurav" wrote:

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

average(if not working properly
 

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


"Jim Cone" wrote in message
...

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

average(if not working properly
 

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--

--
RyGuy


"Gaurav" wrote:

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


"Jim Cone" wrote in message
...

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

average(if not working properly
 

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.

average(if not working properly
 

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


"Jim Cone" wrote in message
...

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.