Thanks:  0
Likes:  0

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

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

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•