Average without the 0s

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
This is an array formula that must be entered/edited using ctrl+shift+enter

=AVERAGE(IF(F2:F22>0,F2:F22))
 
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?
 
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.
 
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?
 
That won't work, because I have 0s in the range. I need the average without
the days that haven't occured yet included.
 
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 :)
 
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!
 
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.)
 
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.
 
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.
 
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
 
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))))))
 
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))))))
 
THANK YOU SO MUCH!!! THAT WORKED LIKE A CHARM AND WILL SAVE ME A GOOD 30
MINUTES EACH MORNING!!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Averages 7
Help! Need to ignore 0s and blank cells in average formula! 2
Problem with average 2
Averaging across worksheets 1
Average of Subtotals in Pivot Table 0
Finding the Average 4
Daily Average 6
min,average>0 if 52 cells read zero 2

Back
Top