Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
S.O.S.....
Hello,
I have a spreadsheet set up with column A for the date (one day per row), and column D as the total daily takings. I want a formula that changes automatically when I enter each new daily amount, and if I enter a daily taking of $0, I don't want excel to include that day in the month's average. So I want it to regognise that the $0 day is not to be included, and only divide by say 29 days instead of 30 for June. Hope I've made sense...?? Thank you in anticipation. -- Regards, Sarah |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
S.O.S.....
Here's one way.
Try this array formula** : =AVERAGE(IF(TEXT(A2:A20,"mmmyyyy")="Jun2007",IF(D2 :D200,D2:D20))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Sarah" wrote in message ... Hello, I have a spreadsheet set up with column A for the date (one day per row), and column D as the total daily takings. I want a formula that changes automatically when I enter each new daily amount, and if I enter a daily taking of $0, I don't want excel to include that day in the month's average. So I want it to regognise that the $0 day is not to be included, and only divide by say 29 days instead of 30 for June. Hope I've made sense...?? Thank you in anticipation. -- Regards, Sarah |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
S.O.S.....
Thanks very much for your help!!! Much appreciated.
-- Regards, Sarah "T. Valko" wrote: Here's one way. Try this array formula** : =AVERAGE(IF(TEXT(A2:A20,"mmmyyyy")="Jun2007",IF(D2 :D200,D2:D20))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Sarah" wrote in message ... Hello, I have a spreadsheet set up with column A for the date (one day per row), and column D as the total daily takings. I want a formula that changes automatically when I enter each new daily amount, and if I enter a daily taking of $0, I don't want excel to include that day in the month's average. So I want it to regognise that the $0 day is not to be included, and only divide by say 29 days instead of 30 for June. Hope I've made sense...?? Thank you in anticipation. -- Regards, Sarah |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
S.O.S.....
"Sarah" wrote in message
... Hello, I have a spreadsheet set up with column A for the date (one day per row), and column D as the total daily takings. I want a formula that changes automatically when I enter each new daily amount, and if I enter a daily taking of $0, I don't want excel to include that day in the month's average. If you don't include days of zero takings in the average then your average will be wrong and misleading. Gordon Burgess-Parker Systems and Management Accountant |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
S.O.S.....
Okay, thanks Gordon. Is there an easier way to make this formula work?
Maybe I'm doing something wrong??? -- Regards, Sarah "Gordon" wrote: "Sarah" wrote in message ... Hello, I have a spreadsheet set up with column A for the date (one day per row), and column D as the total daily takings. I want a formula that changes automatically when I enter each new daily amount, and if I enter a daily taking of $0, I don't want excel to include that day in the month's average. If you don't include days of zero takings in the average then your average will be wrong and misleading. Gordon Burgess-Parker Systems and Management Accountant |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
S.O.S.....
"Sarah" wrote in message
... Okay, thanks Gordon. Is there an easier way to make this formula work? Maybe I'm doing something wrong??? as there are no more than 31 days in any one month, do a SUM of your column with the takings, in the cell on the 33rd row.....then use that to calculate your average. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
S.O.S.....
Hi Sarah
The Average function ignores blank cells anyway. =AVERAGE(D2:D32) will give your average for any month, providing you leave cells blank, rather than entering 0. If you do enter a 0, that is a valid number and the Average function will take it into account. -- Regards Roger Govier "Sarah" wrote in message ... Okay, thanks Gordon. Is there an easier way to make this formula work? Maybe I'm doing something wrong??? -- Regards, Sarah "Gordon" wrote: "Sarah" wrote in message ... Hello, I have a spreadsheet set up with column A for the date (one day per row), and column D as the total daily takings. I want a formula that changes automatically when I enter each new daily amount, and if I enter a daily taking of $0, I don't want excel to include that day in the month's average. If you don't include days of zero takings in the average then your average will be wrong and misleading. Gordon Burgess-Parker Systems and Management Accountant |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
S.O.S.....
"Roger Govier" wrote in message
... Hi Sarah The Average function ignores blank cells anyway. =AVERAGE(D2:D32) will give your average for any month, providing you leave cells blank, rather than entering 0. If you do enter a 0, that is a valid number and the Average function will take it into account. Which the OP should do if the average is to be meaningful.... |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
S.O.S.....
You're welcome. Thanks for the feedback!
Biff "Sarah" wrote in message ... Thanks very much for your help!!! Much appreciated. -- Regards, Sarah "T. Valko" wrote: Here's one way. Try this array formula** : =AVERAGE(IF(TEXT(A2:A20,"mmmyyyy")="Jun2007",IF(D2 :D200,D2:D20))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Sarah" wrote in message ... Hello, I have a spreadsheet set up with column A for the date (one day per row), and column D as the total daily takings. I want a formula that changes automatically when I enter each new daily amount, and if I enter a daily taking of $0, I don't want excel to include that day in the month's average. So I want it to regognise that the $0 day is not to be included, and only divide by say 29 days instead of 30 for June. Hope I've made sense...?? Thank you in anticipation. -- Regards, Sarah |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
S.O.S.....
I was under the assumption that the dates could be for any and all months.
If the dates listed are specific to only a single month and you still want to exclude any hard 0s: =SUM(D1:D31)/COUNTIF(D1:D31,"0") Biff "Roger Govier" wrote in message ... Hi Sarah The Average function ignores blank cells anyway. =AVERAGE(D2:D32) will give your average for any month, providing you leave cells blank, rather than entering 0. If you do enter a 0, that is a valid number and the Average function will take it into account. -- Regards Roger Govier "Sarah" wrote in message ... Okay, thanks Gordon. Is there an easier way to make this formula work? Maybe I'm doing something wrong??? -- Regards, Sarah "Gordon" wrote: "Sarah" wrote in message ... Hello, I have a spreadsheet set up with column A for the date (one day per row), and column D as the total daily takings. I want a formula that changes automatically when I enter each new daily amount, and if I enter a daily taking of $0, I don't want excel to include that day in the month's average. If you don't include days of zero takings in the average then your average will be wrong and misleading. Gordon Burgess-Parker Systems and Management Accountant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|