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,
=LEFT(E34,LEN(E34)-2)+0<30
- that will work for the whole range