Average without the 0s

[Guest]

I have a running total for several store's sales numbers that they report
daily. I am trying to add a page for the entire company combined and include
an average sales month-to-date. However when I enter the formula to add all
stores I get a zero value for the dates that have yet to come. Then when I
ask for the average for the whole company, I get a number that includes those
zero values. So far I have just been extending my cell range for the average
equation to include each day as it occurs and not the zeros, but I would like
to just pull the infomation from my daily reports and do the company total on
its own. Any way around this?

 

[Don Guillett]

This is an array formula that must be entered/edited using ctrl+shift+enter

=AVERAGE(IF(F2:F22>0,F2:F22))

 

[Jim Rech]

Another way:

=SUM(A1:A5)/(COUNTIF(A1:A5,">0"))

--
Jim
|I have a running total for several store's sales numbers that they report
| daily. I am trying to add a page for the entire company combined and
include
| an average sales month-to-date. However when I enter the formula to add
all
| stores I get a zero value for the dates that have yet to come. Then when I
| ask for the average for the whole company, I get a number that includes
those
| zero values. So far I have just been extending my cell range for the
average
| equation to include each day as it occurs and not the zeros, but I would
like
| to just pull the infomation from my daily reports and do the company total
on
| its own. Any way around this?

 

[Guest]

use AVERAGE rather than SUM.

For example if you will have data in A1 thru A100, then
=SUM(A1:A100)/100 will calculate an average including blanks treated as zeros.
=AVERAGE(A1:A100) will calculate an average excluding blanks.

 

[Jim Rech]

I meant:

=SUM(A1:A5)/(COUNTIF(A1:A5,"<>0"))


--
Jim
|I have a running total for several store's sales numbers that they report
| daily. I am trying to add a page for the entire company combined and
include
| an average sales month-to-date. However when I enter the formula to add
all
| stores I get a zero value for the dates that have yet to come. Then when I
| ask for the average for the whole company, I get a number that includes
those
| zero values. So far I have just been extending my cell range for the
average
| equation to include each day as it occurs and not the zeros, but I would
like
| to just pull the infomation from my daily reports and do the company total
on
| its own. Any way around this?

 

[Guest]

That won't work, because I have 0s in the range. I need the average without
the days that haven't occured yet included.

 

[Guest]

The formula seems like it should work, but when I enter it it says it
contains an error. I triple checked to make sure I was entering it correctly,
so that is not the problem. This is the formula I entered:

=SUM(Z4,Z6:Z10,Z12:Z16,Z18:Z22,Z24:Z28)/(COUNTIF(Z4,Z6:Z10,Z12:Z16,Z18:Z22,Z24:Z28),"<>0"))

Could it have anything to do with the fact that my range is not contiuous,
but broken down into weekly totals as well?

Thanks a lot! I feel like at least I'm on the right track to figuring this
out

 

[Guest]

I tried this one but kept getting errors. I am not exactly sure what you
meant by using the ctrl+shift+enter. Sorry and thanks for your help!

 

[Dave Peterson]

Select the cell with the formula
Hit F2 to edit the cell.
Hit ctrl-shift-enter instead of enter.

If you do it correctly, excel will wrap curly brackets {} around your formula.
(don't type them yourself.)

 

[Guest]

Okay, that is what I did and I still got an error. Here is the formula I
tried to enter.

=AVERAGE(IF(V4,V6:V10,V12:V16,V18:V22,V24:V28>0,V4,V6:V10,V12:V16,V18:V22,V24:V28))

Could the problem have anything to do with using several seperate ranges as
opposed to one single range? I have the numbers broke up by weeks.

 

[Dave Peterson]

What's in V5, V11, ...

Maybe you can just try the other suggestion???

 

[Jim Rech]

Unfortunately COUNTIF does not seem to support multiple area ranges. If the
cells inbetween have non-zero values, so that you cannot simply use the
range Z4:Z28 then you may have to use sub-formulas for each area and sum to
get the total.

 

[Guest]

O.K. Try this UDF. It will calculate the average of a range of cells and
exclude both blanks and zeros:

Function avrg(r As Range)
avrg = 0
Count = 0
For Each rr In r
v = rr.Value
avrg = avrg + v
If v <> "" Then
If v <> 0 Then
Count = Count + 1
End If
End If
Next

If Count = 0 Then Count = 1
avrg = avrg / Count
End Function

 

[Don Guillett]

Correct to suit and array enter
=AVERAGE(IF(SMALL((V4:V6,V8,V23),ROW(INDIRECT("1:"&COUNT(V4:V6,V8,V23))))>0,SMALL((V4:V6,V8,V23),ROW(INDIRECT("1:"&COUNT(V4:V6,V8,V23))))))

 

[Don Guillett]

Correct to suit and array enter
=AVERAGE(IF(SMALL((V4:V6,V8,V23),ROW(INDIRECT("1:"&COUNT(V4:V6,V8,V23))))>0,SMALL((V4:V6,V8,V23),ROW(INDIRECT("1:"&COUNT(V4:V6,V8,V23))))))

 

[Guest]

THANK YOU SO MUCH!!! THAT WORKED LIKE A CHARM AND WILL SAVE ME A GOOD 30
MINUTES EACH MORNING!!!!

 

[Don Guillett]

Glad to help.
Send Wild Turkey 101