How to find the least negative value in an array
Requirement:
The user has a formula with an array of numbers. From this array the user wants to get the least negative number (the negative number closest to 0).
Solution:
=MAX(IF({-668;-576;-484;-393;-303;-211;-120;-29;62;153;244;335;426;517;609;700;791;885;977}<0,{-668;-576;-484;-393;-303;-211;-120;-29;62;153;244;335;426;517;609;700;791;885;977}))
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to take a piece of spreadsheet to a sniped code and translate into array form |
| How to insert VLOOKUP into cell with variable array |
| How to use SumProduct array formula |
| How to combine LARGE and SUMIF - Array formula |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
