» Find the minimum positive value in Microsoft Excel
CATEGORY - Excel Array Formulas , Other Q&A Formulas
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- Treason: Liberal Treachery from the Cold War to the War on Terrorism
- Business Plans Kit for Dummies (With CD-ROM)
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- Microsoft Office XP Step-By-Step (With CD-ROM)
- Investments + S&P Card + Powerweb + StockTrak discount coupon
- The Accounting Game : Basic Accounting Fresh from the Lemonade Stand
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.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
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