3

So I am filling a cell with Blue, Green, Yellow, or Red based on a Percentile Rank that is returned, that is numbers like ( >=90th=Blue, >=60th=Green, >=30th=Yellow, <30th=Red )

Currently I am using this Conditional Formatting in each of those cells, Which Does Work!

=VALUE(LEFT(INDIRECT(ADDRESS(ROW(),COLUMN())),LEN((INDIRECT(ADDRESS(ROW(),COLUMN()))))-2)) <30

Basically what this does is find the row and address of cell containing the formula, which works great with Conditional Formatting by true formula. In my case INDIRECT(ADDRESS(ROW(),COLUMN())) = $E$34 as an example.

So after some evaluating it looks like this =VALUE(LEFT($E$34,LEN(($E$34-2)) <30

If $E$34 = 4th Then my formula evaluates to True since its less than 30 and colors the cell Red for me.

So my question is, is there an easier or more efficient way to format the cell based on these types of values that have suffixes like, st, nd, rd, and th for numbers like 4th, 13th, and 100th?

Thanks,

2
  • 3
    There shouldn't be any need to use the whole INDIRECT/ADDRESS construction - you should be able to refer to the top left cell of your range in the formula so it only has to look like your shorter version, e.g. if range to format is E34:E100 select than range and apply this formula =LEFT(E34,LEN(E34)-2)+0<30 - that will work for the whole range May 30, 2013 at 19:45
  • Thanks Barry, That also does work. I also like how just adding 0 to the formula makes the text a number. I had tried to reference the actual cell range relative like you have but was running into problems in the formula bar of the conditional fomatting. Thanks for your comment.
    – Scheballs
    May 31, 2013 at 11:24

1 Answer 1

3

I know, this does not answer the question but is a relatively simple way to address a problem of this kind assuming that the Percentile Rank is not returned by a calculation within Excel:

Remove the ordinal units (along the lines indicated in OP), use an Icon Set of four traffic lights, reduce the width of ColumnE and reinstate the units with a formula, say in ColumnF, such as

=E1&IF(OR(MOD(E1,10)={0;4;5;6;7;8;9},INT(MOD(E1,100)/10)=1),"th",CHOOSE(MOD(E1,10),"st","nd","rd"))

SU601650 example

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .