|

# » Find the minimum positive value in Microsoft Excel

The following numbers appear in cells A1:A5

 A1 -50 A2 -25 A3 25 A4 50 A5 100

Enter the following formula in cell D1: {=MIN(IF((A1:A5>0)*(A1:A5),(A1:A5)))}

The Result = 25

Please note: The formula is an Array Formula, after entering the formula in the cell, press F2 and than press Ctrl+Shift+Enter

Rate This Tip
 1 2 3 4 5
Rating: 3.04     Views: 47341
how to use a varible inside a formula
Ellen  Posted on: 31-12-1969
the following sums next 7 columns on the right
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[7])"

How do I sum next N columns on the right?
dim N=9
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[&N&])" 'will not work

Thanks very much
Reply: how to use a varible inside a formula - Ellen from Apex,NC wrote on August 13, 2003 12:00 PM EST
Alan  Posted on: 31-12-1969
Hi Ellen,

When you declare the variable, you need to state what it is, not its value:

To keep it simple, try this:

Sub Alan()

Dim N As Integer

N = 9

ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[" & N & "])"

End Sub

Note that this will write over whatever was already in the active cell as written!

Hope that works,

Alan.
Name
Comment Title