Contain your excitement!

I’ve got the following rules which highlight a text cell depending on the decimal value of dividing one cell from another – effectively (or not!) highlighting areas based on their percentage.

GREEN =(I15/B15)=0
RED =(I15/B15)>0.33
AMBER =

What formula can I use to highlight in amber those text cells where the calculation result is between 0.33 and 0.99?

I could do a hidden column where I put the result then just use that value to determine colours but it’s bugging me how to do it as above!

Thanks in advance.

do you not just use and AND((I15/B15)>0.33,(I15/B15)<0.99))

I am properly flying by seat of the pants here as I’m sure I alwyas get confused with AND syntax in excel

You’re saying here that > 0.33 is RED but you want between 0.33 and 0.99 is AMBER. Does not compute.

Thank you both. I think I need to go and have a rethink.

Thats a good point.

Amber should be 0 to 0.33, red anything above?

as it only goes up to 0.99 will it ever be 1?

if it goes up to 0.991 for eg you’ll get no formatting

Its for showing progress levels of items being processed. Column ‘I’ is the number of unprocessed items so when all items are done then it should show as 0 hence my original formula. To be honest it’s not a major feature but just one of those tasks which happily gets the brain working!

Thanks again

Any reason not to use an RAG formatting style rather than hand cranking the formulae? (conditional formatting/icon sets, pick whatever suits then back to conditional formatting/manage rules to set the thresholds)

presuming you want either red, amber or green – if you have conditional formatting for red & green then just format all the cells as amber and then if it meets the condition it’ll either turn it red or green.

Thanks again for all your help. I think if there’s a next time then I’ll do a hidden column and base the conditional formatting on just that instead of multiple cells.

Much obliged 👍