Excel - Skipping a cell when calculating a total

December 2016




Issue


I have a series of codes back and forth it seems a bit insane.
but what I'm wanting is for cell b1 c1 d1 e1 and etc to add together which I have the following for this to return as my average

it looks like this >>>>
 =SUM(AVERAGE(B11:H11))/SUM(AVERAGE($B$5:$H$5)) 


now the only problem I'm running into is if b1 has a number in it but c1:e1 does not have numbers

i want them to refer to another cell that dose have number while still viewing as blank

but excluding the blank cells within the final number

I'm working with %'s

any way its not working to well for me but this is the closest i have gotten to it working

=SUM(IF(B9="",B9,$B$5),IF(C9="",C9,$C$5),IF(D9="",D9,$D$5),IF(E9="",E9,$E$5),IF(F9="",F9,$F$5),IF(G9="",G9,$G$5),IF(H9="",H9,$H$5)/SUM($B$5:$H$5))


but the final % is way off.. and I'm not sure if it is truly not counting the blank cells

ANY HELP WOULD BE GREATLY APPRECIATED

Solution


You can use these formulas:

=SUM(B6:H6)/SUMPRODUCT((B$2:H$2) * (B6:H6<>"")) 


or

=SUM(B6:H6)/SUMIF(B6:H6,"<>",B$2:H$2) 


This finds the average based on the cells that are populated.

Note


Thanks to rizvisa for this tip on the forum.

Related :

This document entitled « Excel - Skipping a cell when calculating a total » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.