Excel - Skipping a cell when calculating a total

March 2017




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


Published by aakai1056.
This document, titled "Excel - Skipping a cell when calculating a total," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).