Excel formula to find largest negative value

Question

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

Answer 1

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.

Answer 2

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

Answer 3

Good day HansV and Bernard,

Very much thanks for the explanation.

Best regards