If you have a list of descending values and want to find the position of the largest negative number, how would you do it?
First of all, when talking about negative numbers, which number is greater and which is lesser? Is -8 greater than -3, or is it less than -3? The answer is -8 is less than -3. So when finding the largest negative number, the number closer to zero is greater than the number further from zero.
To sum it up, -3 is larger than -8. Keeping this in mind will help when trying to find the largest negative number in a list.
A question to ask is: When would you want to find the position of the largest negative number in a descending list?
When Would You Want to Find the Largest Negative Number?
Imagine that you have a starting balance that is reduced each week, and you want a formula to calculate what week the balance ends up being below zero. This would be the week that has the largest negative number. Now this can change – the starting balance could be different; the amount it is reduced each week could be different.
See the example below:
So you want to find the week number for the largest negative number. You can see in the example image that this is Week 17.
But now, how would you use a formula to find it automatically?
What Formula Can Find the Largest Negative Number?
The formula would need to ensure that all variables of are handled. The starting balance could change; the amount the balance is reduced each week could vary; the value that you want returned is to the left of the value you are checking. So a standard VLOOKUP wouldn’t work.
How can this be handled? The formula to calculate would include four separate functions – INDEX, MATCH, LARGE, and COUNTIF. And here it is:
Now that’s a lot of functions to combine, and the formula can be complex at first glance. However, breaking it makes it simpler and easier to understand what’s happening. Lets’s start breaking down the formula.
This function counts the number of numbers in all of column B that are greater than or equal to zero. This seems counter-intuitive since we want the largest number that is less than zero. But hold on…we’ll get to that later.
Using the data above, this function returns the value of 16, since there are 16 numbers in column B that are greater than or equal to zero.
This function returns the nth largest number in column B. The nth number that the LARGE function is being told to return is the result of the above COUNT function, which in this example, is 16.
The 16th largest number in column B is 5. Still not the number we are looking for – that comes next.
This takes the number returned from the above COUNTIF function and adds one to it. Why do this?
Remember that the result of the COUNTIF function was 16, and this number was passed to the LARGE function. Without adding 1 to the 16 that is being passed to the LARGE function, the LARGE function would take the list of of descending numbers in column B and find the 16th largest number that is greater than or equal to zero.
That is not what is wanted. What is actually wanted is the first negative number (the greatest negative number).
That is why 1 is added to the 16. Since, in the list of descending numbers, there are 16 numbers greater than or equal to zero, the next largest number would be the largest negative number.
And that is the number that we want! In this example, the 17th largest number is -28.
Now, to get the matching week name. The example shows that the week name is in cell A18 and is Week17. Since the week name is in the column to the left of the searched value in column B, a standard VLOOKUP function won’t work. A combination of INDEX and MATCH will allow the value to the left to be returned.
Here the MATCH function takes the result of the functions above, which is -28. It then looks in column B for that number and returns its position. The match type argument is 0. This tells the function that an exact match is wanted. The -28 in column B is in position 18.
Lastly, the resulting 18 is passed to the INDEX function part of the formula.
The index function now looks in the 18th cell of column A and returns that value, which is:
Here you have seen how to use a formula in Excel to find the largest negative number. While this formula uses four different functions, and does seem complex, it is very flexible.