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.
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
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 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.
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.
