Summarizing data based on ID

[Guest]

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

 

[vezerid]

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

 

[Bernie Deitrick]

Giz,

Use a pivot table - will do all that and more, automatically.

HTH,
Bernie
MS Excel MVP

 

[Guest]

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]

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

 

[Guest]

That works, but I don't think it differentiates between the different sites,
in the third column in the original data.

 

[Bernie Deitrick]

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)*(Sheet1!$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

 

[Guest]

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

 

[Guest]

I got it to work, thank you so very much for the help.