Share via

Excel formula to find largest negative value

Anonymous
2016-05-16T15:22:03+00:00

Good day Everyone,

I want a formula in Excel 2007 to find the largest negative value in an array. The array is in one column and consists of both positive and negative values.

I searched online and found this suggested formula, =MAX(IF(B5:B30<0,B5:B30)), but it does not work for me. I keep getting #VALUE!

Can somebody help?

Thank you.

Best regards

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2016-05-16T17:39:33+00:00

    A formula such as =IF(AF30="Stopped",G30,VLOOKUP($AD30,Data_D!$A$6:$G$2256,6,FALSE)) is a "standard" formula, there is no need to confirm it with Ctrl+Shift+Enter.

    The formula =MAX(IF(B5:B30<0,B5:B30)) is an array formula because the first argument of IF is normally a single condition, but here there is a condition for each of the cells in B5:B30. Hence, IF(B5:B30<0,B5:B30) does not return a single value, but an array of values. Such formulas must be confirmed with Ctrl+Shift+Enter.

    0 comments
Answer accepted by question author
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2016-05-16T15:27:03+00:00

    Did you confirm the formula with Ctrl+Shift+Enter? That is essential for such a formula, to make it an array formula.

    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2016-05-16T16:25:28+00:00

    As HansV points out, you must array-enter the formula.

    best wishes

    0 comments
  3. Anonymous
    2016-05-16T17:33:07+00:00

    HI, HansV MVP,

    Many thanks for your advice.

    I did try that Ctrl+Shift+Enter after I re-entered the formula in the same cell, but it did not work.

    After I read your advice, I tried it on another cell and it worked. Thanks again.

    The curious thing is that I have not encountered any problem when previously used other formulae without the Ctrl+Shift+Enter - example, =IF(AF30="Stopped",G30,VLOOKUP($AD30,Data_D!$A$6:$G$2256,6,FALSE))

    Have I misunderstood something about the Ctrl+Shift+Enter function when entering formulae.

    Thank you (:

    0 comments