Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable in a formula
I have defined this range:
Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Dim Population As Range Set Population = Selection And then I want to use this range in the percentile function, but I haven't been able to figure it out: Range("B1").Select ActiveCell.Formula = "=PERCENTILE(MarketValues,0.2)" Any help would be greatly appreciated. Thanks. Noah |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable in a formula
Hello Noah, This should work for you... Dim marketvalues As Range marketvalues = Range("A1:A" & Rows.Count).End(xlUp) Range("B2").Formula = "=PERCENTILE(MarketValues,0.2)" Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=48000 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable in a formula
Where are you defining "MarketValues"?
Is MarketValues a named range that is the same as population? If so, this seemed to work for me: Sub test() Dim Population As Range Set Population = Range("A1", Range("A1").End(xlDown)) Application.Names.Add Name:="Marketvalues", _ RefersTo:=Population Range("B1").Formula = "=PERCENTILE(MarketValues,0.2)" End Sub "Noah" wrote: I have defined this range: Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Dim Population As Range Set Population = Selection And then I want to use this range in the percentile function, but I haven't been able to figure it out: Range("B1").Select ActiveCell.Formula = "=PERCENTILE(MarketValues,0.2)" Any help would be greatly appreciated. Thanks. Noah |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable in a formula
I couldn't get it to work w/o some changes. You need a set statement for
marketvalues. For me, the Range statement only returned cell A1. In the Percentile function, you have to refer to the marketvalues.address as Excel won't recognize marketvalues (you'll get a NAME error). Dim marketvalues As Range Set marketvalues = Range("A1", Range("A" & Rows.Count).End(xlUp)) Range("B2").Formula = "=PERCENTILE(" & marketvalues.Address & ",0.2)" "Leith Ross" wrote: Hello Noah, This should work for you... Dim marketvalues As Range marketvalues = Range("A1:A" & Rows.Count).End(xlUp) Range("B2").Formula = "=PERCENTILE(MarketValues,0.2)" Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=480006 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable within a formula | Excel Worksheet Functions | |||
Using a variable in formula | Excel Programming | |||
Using a second variable within a formula... | Excel Discussion (Misc queries) | |||
Variable in a formula | Excel Programming | |||
Variable to formula. | Excel Programming |