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. Anonymous
    2016-05-16T17:34:18+00:00

    Hi Dana D,

    HansV MVP has provided the solution for me.

    Thank you ! (:

    0 comments
  2. Anonymous
    2016-05-16T17:36:09+00:00

    Hi Bernard Liengme,

    HansV MVP has provided the solution for me - similar to your first example.

    Thank you ! (:

    0 comments
  3. Anonymous
    2016-05-16T17:40:58+00:00

    In the formula  =MAX(IF(B5:B30<0,B5:B30)), the IF part returns many values so we need an array formula.

    In =IF(AF30="Stopped",G30,VLOOKUP($AD30,Data_D!$A$6:$G$2256,6,FALSE)) the IF returns one of two values: either the G30 value of the result of the VLOOKUP. So this is not an array formula.

    Some simplification here but it should clarify things a bit

    best wishes

    0 comments