Average array formula to conitinue calculating even if one of cells or many are empty

[wellous]

Hello All,

I have a formula of average array which is working good, but if one of cells or more are empty it won't work at all

the formula

Please Login or Register  to view this content.

i would like to be able to calculate the average even if cell (B16) and or (C16) and or (D16)...etc are blank cells
Any idea?

Thanks in advance

[Pete_UK]

As you are using XL2010, you can use the AVERAGEIFS function - no need for an array function, and it will ignore blank cells.

Hope this helps.

Pete

[wellous]

Thanks Pete, yes i use XL2010
i did it like that : =AVERAGEIFS(SOURCE!N2:N21900,SOURCE!A2:A21900,SOURCE!A2:A21900=B16,SOURCE!B2:B21900,SOURCE!B2:B21900=C16)
but it doesn't work!
N2:N21900 ... is the Average_range,
please help,
thanks

[Tony Valko]

As you are using XL2010, you can use the AVERAGEIFS function - no need for an array function, and it will ignore blank cells.

I think they meant if the criteria cells are empty just ignore them.

This monster array formula** should do it:

=AVERAGE(IF((IF(B16="",Source!A2:A21637=Source!A2:A21637,Source!A2:A21637=B16))*(IF(C16="",Source!B2:B21637=Source!B2:B21637,Source!B2:B21637=C16))*(IF(D16="",Source!C2:C21637=Source!C2:C21637,Source!C2:C21637=D16))*(IF(E16="",Source!D2:D21637=Source!D2:D21637,Source!D2:D21637=E16)),Source!N2:N21637))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[wellous]

THANKS Tony, this is exactly what i want

[Tony Valko]

You're welcome. Thanks for the feedback!

[wellous]

Hello Tony,
Can we make same formula to ignore cells with 0 while calculating the average from the average range?
something like "<>0" with AverageIF ?

thanks a lot

[Tony Valko]

Like this...

=AVERAGE(IF((IF(B16="",Source!A2:A21637=Source!A2:A21637,Source!A2:A21637=B16))*(IF(C16="",Source!B2:B21637=Source!B2:B21637,Source!B2:B21637=C16))*(IF(D16="",Source!C2:C21637=Source!C2:C21637,Source!C2:C21637=D16))*(IF(E16="",Source!D2:D21637=Source!D2:D21637,Source!D2:D21637=E16))*(Source!N2:N21637<>0),Source!N2:N21637))

Still array entered.

[wellous]

wow, perfect,
thanks a ton

[Tony Valko]

You're welcome!