Average over non-existing numbers

[houghi]

How do I get averages over non-existing numbers? e.g. I have the
following (Best viewed in a fixed font)

Week Average 1 2 3 4
Average 1 25 10 20 20
Average 2 33.3 30 30 40
Average 3 #### 20 25 30

Row 4 does the calculated average of row 2 and 3. Column 2 does the
calculated average of column C, D, E and F
B4 could do either.

What I would like is that it calculates the averages for all 4 weeks.
However as I do not have the results for week 4 yet, I get a nice
#### when I do an. :-(

Is there a way to disregard the non-existing numbers? I have looked and
now I do a dirty trick by having the week I am in, in a seperate fiels
and then calculate the averages from there. However that is not realy a
good option.

I have looked, but was not able to find a solution. I use Excel 2000.

houghi

 

[CSUS_CE_Student]

When i have a problem like this i just use and if statement like this

=IF(E2=0 & E3=0 & E4=0,0,(E2+E3+E4)/3)
i have this equasion in cell F2

i wasnt sure of the cells you were using but this is what i do. Im sure
there is a much better way to do this but im not sure how

using the if statement lets you bypass the error if there is no data in
those cells

Regards:
CSUS_CE_Student

 

[houghi]

When i have a problem like this i just use and if statement like this

=IF(E2=0 & E3=0 & E4=0,0,(E2+E3+E4)/3)
i have this equasion in cell F2

This might work if it was only the 4 week period. Unfortunatly I have 52
weeks in a year and I add information each week. So it will look like
this. (AX is just a guestimate.)

I also realized just now that it simply is most likely because I try to
get an average of empty cells. I use Excel 2000 (No option to change)
and that does not have COUNTNUM, otherwise I could do it that way. :-(

houghi

 

[houghi]

houghi wrote:
<snip>

OK. Some clearification. Sorry about the confusion.

Here a much simpeler example of what I have and of what I expect to have

wk1 wk2 wk3 Average
Store1 10 5 7.5
Store2 20 20
Calculated av. 15 5 #DIV/0! #DIV/0!
Wanted result 15 5 10

Explanation:
Week one both stores are open and have an income of 10 and 20, resulting
in an average of 15 per open store.
Week 2 only store one is open and has an income of 5, resulting in 5 on
average.
Week 3 both stores are closed, so no averages should be calculated
Store one is open week one and two, closed on week 3. Average is 7.5,
calculated correctly
Store 2 is open week 1 and average is 20 Calculated correctly
Now the averages of store one and two. For week 2 this is calulated
correctly, yet for week 3 it gives an error.

So it should only calculate the averages of the numbers that are there.
Again, I do not have COUNTNUM as I use Excel 2000 and have no choice in
the version I am using. :-(

Obviously this example is an extremely simple example. I have in reality
many, many more fields and deeper datamining going on.

What I can also not do is select the fields for the averages every week.
That would defeat the purpose and then calculating by hand would be much
faster.

80 stores and about 250 parameters per store would mean correcting it
for 2000 calculations. Not a serious option.

houghi

 

[Sune Fibaek]

Hi Houghi

If I'm not misunderstanding you need either one of these formulaes:

In the caclutated avg. row use IF(IFERROR(... to eliminate the error
messages. Example:
=IF(ISERROR(SUM(B2:B3)/COUNT(B2:B3));"";SUM(B2:B3)/COUNT(B2:B3)) will return
whatever value is the average of the two cells above or empty if there is an
error in the calculation. This will sort your problem of #DIV/0!

You could also use a sumproduct formula to select those values that are <>
"" (or whatever you chose as value_if_true in the IF-sentence). As in:
=SUM(B44)/SUMPRODUCT((B44<>"")*1).

Either way your average sale will be 10 in your example.

/Sune

 

[houghi]

Hi Houghi

If I'm not misunderstanding you need either one of these formulaes:

In the caclutated avg. row use IF(IFERROR(... to eliminate the error
messages. Example:
=IF(ISERROR(SUM(B2:B3)/COUNT(B2:B3));"";SUM(B2:B3)/COUNT(B2:B3)) will return
whatever value is the average of the two cells above or empty if there is an
error in the calculation. This will sort your problem of #DIV/0!

You could also use a sumproduct formula to select those values that are <>
"" (or whatever you chose as value_if_true in the IF-sentence). As in:
=SUM(B44)/SUMPRODUCT((B44<>"")*1).

Either way your average sale will be 10 in your example.

Thanks. Will try it tomorrow when I am back in the office.

houghi

 

[houghi]

Hi Houghi

If I'm not misunderstanding you need either one of these formulaes:

In the caclutated avg. row use IF(IFERROR(... to eliminate the error
messages. Example:
=IF(ISERROR(SUM(B2:B3)/COUNT(B2:B3));"";SUM(B2:B3)/COUNT(B2:B3)) will return
whatever value is the average of the two cells above or empty if there is an
error in the calculation. This will sort your problem of #DIV/0!

Works like a charm. Thanks.

houghi

 

[Sune Fibaek]

You'r welcome - great to have some feed-back!

/Sune