Using a variable in a formula

[Guest]

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

 

[Leith Ross]

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

 

[Guest]

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

 

[Guest]

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)"