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
Seamus2626Asked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
I see you closing the request.  My solution wasn't correct?
0
 
NorieVBA ExpertCommented:
Seamus

How will the range be dynamic?

Will the no of columns change or the no of rows change, or both?
0
 
NBVCCommented:
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Thanks
0
 
Seamus2626Author Commented:
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
0
 
Seamus2626Author Commented:
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
0
 
NBVCCommented:
it works for me.  Do you have any NA# errors in the database in CashFormula?

If still having error, please post workbook sample.
0
 
Seamus2626Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.