## 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.

Published by

aakai1056.

Latest update on April 27, 2010 at 08:15 AM by aakai1056.