nested if #n/a problem

**penfold** · 05-09-2008, 01:52 PM

Hi,

In the following, if the result is FALSE I need #n/a to be returned.

=IF(QUERY!$G$14=1,PERCENTRANK($C$1:$C$118,$C5)*100,IF(QUERY!$G$14=2,PERCENTRANK($C$1:$C$16,$C5)*100,IF(QUERY!$G$14=3,PERCENTRANK($C$17:$C$28,$C5)*100,IF(QUERY!$G$14=4,PERCENTRANK($C$29:$C$47,$C5)*100,IF(QUERY!$G$14=5,PERCENTRANK($C$48:$C$60,$C5)*100,IF(QUERY!$G$14=6,PERCENTRANK($C$61:$C$98,$C5)*100,IF(QUERY!$G$14=7,PERCENTRANK($C$99:$C$118,$C5)*100)))))))

Where G14 = 2 or 6 #n/a is returned as I'd hoped. However, where G14 = 3, 4, 5 or 7 and the result is FALSE, "0" is returned.

By way of an example, in response to the following

IF(QUERY!$G$14=5,PERCENTRANK($C$48:$C$60,$C5)*100

if G14 does equal 5 I want all cells outside of C48:C60 to return an #n/a. As I said, this works for 2 and 6 but not for 3, 4, 5 and 7.

Many thanks

**martindwilson** · 05-09-2008, 04:40 PM

ok i just tested this ,named a sheet query
put your formula in a1 sheet 1
then tried all values 1 thro 7 in g14 sheet query
get #n/a for all
there is nothing in column c
i suspect column c is not formated the same thro'out.

**penfold** · 05-09-2008, 06:24 PM

Hi Martin,

Thanks for your response.

Unfortunately it still does not work for me.

In fact, I don't think I have the formula right at all.

Please could you take a look at the uploaded sheet attached.

When QUERY!$G$14 matches a logical test in the IF statement I want to perform a percentilerank in the specified cells and leave an #n/a in all other cells.

Hope this makes sense.

Thanks for your help.

**martindwilson** · 05-09-2008, 07:19 PM

right i split each one and just made g14 on sheet one the lookup value in cells L1-L7
also put original formula as if its looking at g14 in O1
you'll see n/a is returned only if the value in c5 is not present in the range you specified in each formula .once strung together the if statements have a problem!
why are you using c5 as your reference for x in percentrank?

**penfold** · 05-09-2008, 07:43 PM

C5 is not supposed to be static.

If the formula is in D1 it is to calculate the percentrank of C1 against C1:C16. If the formula is in D2 it is to calculate the percent ranks of C2 against C1:C16 etc.

Similarly, if the formula is in D17 it is to calculate the percentrank of C17 against C17:C28. If the formula is in D18 it is to calculate the percent rank of C18 against C17:C28 etc.

You say once strung together the statements have a problem - is there any way to make this work?

Thanks for your help.

**Domenic** · 05-09-2008, 08:45 PM

Try...

D1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ISNUMBER(MATCH(ROW(C1),ROW(CHOOSE(QUERY!$G$14,$C$1:$C$118,$C$1:$C$16,$C$17:$C$28,$C$29:$C$47,$C$48:$C$60,$C$61:$C$98,$C$99:$C$118)),0)),PERCENTRANK(CHOOSE(QUERY!$G$14,$C$1:$C$118,$C$1:$C$16,$C$17:$C$28,$C$29:$C$47,$C$48:$C$60,$C$61:$C$98,$C$99:$C$118),$C1)*100,#N/A)

Hope this helps!