JimS
03-24-2010, 07:43 AM
I’m using VBA to populate some cells with a Percentile Formula but want to use a Range Named Cell as the formulas percentile value variable.
For example, currently the formula is hard-coded to use 0.97 (see below) but I would like it to reference a Named Range Cell so the user can change what percentile they would like to use in the calculation.
I have created a Named Cell labeled “Percent” and referenced it with the following line in my code:
pTile = Range("Percent").Value
How can I use the pTile in the following line of VBA instead of using the 0.97 (currently the hardcoded value)?
.Cells(3, 2) = "=Percentile(B6:B" & LastRow & ", 0.97)"
Also, the user simply enters a whole number in the 'Percent' Named Range Cell (ie: 91, 92, 93, 94, 95, etc) so vba would need to convert the users entry to be in the 0.00 format used in the vba formula.
Can this be done easily?
Thanks for any help…
JimS
For example, currently the formula is hard-coded to use 0.97 (see below) but I would like it to reference a Named Range Cell so the user can change what percentile they would like to use in the calculation.
I have created a Named Cell labeled “Percent” and referenced it with the following line in my code:
pTile = Range("Percent").Value
How can I use the pTile in the following line of VBA instead of using the 0.97 (currently the hardcoded value)?
.Cells(3, 2) = "=Percentile(B6:B" & LastRow & ", 0.97)"
Also, the user simply enters a whole number in the 'Percent' Named Range Cell (ie: 91, 92, 93, 94, 95, etc) so vba would need to convert the users entry to be in the 0.00 format used in the vba formula.
Can this be done easily?
Thanks for any help…
JimS