If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to match date criteria then calculate an average of matches
I have a spreadsheet currently containing 2 sheets, the first sheet contains
raw data and several lines of information are tied to the same date which is listed in column A, the data I want to sumarize is listed in columnar style across the page. In the second sheet I want a summary by date, averages and standard deviations. The second sheet also contains the date I'm looking to match calculate. I know how to use the sumif and countif functions, but I think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I had this working in another spreadsheet, but lost access to it in a move. |
#2
|
|||
|
|||
How to match date criteria then calculate an average of matches
You need to be more specific. Where is the data you want to average?
-- Biff Microsoft Excel MVP "Buffy M. Warren" Buffy M. wrote in message ... I have a spreadsheet currently containing 2 sheets, the first sheet contains raw data and several lines of information are tied to the same date which is listed in column A, the data I want to sumarize is listed in columnar style across the page. In the second sheet I want a summary by date, averages and standard deviations. The second sheet also contains the date I'm looking to match calculate. I know how to use the sumif and countif functions, but I think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I had this working in another spreadsheet, but lost access to it in a move. |
#3
|
|||
|
|||
How to match date criteria then calculate an average of matche
Sheet 1 column A contains dates that reoccur, column B contains data of
interest to calculate averages and standard deviations, also Row 1 contains header or column title information. Sheet 2, or what I call the Summary sheet, repeats the dates of interest (only once) in Column A, also has EXACT same header list in Row 1. In cell B2 of Sheet 2 I want to average the values from Sheet 1, with the same header (title) that match the date in cell A2. "T. Valko" wrote: You need to be more specific. Where is the data you want to average? -- Biff Microsoft Excel MVP "Buffy M. Warren" Buffy M. wrote in message ... I have a spreadsheet currently containing 2 sheets, the first sheet contains raw data and several lines of information are tied to the same date which is listed in column A, the data I want to sumarize is listed in columnar style across the page. In the second sheet I want a summary by date, averages and standard deviations. The second sheet also contains the date I'm looking to match calculate. I know how to use the sumif and countif functions, but I think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I had this working in another spreadsheet, but lost access to it in a move. |
#4
|
|||
|
|||
How to match date criteria then calculate an average of matche
See this screencap:
http://img103.imageshack.us/img103/369/avgat5.jpg That shows the array formula** needed for the average. The array formula** for the stdev uses the same structu =AVERAGE(IF($A$3:$A$8=$A12,B$3:B$8)) =STDEV(IF($A$3:$A$8=$A12,B$3:B$8)) I put everything on the same sheet to make it easier to follow. All you need to do is plug in the actual sheet name. Here's a tip that you might find useful. Just like the screencap shows, I put everything on the same sheet. After I've got all the formulas entered I'll then cut and paste the entire block of formula data to the other sheet. Excel will automatically add the sheet name for you. I find this to be easier than starting on the other sheet and having to switch back and forth between sheets when writing the formulas. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Buffy M. Warren" wrote in message ... Sheet 1 column A contains dates that reoccur, column B contains data of interest to calculate averages and standard deviations, also Row 1 contains header or column title information. Sheet 2, or what I call the Summary sheet, repeats the dates of interest (only once) in Column A, also has EXACT same header list in Row 1. In cell B2 of Sheet 2 I want to average the values from Sheet 1, with the same header (title) that match the date in cell A2. "T. Valko" wrote: You need to be more specific. Where is the data you want to average? -- Biff Microsoft Excel MVP "Buffy M. Warren" Buffy M. wrote in message ... I have a spreadsheet currently containing 2 sheets, the first sheet contains raw data and several lines of information are tied to the same date which is listed in column A, the data I want to sumarize is listed in columnar style across the page. In the second sheet I want a summary by date, averages and standard deviations. The second sheet also contains the date I'm looking to match calculate. I know how to use the sumif and countif functions, but I think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I had this working in another spreadsheet, but lost access to it in a move. |
#5
|
|||
|
|||
How to match date criteria then calculate an average of matche
Thanks T. Valko - Absolutely fantastic, much more straight-forward than my
previous path, obviously it was too cumbersome for me to recreate. I haven't had much practice using arrays but this gets me off on the right foot! "T. Valko" wrote: See this screencap: http://img103.imageshack.us/img103/369/avgat5.jpg That shows the array formula** needed for the average. The array formula** for the stdev uses the same structu =AVERAGE(IF($A$3:$A$8=$A12,B$3:B$8)) =STDEV(IF($A$3:$A$8=$A12,B$3:B$8)) I put everything on the same sheet to make it easier to follow. All you need to do is plug in the actual sheet name. Here's a tip that you might find useful. Just like the screencap shows, I put everything on the same sheet. After I've got all the formulas entered I'll then cut and paste the entire block of formula data to the other sheet. Excel will automatically add the sheet name for you. I find this to be easier than starting on the other sheet and having to switch back and forth between sheets when writing the formulas. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Buffy M. Warren" wrote in message ... Sheet 1 column A contains dates that reoccur, column B contains data of interest to calculate averages and standard deviations, also Row 1 contains header or column title information. Sheet 2, or what I call the Summary sheet, repeats the dates of interest (only once) in Column A, also has EXACT same header list in Row 1. In cell B2 of Sheet 2 I want to average the values from Sheet 1, with the same header (title) that match the date in cell A2. "T. Valko" wrote: You need to be more specific. Where is the data you want to average? -- Biff Microsoft Excel MVP "Buffy M. Warren" Buffy M. wrote in message ... I have a spreadsheet currently containing 2 sheets, the first sheet contains raw data and several lines of information are tied to the same date which is listed in column A, the data I want to sumarize is listed in columnar style across the page. In the second sheet I want a summary by date, averages and standard deviations. The second sheet also contains the date I'm looking to match calculate. I know how to use the sumif and countif functions, but I think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I had this working in another spreadsheet, but lost access to it in a move. |
#6
|
|||
|
|||
How to match date criteria then calculate an average of matche
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Buffy M. Warren" wrote in message ... Thanks T. Valko - Absolutely fantastic, much more straight-forward than my previous path, obviously it was too cumbersome for me to recreate. I haven't had much practice using arrays but this gets me off on the right foot! "T. Valko" wrote: See this screencap: http://img103.imageshack.us/img103/369/avgat5.jpg That shows the array formula** needed for the average. The array formula** for the stdev uses the same structu =AVERAGE(IF($A$3:$A$8=$A12,B$3:B$8)) =STDEV(IF($A$3:$A$8=$A12,B$3:B$8)) I put everything on the same sheet to make it easier to follow. All you need to do is plug in the actual sheet name. Here's a tip that you might find useful. Just like the screencap shows, I put everything on the same sheet. After I've got all the formulas entered I'll then cut and paste the entire block of formula data to the other sheet. Excel will automatically add the sheet name for you. I find this to be easier than starting on the other sheet and having to switch back and forth between sheets when writing the formulas. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Buffy M. Warren" wrote in message ... Sheet 1 column A contains dates that reoccur, column B contains data of interest to calculate averages and standard deviations, also Row 1 contains header or column title information. Sheet 2, or what I call the Summary sheet, repeats the dates of interest (only once) in Column A, also has EXACT same header list in Row 1. In cell B2 of Sheet 2 I want to average the values from Sheet 1, with the same header (title) that match the date in cell A2. "T. Valko" wrote: You need to be more specific. Where is the data you want to average? -- Biff Microsoft Excel MVP "Buffy M. Warren" Buffy M. wrote in message ... I have a spreadsheet currently containing 2 sheets, the first sheet contains raw data and several lines of information are tied to the same date which is listed in column A, the data I want to sumarize is listed in columnar style across the page. In the second sheet I want a summary by date, averages and standard deviations. The second sheet also contains the date I'm looking to match calculate. I know how to use the sumif and countif functions, but I think this needs a combination of MATCH, AVERAGE, STDEV and OFFSET. I had this working in another spreadsheet, but lost access to it in a move. |
Thread Tools | |
Display Modes | |
|
|