Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average doesn't average correctly? | Excel Discussion (Misc queries) | |||
average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |