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

Toddcan · Apr 15th, 2002, 06:32 AM

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 · Apr 15th, 2002, 06:56 AM

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 · Apr 15th, 2002, 06:57 AM

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

+ 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 15th, 2002, 06:32 AM	#1
Toddcan New Member Join Date: Apr 2002 Posts: 11	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

Apr 15th, 2002, 06:56 AM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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

Apr 15th, 2002, 06:57 AM	#3
s-o-s Board Regular Join Date: Apr 2002 Location: Kissimmee, Florida Posts: 384	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 + 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.