Average over non-existing numbers
 

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
--
But I will accept the rules that you feel necessary to your freedom. I am
free, no matter what rules surround me. If I find them tolerable, I
tolerate them; if I find them too obnoxious, I break them. I am free
because I know that I alone am morally responsible for everything I do.

Average over non-existing numbers
 

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" wrote:

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
--
But I will accept the rules that you feel necessary to your freedom. I am
free, no matter what rules surround me. If I find them tolerable, I
tolerate them; if I find them too obnoxious, I break them. I am free
because I know that I alone am morally responsible for everything I do.

Average over non-existing numbers
 

CSUS_CE_Student wrote:
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
--
Always listen to experts. They'll tell you what can't be done,
and why. Then do it.
-- Heinlein : Time Enough For Love

Average over non-existing numbers
 

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
--
Always listen to experts. They'll tell you what can't be done,
and why. Then do it.
-- Heinlein : Time Enough For Love

Average over non-existing numbers
 

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(B4:D4)/SUMPRODUCT((B4:D4<"")*1).

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

/Sune

"houghi" wrote:

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
--
Always listen to experts. They'll tell you what can't be done,
and why. Then do it.
-- Heinlein : Time Enough For Love

Average over non-existing numbers
 

Sune Fibaek wrote:
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(B4:D4)/SUMPRODUCT((B4:D4<"")*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
--
Always listen to experts. They'll tell you what can't be done,
and why. Then do it.
-- Heinlein : Time Enough For Love

Average over non-existing numbers
 

Sune Fibaek wrote:
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
--
Remind me to write an article on the compulsive reading of news. The
theme will be that most neuroses can be traced to the unhealthy habit
of wallowing in the troubles of five billion strangers. -- Heinlein

Average over non-existing numbers
 

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

/Sune

"houghi" wrote:

Sune Fibaek wrote:
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
--
Remind me to write an article on the compulsive reading of news. The
theme will be that most neuroses can be traced to the unhealthy habit
of wallowing in the troubles of five billion strangers. -- Heinlein