Prorated Average
 

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!

Prorated Average
 

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




Sunbronx wrote:

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!

Prorated Average
 

=AVERAGE(A1:E1)*5

--
HTH

Bob Phillips

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

"Sunbronx" wrote in message
...
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!