Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple averages in large continous data field
I figured I'd try this once more. :-)
In column C I have the date, in column D I have the time, and in column E I have a measurement taken at that date/time period. Theres 25,000+ rows of this data, so doing what I need wouldnt be something easily done manually. Based on the date and time, I would like to find the average of the measurements taken per date, per shift. Day shift is 7:00 AM until 9:00 PM, and night shift is 9:01 PM until 6:59 AM. The night shift does not need to be spread across dates, as for example, August 30, 9:00 PM until midnight is a separate date than August 31, 12:01 AM until 6:59 AM. I would like to post the results for each shift per date in column G at the first two rows per date. There are about 200 rows per date, so where the results would be about 200 rows apart. Is there anyway this can be done? Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple averages in large continous data field
Try this,
1. Insert a new colum for shift. You can decide this with a simple if formula checcking the date and time. With this done you have all the reading marked as either D for day shift or N for night shift. 2. After this you can easily use SUMIF()/COUTNIF() to find the average. hope this makes sense!! -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "Me" wrote: I figured I'd try this once more. :-) In column C I have the date, in column D I have the time, and in column E I have a measurement taken at that date/time period. Theres 25,000+ rows of this data, so doing what I need wouldnt be something easily done manually. Based on the date and time, I would like to find the average of the measurements taken per date, per shift. Day shift is 7:00 AM until 9:00 PM, and night shift is 9:01 PM until 6:59 AM. The night shift does not need to be spread across dates, as for example, August 30, 9:00 PM until midnight is a separate date than August 31, 12:01 AM until 6:59 AM. I would like to post the results for each shift per date in column G at the first two rows per date. There are about 200 rows per date, so where the results would be about 200 rows apart. Is there anyway this can be done? Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple averages in large continous data field
I understand what you're saying, but unfortunately, I am not good at creating
logic formulas (if then else stuff). :-( Would this be a if and statement? "Pranav Vaidya" wrote: Try this, 1. Insert a new colum for shift. You can decide this with a simple if formula checcking the date and time. With this done you have all the reading marked as either D for day shift or N for night shift. 2. After this you can easily use SUMIF()/COUTNIF() to find the average. hope this makes sense!! -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "Me" wrote: I figured I'd try this once more. :-) In column C I have the date, in column D I have the time, and in column E I have a measurement taken at that date/time period. Theres 25,000+ rows of this data, so doing what I need wouldnt be something easily done manually. Based on the date and time, I would like to find the average of the measurements taken per date, per shift. Day shift is 7:00 AM until 9:00 PM, and night shift is 9:01 PM until 6:59 AM. The night shift does not need to be spread across dates, as for example, August 30, 9:00 PM until midnight is a separate date than August 31, 12:01 AM until 6:59 AM. I would like to post the results for each shift per date in column G at the first two rows per date. There are about 200 rows per date, so where the results would be about 200 rows apart. Is there anyway this can be done? Thanks in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple averages in large continous data field
I'm going to assume that if a measurement was taken between 0700 and
2100, it was on a day shift; if not, it was during a night shift, since the two shifts complement each other. Insert a new column. Let's say G. Use the header "Shift". This is the formula in G2 (assuming row 1 is header): =IF(AND(D2TIMEVALUE("7:00 am"),D2<TIMEVALUE("9:00 pm")),"Day Shift","Night Shift") Copy this formula down all the way. Next, create a pivot table. Use Date and Shift for row values, and measurement for data values. Change field settings to summarize by Average (rather than count or sum, whichever it defaults to). If you truly want to, you can then use GetPivotData function to bring the data back to the source worksheet, just make sure the column you do this in is not part of the pivot data range. On Aug 31, 11:00 am, Me wrote: I understand what you're saying, but unfortunately, I am not good at creating logic formulas (if then else stuff). :-( Would this be a if and statement? "Pranav Vaidya" wrote: Try this, 1. Insert a new colum for shift. You can decide this with a simple if formula checcking the date and time. With this done you have all the reading marked as either D for day shift or N for night shift. 2. After this you can easily use SUMIF()/COUTNIF() to find the average. hope this makes sense!! -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "Me" wrote: I figured I'd try this once more. :-) In column C I have the date, in column D I have the time, and in column E I have a measurement taken at that date/time period. There's 25,000+ rows of this data, so doing what I need wouldn't be something easily done manually. Based on the date and time, I would like to find the average of the measurements taken per date, per shift. Day shift is 7:00 AM until 9:00 PM, and night shift is 9:01 PM until 6:59 AM. The night shift does not need to be spread across dates, as for example, August 30, 9:00 PM until midnight is a separate date than August 31, 12:01 AM until 6:59 AM. I would like to post the results for each shift per date in column G at the first two rows per date. There are about 200 rows per date, so where the results would be about 200 rows apart. Is there anyway this can be done? Thanks in advance.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple averages in large continous data field
That worked perfectly!
Thank you very much. :-) "iliace" wrote: I'm going to assume that if a measurement was taken between 0700 and 2100, it was on a day shift; if not, it was during a night shift, since the two shifts complement each other. Insert a new column. Let's say G. Use the header "Shift". This is the formula in G2 (assuming row 1 is header): =IF(AND(D2TIMEVALUE("7:00 am"),D2<TIMEVALUE("9:00 pm")),"Day Shift","Night Shift") Copy this formula down all the way. Next, create a pivot table. Use Date and Shift for row values, and measurement for data values. Change field settings to summarize by Average (rather than count or sum, whichever it defaults to). If you truly want to, you can then use GetPivotData function to bring the data back to the source worksheet, just make sure the column you do this in is not part of the pivot data range. On Aug 31, 11:00 am, Me wrote: I understand what you're saying, but unfortunately, I am not good at creating logic formulas (if then else stuff). :-( Would this be a if and statement? "Pranav Vaidya" wrote: Try this, 1. Insert a new colum for shift. You can decide this with a simple if formula checcking the date and time. With this done you have all the reading marked as either D for day shift or N for night shift. 2. After this you can easily use SUMIF()/COUTNIF() to find the average. hope this makes sense!! -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "Me" wrote: I figured I'd try this once more. :-) In column C I have the date, in column D I have the time, and in column E I have a measurement taken at that date/time period. There's 25,000+ rows of this data, so doing what I need wouldn't be something easily done manually. Based on the date and time, I would like to find the average of the measurements taken per date, per shift. Day shift is 7:00 AM until 9:00 PM, and night shift is 9:01 PM until 6:59 AM. The night shift does not need to be spread across dates, as for example, August 30, 9:00 PM until midnight is a separate date than August 31, 12:01 AM until 6:59 AM. I would like to post the results for each shift per date in column G at the first two rows per date. There are about 200 rows per date, so where the results would be about 200 rows apart. Is there anyway this can be done? Thanks in advance.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple averages in large continous data field | Excel Discussion (Misc queries) | |||
Averages formula for multiple ranges | Excel Worksheet Functions | |||
Pivot Table - Dragging multiple items into data field | Excel Discussion (Misc queries) | |||
how do i get field name from result of MAX or LARGE function | Excel Worksheet Functions | |||
Compile/Analyze Word Form Field data from multiple forms? | Excel Discussion (Misc queries) |