Hi,
I have a table with data. That contains temperatures and dates, but it's not every day. For example:
DATA
| 1 | A | B | C | D |
| 2 | Date | Station A Temp | Station B Temp | Station C Temp |
| 3 | Nov 1 | 1 | 3 | 5 |
| 4 | Nov 2 | 2 | 4 | 6 |
| 5 | Nov 3 | 7 | 5 | 3 |
| 6 | Nov 5 | 8 | 6 | 4 |
| 7 | Nov 6 | 7 | 5 | 9 |
| 8 | Nov 9 | 10 | 6 | 8 |
Main Goal: I want to create a formula to pull the minimum temperature between the three stations for a specific date if there is data available (e.g. what's the lowest temperature on Nov 1).
Secondary Goal (Optional): Pull the station associated with the min value.
My set up to populate the table:
RESULT
| 1 | A | B | C |
| 2 | Date | Min Temp | Station |
| 3 | Nov 1 | ||
| 4 | Nov 2 | ||
| 5 | Nov 3 | ||
| 6 | Nov 4 | ||
| 7 | Nov 5 | ||
| 8 | Nov 6 | ||
| 9 | Nov 7 | ||
| 10 | Nov 8 | ||
| 11 | Nov 9 |
When I write my formula, I assume I believe I need an INDEX/MATCH to see if the raw DATA has data for that date. For example, Nov 1 has DATA, so my formula should detect that 1 is the minimum value in the row, and my RESULT in B3 should return 1. There is no DATA for Nov 4, so my RESULT in B6 would just be #N/A.
If anyone could provide any ideas, that would be greatly appreciated!
Extra Information
I have done some research and I found two sites that give two different formulas (for my main goal, I haven't looked at my second goal yet).
Option A - It works, but I don't understand a portion of the formula
https://www.exceldemy.com/index-match-minimum-value/
- Under section 4, "Fusing SMALL Function with Index-Match"
- =SMALL(INDEX($C$4:$G$14, MATCH($E$16, $B$4:$B$14, 0), 0), ROWS($D$17:$D17))
My formula is:
=SMALL(INDEX($B$2:$D$7,MATCH(A12,$A$2:$A$7,0),0),ROWS(A12:A12))
- I understand the formula up until ROWS. I have no clue what it's doing there, and the formula doesn't work without it, so hopefully someone can explain it to me. Thanks!
- I also like how this is dynamic. I can click and drag and the other cells below will populate correctly
Option B - Couldn't get it to work
https://www.mrexcel.com/board/threads/using-minifs-with-dynamic-row.1124635/
- =MINIFS(AF1:AL1,INDEX(AF2:AL15,MATCH(AQ3,D2:D15,0),0),0 --> Feb 19, 2020 comment
Option C - Seems straightforward, but seems to have some faults
https://superuser.com/questions/1797826/excel-minifs-formula-to-return-another-value-in-the-same-row
- =INDEX(E:E,MATCH(MINIFS(B:B,C:C,J2),B:B,0))
My formula is:
=INDEX(B3:I3,MATCH(MINIFS(A2:A4,A2:A4,A11),A2:A4,0))
- This is sample data, but in my actual spreadsheet, it doesn't necessarily return the minimum value
- I can't click and drag to populate other cells because missing dates causes mis-alignment, so this seems better suited when data is less dynamic or cleaner (for a lack of better words)
- Not understanding certain sections of the formula when I try to adapt it to my needs
- INDEX(B3:I3,MATCH(MINIFS(X?:X?,A2:A4,A11),Y?:Y?,0))
- I understand the structure of MINIFS, but maybe because it's combined with matching that throws me off. In the spot of X?:X? and Y?:Y?, I originally had B3:I3 in both places, but it didn't work until I changed both to A2:A4.
Thanks in advance!
Accepted Solutions