Thread
:
Average without the 0s
View Single Post
#
16
Posted to microsoft.public.excel.misc
lbeemer
external usenet poster
Posts: 7
Average without the 0s
THANK YOU SO MUCH!!! THAT WORKED LIKE A CHARM AND WILL SAVE ME A GOOD 30
MINUTES EACH MORNING!!!!
"Don Guillett" wrote:
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
SalesAid Software
"Jim Rech" wrote in message
...
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.
--
Jim
"lbeemer" wrote in message
...
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 :)
"Jim Rech" wrote:
I meant:
=SUM(A1:A5)/(COUNTIF(A1:A5,"<0"))
--
Jim
"lbeemer" wrote in message
...
|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?
Reply With Quote
lbeemer
View Public Profile
Find all posts by lbeemer