Log In
Register
Home
New Member
November 27, 2018
Offline
I am working on a weather dashboard. I did a pivot table for the max and min temperature by day. On the dashboard tab, when you enter the date in cell D5, it returns the max and min temperature. I also want it to show the time of the max and min temperature (cells F5 and H5). The SumIfs function works if there is only one instance of the max (or min) temperature in a day. For example, on 9/18/18, the temperature of 87.6 occurs 4 times. As a result, it is summing all 4 times. I am looking for a formula that will essentially do a VLookup based on date and temperature that will return the time of the first instance it meets both criteria. Using the 9/18/18 example, the time it should return for the max temperature is 12:24PM.
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Rich
Please refer attached.
I use a VLOOKUP combined with a PivotTable.
Please note that your original formulas in cell E5 and G5 are not correct as they just search for the first entry which may not be the Max/Min Temperature.
Furthermore the range covered is only up to rows 1038.
Hope this helps.
Sunny
Answers Post
Trusted Members
Moderators
November 1, 2018
Offline
For your max temp time:
=AGGREGATE(15,6,1/1/((acuriteweather[Date]=D5)*(acuriteweather[Outdoor Temperature]=E5))*acuriteweather[Timestamp],1)
For the min temp time:
=AGGREGATE(15,6,1/1/((acuriteweather[Date]=D5)*(acuriteweather[Outdoor Temperature]=G5))*acuriteweather[Timestamp],1)
Also, since you have a pivot table, you can use GETPIVOTDATA for the Min and Max temps:
=IFERROR(GETPIVOTDATA("High",TempPivot!$A$3,"Date",D5,"Months",MONTH(D5)),"")
=IFERROR(GETPIVOTDATA("Low",TempPivot!$A$3,"Date",D5,"Months",MONTH(D5)),"")
New Member
November 27, 2018
Offline
Velouria said
For your max temp time:=AGGREGATE(15,6,1/1/((acuriteweather[Date]=D5)*(acuriteweather[Outdoor Temperature]=E5))*acuriteweather[Timestamp],1)
For the min temp time:
=AGGREGATE(15,6,1/1/((acuriteweather[Date]=D5)*(acuriteweather[Outdoor Temperature]=G5))*acuriteweather[Timestamp],1)
Also, since you have a pivot table, you can use GETPIVOTDATA for the Min and Max temps:
=IFERROR(GETPIVOTDATA("High",TempPivot!$A$3,"Date",D5,"Months",MONTH(D5)),"")
=IFERROR(GETPIVOTDATA("Low",TempPivot!$A$3,"Date",D5,"Months",MONTH(D5)),"")
Velouria, when I use the AGGREGATE function you outlined, I get a #VALUE! return. What does the 1/1((accurite...) represent? I understand the rest of the formula. Thanks.
Trusted Members
Moderators
November 1, 2018
Offline
This part:
((acuriteweather[Date]=D5)*(acuriteweather[Outdoor Temperature]=G5))
creates an array of 1s and 0s. Multiplying by the time stamp returns an array of time values and zeroes. Since we want the minimum, we don't want the 0 values in there, so 1 divided by that array, will return an array of fractions and #DIV/0 errors. Finally we divide 1 by that array again, to invert the fractions (the errors remain unchanged). Passing 6 as the second argument to AGGREGATE tells it to ignore errors, so we get the minimum time value, not 0 or an error.
Here's your file with the formulas in place.
1 Guest(s)
