Seamus2626
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
Hi,
I am using this formula to calculate some percentiles.
I want to make the range dynamic, can someone show how?
Many thanks
Seamus
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
Enter a name you want then in source field enter formula:
=OFFSET('Input - D1'!$W$8,0,0,COUNTA('Input
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,
where MyRange is then Dynamic Named range name
ASKER
Imnorie, just the number of rows will change, im trying to work in your solution nb_vc
Thanks
Thanks
ASKER
Hi nb_vc,
I am getting an #NA error for the above formula,
=PERCENTILE.EXC(IF(Test=1, CashFormul a),'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 am getting an #NA error for the above formula,
=PERCENTILE.EXC(IF(Test=1,
in the name manager i have
=OFFSET('Input - D1'!$W$8,0,0,COUNTA('Input
Thanks
ASKER
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
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.
If still having error, please post workbook sample.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks
How will the range be dynamic?
Will the no of columns change or the no of rows change, or both?