substitute a blank cell in a SUM function range with another cell that has a value

[4653]

I have 11 cells that are part of my question, A1, C1, C2, C3, C4, C5, E1, E2, E3, E4 and E5. A user will have an option to input an arbitrary value such as 200 or 400 into cells A1, C1, C2, C3, C4 and C5. The user must always enter a value into cell A1, however, each cell C1 through C5 will be optional (meaning they could enter a value into C1, C2 and C4 but leave C3 and C5 blank, or any combination possible). The values for cells E1 through E5 are already calculated via another formula and the user has no control over these values. What I need is a formula that will add cells C1 through C5 and divide it by the sum of E1 through E5. However, for each cell in C1 through C5 that is blank, the formula needs to use the value in A1 to calculate the answer. So using my previous example, the answer would turn out to be (C1+C2+A1+C4+A1)/(E1:E5). Any help would be much appreciated.

 

[Aladin Akyurek]

I have 11 cells that are part of my question, A1, C1, C2, C3, C4, C5, E1, E2, E3, E4 and E5. A user will have an option to input an arbitrary value such as 200 or 400 into cells A1, C1, C2, C3, C4 and C5. The user must always enter a value into cell A1, however, each cell C1 through C5 will be optional (meaning they could enter a value into C1, C2 and C4 but leave C3 and C5 blank, or any combination possible). The values for cells E1 through E5 are already calculated via another formula and the user has no control over these values. What I need is a formula that will add cells C1 through C5 and divide it by the sum of E1 through E5. However, for each cell in C1 through C5 that is blank, the formula needs to use the value in A1 to calculate the answer. So using my previous example, the answer would turn out to be (C1+C2+A1+C4+A1)/(E1:E5). Any help would be much appreciated.

Looks like...

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(C1:C5),C1:C5,A1))/SUM(E1:E5)

Adjust to suit.

 

[4653]

Works perfect! Thank you so much!

 

[Aladin Akyurek]

Works perfect! Thank you so much!

You are welcome. Thanks for providing feedback.