Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

Add a dynamic range

=PERCENTILE.EXC(IF(Test=1,'Input - D1'!$W$8:$AH$107),'S7 - Product Risk Scenario'!S7)

Hi,

I am using this formula to calculate some percentiles.

I want to make the range dynamic, can someone show how?

Many thanks
Seamus
Avatar of Norie
Norie

Seamus

How will the range be dynamic?

Will the no of columns change or the no of rows change, or both?
Go to Formulas tab, Define Name,

Enter a name you want then in source field enter formula:

=OFFSET('Input - D1'!$W$8,0,0,COUNTA('Input - D1'!$W:$W),12)

This assumes that column W has now blanks within the desired range.

Now replace the range with that named range..

e.g.

=PERCENTILE.EXC(IF(Test=1,MyRange),'S7 - Product Risk Scenario'!S7)

where MyRange is then Dynamic Named range name
Avatar of Seamus2626

ASKER

Imnorie, just the number of rows will change, im trying to work in your solution nb_vc

Thanks
Hi nb_vc,

I am getting an #NA error for the above formula,

=PERCENTILE.EXC(IF(Test=1,CashFormula),'S7 - Product Risk Scenario'!S7)

in the name manager i have

=OFFSET('Input - D1'!$W$8,0,0,COUNTA('Input - D1'!$W:$W),12)

Thanks
I've requested that this question be closed as follows:

Accepted answer: 0 points for Seamus2626's comment #a39645453

for the following reason:

Just needed to drop the a in counta and set up another dynamic range for Test and im all good!

Many thanks
it works for me.  Do you have any NA# errors in the database in CashFormula?

If still having error, please post workbook sample.
ASKER CERTIFIED SOLUTION
Avatar of NBVC
NBVC
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That was a mistake, i was meant to allocate points, it works fine, i got rid of counta and set up a dynamic range for my first named range test and it was all good!

Thanks