Search code examples
excelmultiple-columnsrowsdifference

Finding the row with the biggest difference of two columns in excel

I have a table with three columns. One for the date, the other min temperature and the other max temperature. I want to find the day that showed the greatest difference between the two temperatures. How do I do that in excel? This is for instance what i have

02/02/2010  30.0   31.0
02/03/2010  29.4   27.2

so for instance 02/03/2010 had the greatest variation in temperature

Solution

  • Try:

    =INDEX(temps[Date],MATCH(MAX(ABS(temps[Temp1]-temps[Temp2])),ABS(temps[Temp1]-temps[Temp2]),0))

    I used structured references where temps is the name of the table, but you can use regular addressing if you prefer.

    In some earlier versions of Excel, you may need to "confirm" this array-formula by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar