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

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

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

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