Mitigating blanks with Arrays AVERAGE(IF(

**nothingisthis** · 07-21-2009, 12:49 PM

Hi there -

I'm using an arrays AVERAGE(IF( to average several scores from one spreadsheet to another.

However, if I have 30 employees, 1 may have no score, and I don't want that no score to factor into the average, but with the above array, it does.

Specifically, I get the wrong average for Score A, but the right one for Overal Score.

Please see attached sample.

Any help is greatly appreciated!!

-Victoria

**contaminated** · 07-21-2009, 12:58 PM

Try this
=AVERAGE(IF(($G$6=$D$2:$D$19)*(B2:B19<>""),B2:B19))

**martindwilson** · 07-21-2009, 01:02 PM

wel you have something in the cell thats no score in col c thats why
but to ignore 0 values use
=AVERAGE(IF((G6=D2:D19)*(C2:C19>0),C2:C19))

**shg** · 07-21-2009, 01:04 PM

=AVERAGE(IF( ($G6 = $D$2:$D$19) * ISNUMBER(C$2:C$19), C$2:C$19)), array-entered.

**nothingisthis** · 07-21-2009, 01:17 PM

WOW! You guys are all sorts of smart! I can learn a ton from people here!

Thanks Vusal again, for helping me out!!

**contaminated** · 07-21-2009, 01:27 PM

If it meets your requirements, mark the thread as "SOLVED", and don't forget to add reputation by pressing blue scales on the right of your screen

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save