Prorated Average

[Guest]

I have a forumla to find a sum of a set of numbers looking something like this:
=SUM(A1, B1, C1, D1, E1)

There are some cases where one of these may be blank (i.e., a missing value
for A1, B1, C1, D1, or E1 is missing).

I'm looking for a way to fill in that missing value with the average of the
values that aren't missing...so....

if E1 is missing, I want Excel to automatically replace it with the averge
of A1, B1, C1, and D1, and plug it back into the original equation to come up
with the SUM.

Any ideas?

Thanks!

 

[somethinglikeant]

Multiply SUM(a1,b2,etc) by the ratio of cells in the range to non zero
cells in the range

counta and countif(range,0) come in handy here

somethinglikeant

 

[Bob Phillips]

=AVERAGE(A1:E1)*5

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)