I have an application that requiers excel to find out for me the hour of the
max temperature of a person in a given day. The person's temperature is
recorded in the following way in Excel:
Columns: 1 2 3
Day Hour Temperature

I want to use Pivot table and I can get the Max tempreature of the person on
all the days; however, I don't know to use Pivot table to get the Hour of the
highest temperature. A simple request... can anyone help?

I don't think that a pivottable will give you the info you want.

But you could use a formula.

Say your data is in A2:C500

And you put the date that you want to find in E1.

Then you can put this array formula in E2:

=MAX((A2:A500=E1)*(C2:C500))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

You could also use a formula like:
=MAX(IF(A2:A500=E1,C2:C500))
(still an array formula -- ctrl-shift-enter)




rlo wrote:

I have an application that requiers excel to find out for me the hour of the
max temperature of a person in a given day. The person's temperature is
recorded in the following way in Excel:
Columns: 1 2 3
Day Hour Temperature

I want to use Pivot table and I can get the Max tempreature of the person on
all the days; however, I don't know to use Pivot table to get the Hour of the
highest temperature. A simple request... can anyone help?

--

Dave Peterson

Dave,

Thanks... your formula will get me the highest temperature of the person of
a specified date in E2. What I need is the following: Find out the hour of
the highest temperature of the person every day. For example, I need a report
that shows:

Day 1, Hour 4, the person's temperature in Hour 4 of Day 1 that is the max
temperature of the person on Day 1

Day 2, Hour 12, the person's temperature in Hour 12 of Day 2 that is the max
temperature of the person on Day 2
....

Any thoughts? Will Pivot table do it?
I can create a Pivot table to show

Day 1, Highest temperature xx1
Day 2, Highest temperature xx2

Ron

"Dave Peterson" wrote:

I don't think that a pivottable will give you the info you want.

But you could use a formula.

Say your data is in A2:C500

And you put the date that you want to find in E1.

Then you can put this array formula in E2:

=MAX((A2:A500=E1)*(C2:C500))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

You could also use a formula like:
=MAX(IF(A2:A500=E1,C2:C500))
(still an array formula -- ctrl-shift-enter)




rlo wrote:

I have an application that requiers excel to find out for me the hour of the
max temperature of a person in a given day. The person's temperature is
recorded in the following way in Excel:
Columns: 1 2 3
Day Hour Temperature

I want to use Pivot table and I can get the Max tempreature of the person on
all the days; however, I don't know to use Pivot table to get the Hour of the
highest temperature. A simple request... can anyone help?

--

Dave Peterson

I don't quite understand what you want.

Why would you want to specify the date AND the hour to get the max temp for that
date?

Why not just specify the date?

If that's really what you meant...

I would use data|filter|advanced filter to get a list of unique dates for that
person (add headers to your data if you don't have them.

Put those dates in another location (a separate sheet????)

And then use the formula in the adjacent cells of those unique dates.

Debra Dalgleish describes it he
http://contextures.com/xladvfilter01.html#FilterUR

The pivottable that you wanted to use does essentially for you, but

rlo wrote:

Dave,

Thanks... your formula will get me the highest temperature of the person of
a specified date in E2. What I need is the following: Find out the hour of
the highest temperature of the person every day. For example, I need a report
that shows:

Day 1, Hour 4, the person's temperature in Hour 4 of Day 1 that is the max
temperature of the person on Day 1

Day 2, Hour 12, the person's temperature in Hour 12 of Day 2 that is the max
temperature of the person on Day 2
...

Any thoughts? Will Pivot table do it?
I can create a Pivot table to show

Day 1, Highest temperature xx1
Day 2, Highest temperature xx2

Ron

"Dave Peterson" wrote:

I don't think that a pivottable will give you the info you want.

But you could use a formula.

Say your data is in A2:C500

And you put the date that you want to find in E1.

Then you can put this array formula in E2:

=MAX((A2:A500=E1)*(C2:C500))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

You could also use a formula like:
=MAX(IF(A2:A500=E1,C2:C500))
(still an array formula -- ctrl-shift-enter)




rlo wrote:

I have an application that requiers excel to find out for me the hour of the
max temperature of a person in a given day. The person's temperature is
recorded in the following way in Excel:
Columns: 1 2 3
Day Hour Temperature

I want to use Pivot table and I can get the Max tempreature of the person on
all the days; however, I don't know to use Pivot table to get the Hour of the
highest temperature. A simple request... can anyone help?

--

Dave Peterson


--

Dave Peterson

Dave,

Let me try the following to see if it is clearer:

- With the A2:C500 data, I want use excel to generate a report that shows
the hour when the highest temperature is observed of a person on a given
day... something like below (as an example)

9/24/2009, 100.3, 10
9/25/2009, 101.9, 11
etc.

So, excel will need to figure out first what is the highest temperature of
the person on a given day (by comaring all 24 data points) and then also find
out the Hour that the highest temperature is observed and print the
information out.

Hope this helps :)


Ron

"Dave Peterson" wrote:

I don't quite understand what you want.

Why would you want to specify the date AND the hour to get the max temp for that
date?

Why not just specify the date?

If that's really what you meant...

I would use data|filter|advanced filter to get a list of unique dates for that
person (add headers to your data if you don't have them.

Put those dates in another location (a separate sheet????)

And then use the formula in the adjacent cells of those unique dates.

Debra Dalgleish describes it he
http://contextures.com/xladvfilter01.html#FilterUR

The pivottable that you wanted to use does essentially for you, but

rlo wrote:

Dave,

Thanks... your formula will get me the highest temperature of the person of
a specified date in E2. What I need is the following: Find out the hour of
the highest temperature of the person every day. For example, I need a report
that shows:

Day 1, Hour 4, the person's temperature in Hour 4 of Day 1 that is the max
temperature of the person on Day 1

Day 2, Hour 12, the person's temperature in Hour 12 of Day 2 that is the max
temperature of the person on Day 2
...

Any thoughts? Will Pivot table do it?
I can create a Pivot table to show

Day 1, Highest temperature xx1
Day 2, Highest temperature xx2

Ron

"Dave Peterson" wrote:

I don't think that a pivottable will give you the info you want.

But you could use a formula.

Say your data is in A2:C500

And you put the date that you want to find in E1.

Then you can put this array formula in E2:

=MAX((A2:A500=E1)*(C2:C500))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

You could also use a formula like:
=MAX(IF(A2:A500=E1,C2:C500))
(still an array formula -- ctrl-shift-enter)




rlo wrote:

I have an application that requiers excel to find out for me the hour of the
max temperature of a person in a given day. The person's temperature is
recorded in the following way in Excel:
Columns: 1 2 3
Day Hour Temperature

I want to use Pivot table and I can get the Max tempreature of the person on
all the days; however, I don't know to use Pivot table to get the Hour of the
highest temperature. A simple request... can anyone help?

--

Dave Peterson


--

Dave Peterson