Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarizing data based on ID
Hi,
I have a table with 3 columns: Site, Temperature, and Date. The "Site" field contains multiple unique "Sites" (7 to be precise), and multiple records per unique site on any given date. What I would like to do, for any given day, is summarize (avg, max, min, etc) each site's temperature . The way I have done this before is to have a table with, using this data as an example, 8 columns: Date and 1 column for each site, with temperature the data under each site's column. I would then just use the avg, max, min, etc functions in one cell and drag it across to get the summary for each site. This would be tedious here, however, since I would have to do a bunch of copying and pasting to get the data arranged in this manner. Any way around this through nested functions or VB code?? Thanx in advance for any help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarizing data based on ID
Array formulas would help here. Basically the idea is that you multiply
each element of the temperatures array (assumed C1:C10) with a logical expression evaluating to 0 or 1. However, the operation must be done for each element of the array, hence they must be entered using Shift+Ctrl+Enter =AVERAGE((C1:C10)*--(A1:A10="My Site")) You can use this technique for all your aggregate functions with the exception of MIN. Here, multiplying positive temperatres with 0 would make 0 the minimum. In this case you can use something like: =MIN(C1:C10+IF(A1:A10="My Site", 0, 1000)) HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarizing data based on ID
Giz,
Use a pivot table - will do all that and more, automatically. HTH, Bernie MS Excel MVP "Giz" wrote in message ... Hi, I have a table with 3 columns: Site, Temperature, and Date. The "Site" field contains multiple unique "Sites" (7 to be precise), and multiple records per unique site on any given date. What I would like to do, for any given day, is summarize (avg, max, min, etc) each site's temperature . The way I have done this before is to have a table with, using this data as an example, 8 columns: Date and 1 column for each site, with temperature the data under each site's column. I would then just use the avg, max, min, etc functions in one cell and drag it across to get the summary for each site. This would be tedious here, however, since I would have to do a bunch of copying and pasting to get the data arranged in this manner. Any way around this through nested functions or VB code?? Thanx in advance for any help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarizing data based on ID
I am trying to get at max, min, avg, the first and third quartiles of data
for temperature for every day. The formula I would use for quartiles is "QUARTILE(selection,1)" for the first quartile, for example. How do I get that in a pivot table?? I have tried to figure it out but am having trouble, thanks. "Bernie Deitrick" wrote: Giz, Use a pivot table - will do all that and more, automatically. HTH, Bernie MS Excel MVP "Giz" wrote in message ... Hi, I have a table with 3 columns: Site, Temperature, and Date. The "Site" field contains multiple unique "Sites" (7 to be precise), and multiple records per unique site on any given date. What I would like to do, for any given day, is summarize (avg, max, min, etc) each site's temperature . The way I have done this before is to have a table with, using this data as an example, 8 columns: Date and 1 column for each site, with temperature the data under each site's column. I would then just use the avg, max, min, etc functions in one cell and drag it across to get the summary for each site. This would be tedious here, however, since I would have to do a bunch of copying and pasting to get the data arranged in this manner. Any way around this through nested functions or VB code?? Thanx in advance for any help |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarizing data based on ID
Giz,
I guess it's that etc... part that has jumped up and bitten us. Anyway, let's say that your table is on Sheet1, with Dates in column A and temperatures in column B - let's say, 1000 rows of data. Make the pivot table, with dates as the row field, and get your Max, Min, Average, and whatever else you want that is available using the standard pivot table functionality. Then for the quartiles, in the next column over from your pivot table, Array enter (enter using Ctrl-Shift-Enter) the following formula =QUARTILE(IF(Sheet1!$A$1:$A$1000=Sheet2!$A5,Sheet1 !$B$1:$B$1000),1) This assumes that the first date of the pivot table is in cell A5 of Sheet2. Then copy that over for three columns, and change the 1 to 2, then 3, then 4 (for your quartiles), making sure that you re-enter the formula using Ctrl-Shift-Enter. Then copy down to match the dates in your pivot table. HTH, Bernie MS Excel MVP "Giz" wrote in message ... I am trying to get at max, min, avg, the first and third quartiles of data for temperature for every day. The formula I would use for quartiles is "QUARTILE(selection,1)" for the first quartile, for example. How do I get that in a pivot table?? I have tried to figure it out but am having trouble, thanks. "Bernie Deitrick" wrote: Giz, Use a pivot table - will do all that and more, automatically. HTH, Bernie MS Excel MVP "Giz" wrote in message ... Hi, I have a table with 3 columns: Site, Temperature, and Date. The "Site" field contains multiple unique "Sites" (7 to be precise), and multiple records per unique site on any given date. What I would like to do, for any given day, is summarize (avg, max, min, etc) each site's temperature . The way I have done this before is to have a table with, using this data as an example, 8 columns: Date and 1 column for each site, with temperature the data under each site's column. I would then just use the avg, max, min, etc functions in one cell and drag it across to get the summary for each site. This would be tedious here, however, since I would have to do a bunch of copying and pasting to get the data arranged in this manner. Any way around this through nested functions or VB code?? Thanx in advance for any help |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarizing data based on ID
That works, but I don't think it differentiates between the different sites,
in the third column in the original data. "Bernie Deitrick" wrote: Giz, I guess it's that etc... part that has jumped up and bitten us. Anyway, let's say that your table is on Sheet1, with Dates in column A and temperatures in column B - let's say, 1000 rows of data. Make the pivot table, with dates as the row field, and get your Max, Min, Average, and whatever else you want that is available using the standard pivot table functionality. Then for the quartiles, in the next column over from your pivot table, Array enter (enter using Ctrl-Shift-Enter) the following formula =QUARTILE(IF(Sheet1!$A$1:$A$1000=Sheet2!$A5,Sheet1 !$B$1:$B$1000),1) This assumes that the first date of the pivot table is in cell A5 of Sheet2. Then copy that over for three columns, and change the 1 to 2, then 3, then 4 (for your quartiles), making sure that you re-enter the formula using Ctrl-Shift-Enter. Then copy down to match the dates in your pivot table. HTH, Bernie MS Excel MVP "Giz" wrote in message ... I am trying to get at max, min, avg, the first and third quartiles of data for temperature for every day. The formula I would use for quartiles is "QUARTILE(selection,1)" for the first quartile, for example. How do I get that in a pivot table?? I have tried to figure it out but am having trouble, thanks. "Bernie Deitrick" wrote: Giz, Use a pivot table - will do all that and more, automatically. HTH, Bernie MS Excel MVP "Giz" wrote in message ... Hi, I have a table with 3 columns: Site, Temperature, and Date. The "Site" field contains multiple unique "Sites" (7 to be precise), and multiple records per unique site on any given date. What I would like to do, for any given day, is summarize (avg, max, min, etc) each site's temperature . The way I have done this before is to have a table with, using this data as an example, 8 columns: Date and 1 column for each site, with temperature the data under each site's column. I would then just use the avg, max, min, etc functions in one cell and drag it across to get the summary for each site. This would be tedious here, however, since I would have to do a bunch of copying and pasting to get the data arranged in this manner. Any way around this through nested functions or VB code?? Thanx in advance for any help |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarizing data based on ID
Giz,
To add that condition, simply change =QUARTILE(IF(Sheet1!$A$1:$A$1000=Sheet2!$A5,Sheet1 !$B$1:$B$1000),1) to =QUARTILE(IF((Sheet1!$A$1:$A$1000=Sheet2!$A5)*(She et1!$C$1:$C$1000=Sheet2!$B5),Sheet1!$B$1:$B$1000), 1) You can add as many conditions as you need by adding additional *(Sheet1!$?$1:$?$1000=Sheet2!$?5) to the conditional at the beginning of the IF. Just make sure you remember to use Ctrl-Shift-Enter to enter this... HTH, Bernie MS Excel MVP "Giz" wrote in message ... That works, but I don't think it differentiates between the different sites, in the third column in the original data. "Bernie Deitrick" wrote: Giz, I guess it's that etc... part that has jumped up and bitten us. Anyway, let's say that your table is on Sheet1, with Dates in column A and temperatures in column B - let's say, 1000 rows of data. Make the pivot table, with dates as the row field, and get your Max, Min, Average, and whatever else you want that is available using the standard pivot table functionality. Then for the quartiles, in the next column over from your pivot table, Array enter (enter using Ctrl-Shift-Enter) the following formula =QUARTILE(IF(Sheet1!$A$1:$A$1000=Sheet2!$A5,Sheet1 !$B$1:$B$1000),1) This assumes that the first date of the pivot table is in cell A5 of Sheet2. Then copy that over for three columns, and change the 1 to 2, then 3, then 4 (for your quartiles), making sure that you re-enter the formula using Ctrl-Shift-Enter. Then copy down to match the dates in your pivot table. HTH, Bernie MS Excel MVP "Giz" wrote in message ... I am trying to get at max, min, avg, the first and third quartiles of data for temperature for every day. The formula I would use for quartiles is "QUARTILE(selection,1)" for the first quartile, for example. How do I get that in a pivot table?? I have tried to figure it out but am having trouble, thanks. "Bernie Deitrick" wrote: Giz, Use a pivot table - will do all that and more, automatically. HTH, Bernie MS Excel MVP "Giz" wrote in message ... Hi, I have a table with 3 columns: Site, Temperature, and Date. The "Site" field contains multiple unique "Sites" (7 to be precise), and multiple records per unique site on any given date. What I would like to do, for any given day, is summarize (avg, max, min, etc) each site's temperature . The way I have done this before is to have a table with, using this data as an example, 8 columns: Date and 1 column for each site, with temperature the data under each site's column. I would then just use the avg, max, min, etc functions in one cell and drag it across to get the summary for each site. This would be tedious here, however, since I would have to do a bunch of copying and pasting to get the data arranged in this manner. Any way around this through nested functions or VB code?? Thanx in advance for any help |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarizing data based on ID
Is the "Sheet2!$B5" the title of the "site" field in my pivot table? I tried
that but the results don't seem quite correct. The quartiles are lower than my min value for that site on that date. Thanks for all your help =QUARTILE(IF((Sheet1!$A$1:$A$1000=Sheet2!$A5)*(She et1!$C$1:$C$1000=Sheet2!$B5),Sheet1!$B$1:$B$1000), 1) "Bernie Deitrick" wrote: Giz, To add that condition, simply change =QUARTILE(IF(Sheet1!$A$1:$A$1000=Sheet2!$A5,Sheet1 !$B$1:$B$1000),1) to =QUARTILE(IF((Sheet1!$A$1:$A$1000=Sheet2!$A5)*(She et1!$C$1:$C$1000=Sheet2!$B5),Sheet1!$B$1:$B$1000), 1) You can add as many conditions as you need by adding additional *(Sheet1!$?$1:$?$1000=Sheet2!$?5) to the conditional at the beginning of the IF. Just make sure you remember to use Ctrl-Shift-Enter to enter this... HTH, Bernie MS Excel MVP "Giz" wrote in message ... That works, but I don't think it differentiates between the different sites, in the third column in the original data. "Bernie Deitrick" wrote: Giz, I guess it's that etc... part that has jumped up and bitten us. Anyway, let's say that your table is on Sheet1, with Dates in column A and temperatures in column B - let's say, 1000 rows of data. Make the pivot table, with dates as the row field, and get your Max, Min, Average, and whatever else you want that is available using the standard pivot table functionality. Then for the quartiles, in the next column over from your pivot table, Array enter (enter using Ctrl-Shift-Enter) the following formula =QUARTILE(IF(Sheet1!$A$1:$A$1000=Sheet2!$A5,Sheet1 !$B$1:$B$1000),1) This assumes that the first date of the pivot table is in cell A5 of Sheet2. Then copy that over for three columns, and change the 1 to 2, then 3, then 4 (for your quartiles), making sure that you re-enter the formula using Ctrl-Shift-Enter. Then copy down to match the dates in your pivot table. HTH, Bernie MS Excel MVP "Giz" wrote in message ... I am trying to get at max, min, avg, the first and third quartiles of data for temperature for every day. The formula I would use for quartiles is "QUARTILE(selection,1)" for the first quartile, for example. How do I get that in a pivot table?? I have tried to figure it out but am having trouble, thanks. "Bernie Deitrick" wrote: Giz, Use a pivot table - will do all that and more, automatically. HTH, Bernie MS Excel MVP "Giz" wrote in message ... Hi, I have a table with 3 columns: Site, Temperature, and Date. The "Site" field contains multiple unique "Sites" (7 to be precise), and multiple records per unique site on any given date. What I would like to do, for any given day, is summarize (avg, max, min, etc) each site's temperature . The way I have done this before is to have a table with, using this data as an example, 8 columns: Date and 1 column for each site, with temperature the data under each site's column. I would then just use the avg, max, min, etc functions in one cell and drag it across to get the summary for each site. This would be tedious here, however, since I would have to do a bunch of copying and pasting to get the data arranged in this manner. Any way around this through nested functions or VB code?? Thanx in advance for any help |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarizing data based on ID
I got it to work, thank you so very much for the help.
"Bernie Deitrick" wrote: Giz, To add that condition, simply change =QUARTILE(IF(Sheet1!$A$1:$A$1000=Sheet2!$A5,Sheet1 !$B$1:$B$1000),1) to =QUARTILE(IF((Sheet1!$A$1:$A$1000=Sheet2!$A5)*(She et1!$C$1:$C$1000=Sheet2!$B5),Sheet1!$B$1:$B$1000), 1) You can add as many conditions as you need by adding additional *(Sheet1!$?$1:$?$1000=Sheet2!$?5) to the conditional at the beginning of the IF. Just make sure you remember to use Ctrl-Shift-Enter to enter this... HTH, Bernie MS Excel MVP "Giz" wrote in message ... That works, but I don't think it differentiates between the different sites, in the third column in the original data. "Bernie Deitrick" wrote: Giz, I guess it's that etc... part that has jumped up and bitten us. Anyway, let's say that your table is on Sheet1, with Dates in column A and temperatures in column B - let's say, 1000 rows of data. Make the pivot table, with dates as the row field, and get your Max, Min, Average, and whatever else you want that is available using the standard pivot table functionality. Then for the quartiles, in the next column over from your pivot table, Array enter (enter using Ctrl-Shift-Enter) the following formula =QUARTILE(IF(Sheet1!$A$1:$A$1000=Sheet2!$A5,Sheet1 !$B$1:$B$1000),1) This assumes that the first date of the pivot table is in cell A5 of Sheet2. Then copy that over for three columns, and change the 1 to 2, then 3, then 4 (for your quartiles), making sure that you re-enter the formula using Ctrl-Shift-Enter. Then copy down to match the dates in your pivot table. HTH, Bernie MS Excel MVP "Giz" wrote in message ... I am trying to get at max, min, avg, the first and third quartiles of data for temperature for every day. The formula I would use for quartiles is "QUARTILE(selection,1)" for the first quartile, for example. How do I get that in a pivot table?? I have tried to figure it out but am having trouble, thanks. "Bernie Deitrick" wrote: Giz, Use a pivot table - will do all that and more, automatically. HTH, Bernie MS Excel MVP "Giz" wrote in message ... Hi, I have a table with 3 columns: Site, Temperature, and Date. The "Site" field contains multiple unique "Sites" (7 to be precise), and multiple records per unique site on any given date. What I would like to do, for any given day, is summarize (avg, max, min, etc) each site's temperature . The way I have done this before is to have a table with, using this data as an example, 8 columns: Date and 1 column for each site, with temperature the data under each site's column. I would then just use the avg, max, min, etc functions in one cell and drag it across to get the summary for each site. This would be tedious here, however, since I would have to do a bunch of copying and pasting to get the data arranged in this manner. Any way around this through nested functions or VB code?? Thanx in advance for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a chart based on a 2 dim data range dynamical in 1 d | Charts and Charting in Excel | |||
Create Charts based on data in Table | Charts and Charting in Excel | |||
Purge data based on another Excel file??? | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
How do I validate data using different lists based on the data in. | Excel Discussion (Misc queries) |