| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average Function
In column B I have the dates listed from 01/01/07 through 10/31/2007. In
column E I have totals for each day. What I would like to do is have an average for each day of the week, i.e., Mondays from 01/01/2007-10/31/2007 = 219, Tuesdays from 01/01/2007-10/31/2007= 250 and so on. I know that I will have to have a separate cell doing it's own calculation for each weekday, I'm just not that savvy working with the Sumifs, countifs, or day functions. Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function
One way (Array entered, Ctrl+Shift+Enter)
=AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25)) The above formula will calculate the average of E2:E25 where the weekday in B2:B25 is equal to Monday. To do Tuesday, Wednesday, etc, simply change the WEEKDAY(B2:B25,1)=2 portion to whatever day of the week you are after. DaS wrote: In column B I have the dates listed from 01/01/07 through 10/31/2007. In column E I have totals for each day. What I would like to do is have an average for each day of the week, i.e., Mondays from 01/01/2007-10/31/2007 = 219, Tuesdays from 01/01/2007-10/31/2007= 250 and so on. I know that I will have to have a separate cell doing it's own calculation for each weekday, I'm just not that savvy working with the Sumifs, countifs, or day functions. Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function
So to change the date to do Tuesday I need to change the 1 or the 2? so
Tuesday will be ...(WEEKDAY(B2:B25,2)=2,...? Is that right? I've been messing around with it and now I get the #NUM! statement. Thanks JW "JW" wrote: One way (Array entered, Ctrl+Shift+Enter) =AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25)) The above formula will calculate the average of E2:E25 where the weekday in B2:B25 is equal to Monday. To do Tuesday, Wednesday, etc, simply change the WEEKDAY(B2:B25,1)=2 portion to whatever day of the week you are after. DaS wrote: In column B I have the dates listed from 01/01/07 through 10/31/2007. In column E I have totals for each day. What I would like to do is have an average for each day of the week, i.e., Mondays from 01/01/2007-10/31/2007 = 219, Tuesdays from 01/01/2007-10/31/2007= 250 and so on. I know that I will have to have a separate cell doing it's own calculation for each weekday, I'm just not that savvy working with the Sumifs, countifs, or day functions. Thanks! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function
All Ctrl+Shift+Enter entered.
Monday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25)) Tuesday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=3,E2:E25)) Wednesday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=4,E2:E25)) etc etc DaS wrote: So to change the date to do Tuesday I need to change the 1 or the 2? so Tuesday will be ...(WEEKDAY(B2:B25,2)=2,...? Is that right? I've been messing around with it and now I get the #NUM! statement. Thanks JW "JW" wrote: One way (Array entered, Ctrl+Shift+Enter) =AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25)) The above formula will calculate the average of E2:E25 where the weekday in B2:B25 is equal to Monday. To do Tuesday, Wednesday, etc, simply change the WEEKDAY(B2:B25,1)=2 portion to whatever day of the week you are after. DaS wrote: In column B I have the dates listed from 01/01/07 through 10/31/2007. In column E I have totals for each day. What I would like to do is have an average for each day of the week, i.e., Mondays from 01/01/2007-10/31/2007 = 219, Tuesdays from 01/01/2007-10/31/2007= 250 and so on. I know that I will have to have a separate cell doing it's own calculation for each weekday, I'm just not that savvy working with the Sumifs, countifs, or day functions. Thanks! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function
After messing around with it, I got it. Thank you very much for your help.
For the sake of knowledge, what does the ....,1) represent? "JW" wrote: All Ctrl+Shift+Enter entered. Monday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25)) Tuesday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=3,E2:E25)) Wednesday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=4,E2:E25)) etc etc DaS wrote: So to change the date to do Tuesday I need to change the 1 or the 2? so Tuesday will be ...(WEEKDAY(B2:B25,2)=2,...? Is that right? I've been messing around with it and now I get the #NUM! statement. Thanks JW "JW" wrote: One way (Array entered, Ctrl+Shift+Enter) =AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25)) The above formula will calculate the average of E2:E25 where the weekday in B2:B25 is equal to Monday. To do Tuesday, Wednesday, etc, simply change the WEEKDAY(B2:B25,1)=2 portion to whatever day of the week you are after. DaS wrote: In column B I have the dates listed from 01/01/07 through 10/31/2007. In column E I have totals for each day. What I would like to do is have an average for each day of the week, i.e., Mondays from 01/01/2007-10/31/2007 = 219, Tuesdays from 01/01/2007-10/31/2007= 250 and so on. I know that I will have to have a separate cell doing it's own calculation for each weekday, I'm just not that savvy working with the Sumifs, countifs, or day functions. Thanks! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function
The 1 is an optional argument for the return type.
Taken from Excel help: 1 or omitted = Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel. 2 = Numbers 1 (Monday) through 7 (Sunday). 3 = Numbers 0 (Monday) through 6 (Sunday). DaS wrote: After messing around with it, I got it. Thank you very much for your help. For the sake of knowledge, what does the ....,1) represent? "JW" wrote: All Ctrl+Shift+Enter entered. Monday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25)) Tuesday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=3,E2:E25)) Wednesday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=4,E2:E25)) etc etc DaS wrote: So to change the date to do Tuesday I need to change the 1 or the 2? so Tuesday will be ...(WEEKDAY(B2:B25,2)=2,...? Is that right? I've been messing around with it and now I get the #NUM! statement. Thanks JW "JW" wrote: One way (Array entered, Ctrl+Shift+Enter) =AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25)) The above formula will calculate the average of E2:E25 where the weekday in B2:B25 is equal to Monday. To do Tuesday, Wednesday, etc, simply change the WEEKDAY(B2:B25,1)=2 portion to whatever day of the week you are after. DaS wrote: In column B I have the dates listed from 01/01/07 through 10/31/2007. In column E I have totals for each day. What I would like to do is have an average for each day of the week, i.e., Mondays from 01/01/2007-10/31/2007 = 219, Tuesdays from 01/01/2007-10/31/2007= 250 and so on. I know that I will have to have a separate cell doing it's own calculation for each weekday, I'm just not that savvy working with the Sumifs, countifs, or day functions. Thanks! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average Function
Great! Thank you very much for your help!
"JW" wrote: The 1 is an optional argument for the return type. Taken from Excel help: 1 or omitted = Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel. 2 = Numbers 1 (Monday) through 7 (Sunday). 3 = Numbers 0 (Monday) through 6 (Sunday). DaS wrote: After messing around with it, I got it. Thank you very much for your help. For the sake of knowledge, what does the ....,1) represent? "JW" wrote: All Ctrl+Shift+Enter entered. Monday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25)) Tuesday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=3,E2:E25)) Wednesday: =AVERAGE(IF(WEEKDAY(B2:B25,1)=4,E2:E25)) etc etc DaS wrote: So to change the date to do Tuesday I need to change the 1 or the 2? so Tuesday will be ...(WEEKDAY(B2:B25,2)=2,...? Is that right? I've been messing around with it and now I get the #NUM! statement. Thanks JW "JW" wrote: One way (Array entered, Ctrl+Shift+Enter) =AVERAGE(IF(WEEKDAY(B2:B25,1)=2,E2:E25)) The above formula will calculate the average of E2:E25 where the weekday in B2:B25 is equal to Monday. To do Tuesday, Wednesday, etc, simply change the WEEKDAY(B2:B25,1)=2 portion to whatever day of the week you are after. DaS wrote: In column B I have the dates listed from 01/01/07 through 10/31/2007. In column E I have totals for each day. What I would like to do is have an average for each day of the week, i.e., Mondays from 01/01/2007-10/31/2007 = 219, Tuesdays from 01/01/2007-10/31/2007= 250 and so on. I know that I will have to have a separate cell doing it's own calculation for each weekday, I'm just not that savvy working with the Sumifs, countifs, or day functions. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| @average function | Excel Worksheet Functions | |||
| Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
| I want to use the MATCH function with the AVERAGE function but I . | Excel Worksheet Functions | |||
| Using the average function | Excel Worksheet Functions | |||
| How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Linear Mode
