I'm stuck on a silly formula ... help plz! Not that easy...

[Toddcan]

I want to do this, but I can't figure out how...


in cell A, I want to return the following number...

the minimum value GREATER than zero when performing a simple subtraction of Cell B -Cell (C..F).



eg- Cell B has the number 5
Cell range c..f contain the following numbers: 3,6,7,9


I want a formula to compare the results of 5-3, 5-6, 5-7, 5-9 and return the smallest non-negative number to cell A. The answer in this case is 2.



But, how do I write a formulae to do this? I'm not an excel newbie, so it's ticking me off

 

[Aladin Akyurek]

Array-enter:

=MIN(IF(B1-(C1:F1)>0,B1-(C1:F1)))

In order to array-enter a formula, hit control+shift+enter, instead of just enter.

Aladin

 

[s-o-s]

A nice little problem with a solution that will need you to go into Array Formulas.

Enter the formulas as below but do NOT press enter.

=MIN(IF($B$2-C2:F2>0,$B$2-C2:F2,""))


Instead you must press and hold

<Ctrl> + <Shift> and then Press Enter.

If you have entered it correctly you will now see in the formula bar.

{=MIN(IF($B$2-C2:F2>0,$B$2-C2:F2,""))}

and you will see the correct answer in cell A1.

Excel Help is good if you search for Array Formula if you want to learn more about these powerful functions.