Average without the 0s
 

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?

Average without the 0s
 

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

=AVERAGE(IF(F2:F220,F2:F22))

--
Don Guillett
SalesAid Software

"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?

Average without the 0s
 

Another way:

=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?

Average without the 0s
 

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.
--
Gary''s Student - gsnu200732


"lbeemer" wrote:

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?

Average without the 0s
 

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?

Average without the 0s
 

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200732


"lbeemer" wrote:

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?

Average without the 0s
 

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?

Average without the 0s
 

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!

"Don Guillett" wrote:

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

=AVERAGE(IF(F2:F220,F2:F22))

--
Don Guillett
SalesAid Software

"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?

Average without the 0s
 

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.)

lbeemer wrote:

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!

"Don Guillett" wrote:

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

=AVERAGE(IF(F2:F220,F2:F22))

--
Don Guillett
SalesAid Software

"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?



--

Dave Peterson

Average without the 0s
 

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

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.)

lbeemer wrote:

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!

"Don Guillett" wrote:

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

=AVERAGE(IF(F2:F220,F2:F22))

--
Don Guillett
SalesAid Software

"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?



--

Dave Peterson

Average without the 0s
 

What's in V5, V11, ...

Maybe you can just try the other suggestion???

lbeemer wrote:

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

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.)

lbeemer wrote:

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!

"Don Guillett" wrote:

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

=AVERAGE(IF(F2:F220,F2:F22))

--
Don Guillett
SalesAid Software

"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?



--

Dave Peterson


--

Dave Peterson

Average without the 0s
 

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?

Average without the 0s
 

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


--
Gary''s Student - gsnu200732


"lbeemer" wrote:

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200732


"lbeemer" wrote:

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?

Average without the 0s
 

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?

Average without the 0s
 

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

"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?

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?

Average without the 0s
 

Glad to help.
Send Wild Turkey 101

--
Don Guillett
SalesAid Software

"lbeemer" wrote in message
...
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?