Nesting IF statements, Conditional Formatting
 

Hi Folks,

I'm hoping someone has an easy answer.

I have a dillemma I wanted to address with conditional formatting but
the conditional formatting only allows for 3 circumstances.

I have a column of percentages - 0 through 100%.

I have a series of ranks - A through F and X.
A = 0 through 20%
B = 21 through 40%
C = 41 through 60%
D = 61 through 80%
F = 81 through 100%
X = 0%

If the percentages are in column I, and I want column J to reflect the
proper letter rank, based on I, how do I nest my formula in J to show
the right information, *or* how do I use conditional formatting?

I initially thought maybe an =if((I4<20%),A,if(I4<40%),B,if(etc...)
but got lost in the parentheses... is there an easier way?!
Helllllllp!

Or laugh - either way it'll get done eventually :)

Regards

Ben / ND

Have a look at VLOOKUP() in the help menu.

--
Regards,
Dave


"Nick Danger" wrote:

Hi Folks,

I'm hoping someone has an easy answer.

I have a dillemma I wanted to address with conditional formatting but
the conditional formatting only allows for 3 circumstances.

I have a column of percentages - 0 through 100%.

I have a series of ranks - A through F and X.
A = 0 through 20%
B = 21 through 40%
C = 41 through 60%
D = 61 through 80%
F = 81 through 100%
X = 0%

If the percentages are in column I, and I want column J to reflect the
proper letter rank, based on I, how do I nest my formula in J to show
the right information, *or* how do I use conditional formatting?

I initially thought maybe an =if((I4<20%),A,if(I4<40%),B,if(etc...)
but got lost in the parentheses... is there an easier way?!
Helllllllp!

Or laugh - either way it'll get done eventually :)

Regards

Ben / ND

Try:

=IF(I1=0,"X",CHAR(64+CEILING(I1*100,20)/20+(I10.8)))


--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Nick Danger" wrote in message
...
Hi Folks,

I'm hoping someone has an easy answer.

I have a dillemma I wanted to address with conditional formatting but
the conditional formatting only allows for 3 circumstances.

I have a column of percentages - 0 through 100%.

I have a series of ranks - A through F and X.
A = 0 through 20%
B = 21 through 40%
C = 41 through 60%
D = 61 through 80%
F = 81 through 100%
X = 0%

If the percentages are in column I, and I want column J to reflect the
proper letter rank, based on I, how do I nest my formula in J to show
the right information, *or* how do I use conditional formatting?

I initially thought maybe an =if((I4<20%),A,if(I4<40%),B,if(etc...)
but got lost in the parentheses... is there an easier way?!
Helllllllp!

Or laugh - either way it'll get done eventually :)

Regards

Ben / ND