April 2018

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

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.

Thanks to rizvisa for this tip on the forum.

- Excel - Skipping a cell when calculating a total
- Excel Skipping a cell when calculating a tota (Solved) » Forum - Excel
- Skipping a cell when calculating a total » Forum - Office software
- Excel formula if cell contains text then return value in another cell (Solved) » Forum - Excel
- Excel filename in cell » How-To - Excel
- Excel VBA - Change cell color based on value (Solved) » Forum - Programming