Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Daily Average
I prepare a report containing the daily average for every month in the year.
How can I set the formula (prefer not VBA) which can automatically identify number of days for every month, i.e. in August, get the average of dividing by 31; in September, get the average by dividing of 30 and so on ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Daily Average
Having year in A1, month No in B1:
=DAY(DATE(A1,B1+1,0)) Regards, Stefi €˛yclhk€¯ ezt Ć*rta: I prepare a report containing the daily average for every month in the year. How can I set the formula (prefer not VBA) which can automatically identify number of days for every month, i.e. in August, get the average of dividing by 31; in September, get the average by dividing of 30 and so on ? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Daily Average
If A1 contains a date then =DAY(EOMONTH(A1,0)) will return the number of days
in that month. Hope this helps "yclhk" wrote: I prepare a report containing the daily average for every month in the year. How can I set the formula (prefer not VBA) which can automatically identify number of days for every month, i.e. in August, get the average of dividing by 31; in September, get the average by dividing of 30 and so on ? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Daily Average
Thanks Ron & Stefi.
My MS office returns that the EMONTH function cannot be found. My MS office is Office XP. Does the function not inclduding in this version. The Date(year, month, day) formula is found worked. Pls be kind to explain that : - why the month value should add 1, i.e. B1+1 - does the value in B1 must be alphabet Thanks a lot, "Stefi" wrote: Having year in A1, month No in B1: =DAY(DATE(A1,B1+1,0)) Regards, Stefi €˛yclhk€¯ ezt Ć*rta: I prepare a report containing the daily average for every month in the year. How can I set the formula (prefer not VBA) which can automatically identify number of days for every month, i.e. in August, get the average of dividing by 31; in September, get the average by dividing of 30 and so on ? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Daily Average
To get help on EOMONTH, type EOMONTH into Excel help.
DATE(A1,B1+1,1) is the first day of the month after B1; DATE(A1,B1+1,0) is 1 day before that, and is therefore the last day of month B1. Stefi said "month No in B1" so, no, the month number should *not* be alphabet; it should be 10, not "ten". -- David Biddulph "yclhk" wrote in message ... Thanks Ron & Stefi. My MS office returns that the EMONTH function cannot be found. My MS office is Office XP. Does the function not inclduding in this version. The Date(year, month, day) formula is found worked. Pls be kind to explain that : - why the month value should add 1, i.e. B1+1 - does the value in B1 must be alphabet Thanks a lot, "Stefi" wrote: Having year in A1, month No in B1: =DAY(DATE(A1,B1+1,0)) Regards, Stefi "yclhk" ezt ķrta: I prepare a report containing the daily average for every month in the year. How can I set the formula (prefer not VBA) which can automatically identify number of days for every month, i.e. in August, get the average of dividing by 31; in September, get the average by dividing of 30 and so on ? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Daily Average
If you have Jan,Feb, etc. in column B, then you can use (in UK date system)
=DAY(DATE($A$1,MONTH(DATEVALUE("1/"&B1&"/"&$A$1))+1,0)) or (in US date system) =DAY(DATE($A$1,MONTH(DATEVALUE(B1&"/1/"&$A$1))+1,0)) For EOMONTH you have to enable Analisys Toolpak. DATE(year,month+1,0) is a workaround: 0th day of a month equals last day of the previous month by Excel calculation method. Regards, Stefi €˛yclhk€¯ ezt Ć*rta: Thanks Ron & Stefi. My MS office returns that the EMONTH function cannot be found. My MS office is Office XP. Does the function not inclduding in this version. The Date(year, month, day) formula is found worked. Pls be kind to explain that : - why the month value should add 1, i.e. B1+1 - does the value in B1 must be alphabet Thanks a lot, "Stefi" wrote: Having year in A1, month No in B1: =DAY(DATE(A1,B1+1,0)) Regards, Stefi €˛yclhk€¯ ezt Ć*rta: I prepare a report containing the daily average for every month in the year. How can I set the formula (prefer not VBA) which can automatically identify number of days for every month, i.e. in August, get the average of dividing by 31; in September, get the average by dividing of 30 and so on ? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Daily Average
Hi, David,
When I enter the formula EOMONTH, it returns #NAME? "David Biddulph" wrote: To get help on EOMONTH, type EOMONTH into Excel help. DATE(A1,B1+1,1) is the first day of the month after B1; DATE(A1,B1+1,0) is 1 day before that, and is therefore the last day of month B1. Stefi said "month No in B1" so, no, the month number should *not* be alphabet; it should be 10, not "ten". -- David Biddulph "yclhk" wrote in message ... Thanks Ron & Stefi. My MS office returns that the EMONTH function cannot be found. My MS office is Office XP. Does the function not inclduding in this version. The Date(year, month, day) formula is found worked. Pls be kind to explain that : - why the month value should add 1, i.e. B1+1 - does the value in B1 must be alphabet Thanks a lot, "Stefi" wrote: Having year in A1, month No in B1: =DAY(DATE(A1,B1+1,0)) Regards, Stefi "yclhk" ezt Ć*rta: I prepare a report containing the daily average for every month in the year. How can I set the formula (prefer not VBA) which can automatically identify number of days for every month, i.e. in August, get the average of dividing by 31; in September, get the average by dividing of 30 and so on ? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Daily Average
And what did it say when you typed EOMONTH into Excel help, as I suggested?
-- David Biddulph "yclhk" wrote in message ... Hi, David, When I enter the formula EOMONTH, it returns #NAME? "David Biddulph" wrote: To get help on EOMONTH, type EOMONTH into Excel help. DATE(A1,B1+1,1) is the first day of the month after B1; DATE(A1,B1+1,0) is 1 day before that, and is therefore the last day of month B1. Stefi said "month No in B1" so, no, the month number should *not* be alphabet; it should be 10, not "ten". -- David Biddulph "yclhk" wrote in message ... Thanks Ron & Stefi. My MS office returns that the EMONTH function cannot be found. My MS office is Office XP. Does the function not inclduding in this version. The Date(year, month, day) formula is found worked. Pls be kind to explain that : - why the month value should add 1, i.e. B1+1 - does the value in B1 must be alphabet Thanks a lot, "Stefi" wrote: Having year in A1, month No in B1: =DAY(DATE(A1,B1+1,0)) Regards, Stefi "yclhk" ezt ķrta: I prepare a report containing the daily average for every month in the year. How can I set the formula (prefer not VBA) which can automatically identify number of days for every month, i.e. in August, get the average of dividing by 31; in September, get the average by dividing of 30 and so on ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Daily Balance | Excel Worksheet Functions | |||
Getting the Weekly Daily Average | Excel Discussion (Misc queries) | |||
Convert average daily yield to APY | Excel Worksheet Functions | |||
Running a Daily MTD average. | Excel Worksheet Functions | |||
daily average | Excel Worksheet Functions |