Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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,

share|improve this question
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 –  barry houdini May 30 '13 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 '13 at 11:24

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.