MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Data Summary


Posted by Analog Guy on March 01, 2001 11:32 AM

I'm not sure how to average certain data in a column
while ignoring 'blank' lines? I tried the command
SUMIF($B$1:$B$5,"a",C$1:C$5)/COUNTIF($B$1:$B$5,"a").

The SUMIF gives me 9 which is OK, but the COUNTIF
gives me 3 which is not OK!

COL B COL C
1 a 5
2 b 4
3 a
4 a 4
5 d 6

I only want to average non-blank numbers relating to
'a' values. Example: I want above average to give
me (5+4)/2=4.5 and not (5+4)/3=3!!!

Can anyone help?


Posted by Mark W. on March 01, 2001 12:02 PM

{=AVERAGE(IF((A1:A5="a")*ISNUMBER(B1:B5),B1:B5,""))}

Posted by Analog Guy on March 02, 2001 7:19 AM

Thanks for the formula ... but I can't seem to
get it to work ... I just get #VALUE! back?

Do I have to enter the formula in a special way?

Posted by Analog Guy on March 02, 2001 7:37 AM

Sorry about the last post ... I finally figured it
out ... I need to enter CTRL-SHIFT-ENTER for array
formulae. Never used those before ... you do learn
something new everyday!!

Thanks ... the formula worked perfectly!!!