Function to define a Range

Question

Is there a function or way to define a range in excel via a formula. 

Using a formula,  I would like to determine all of the cells in column A that have a specific value in column B.  I would like the formula to result with the range of cells.  Therefore knowing that i.e. cells A1, A3:A5 and A22 have the specified value next to them in column B.

My ultimate goal is to use the determined range in the percentile function.

Thank you

Answer 1

Hi,

You can do it all in one go like this ARRAY formula. See below on how to enter it. This looks for the letter A in column B and calculates the 30th percentile so chamnge A to the correct value and the percentile you require.

=PERCENTILE(IF(B2:B20="A",A2:A20),0.3)

This is an array formula which must be entered by pressing CTRL+Shift+Enter

and not just Enter. If you do it correctly then Excel will put curly brackets

around the formula {}. You can't type these yourself. If you edit the formula

you must enter it again with CTRL+Shift+Enter.

Answer 2

Is there a function or way to define a range in excel via a formula. 

 

Using a formula,  I would like to determine all of the cells in column A that have a specific value in column B.  I would like the formula to result with the range of cells.  Therefore knowing that i.e. cells A1, A3:A5 and A22 have the specified value next to them in column B.

 

My ultimate goal is to use the determined range in the percentile function.

 

Thank you

 

Hello,

Try an array formula like

=percentile(if(B1:B99=13,A1:A99),50%)

if your specific value in column B is 13. Don't forget to enter your formula with CTRL + SHIFT + ENTER, not just with ENTER.

If you are interested in a VBA solution, a similar problem is solved here:

http://sulprobil.com/html/medianif.html

Regards,

Bernd