Conditional formatting between range of numbers

innocent

Hi Anaplaners,

I have 5 line items (Number Formats) showing variances. I want to apply a conditioner formatting to each of the 5 line items based on a range of numbers.

E.g any numbers between -1500 to 2500 should have a Green colour and any numbers outside these range should get a RED colour .

Please is there any workaround to achieve this?.

R_Goza

@innocent

You'll have to create a conditional formatting line item for each variance to achieve what you're looking for unless you create a line item subset. The logic would look something like the screenshots below whether you create conditional formatting line items for each variance or go the line item subset route . I would also recommend creating a Lookup or Reference module (like I have done) for your min and max ranges instead of hardcoding them in the formula. This will allow you to easily update the values if the ranges need to change.

innocent

Thanks @R_Goza I was thinking there's a way to use just one line item as the only conditional formating line for the different 5 line items.

This is a good solution thanks a lot.

Himanshu1998

Hi @innocent

You can use same “cf variance” line item as conditional formatting for all 5 line items

AjayM

Hi @Himanshu1998 ,

Interested to know how you would achieve this using same 'cf variance' lineitem, as you shouldn't color all 5 lineitems the same, in case their values differ in the range. Ex: 2 of the 3 variances are falling within the range while the others are falling out. Now only 2 of them shall turn GREEN while the others turn RED.

Cheers!

pyrypeura

One option for getting to use one line item would be creating line item subset from these 5 line items and creating a module dimensionalised with LISS and two line items Value/Collect and CF variance. Then Value/Collect line item could be conditionally formatted using CF Variance line item.

This solution would consume more space but would make maintenance easier for example if you add more line items to comparison or if you decide to change rules for your conditional formatting.

AjayM

Thanks @pyrypeura. LISS did cross my mind, but it has to be efficiently utilised, just for the purpose, as you said.

FYI @innocent : 'LIS CF Variances' is the lineitem subset holding all 5 variance lineitems.

Cheers!

innocent

Thank you very much @pyrypeura and @AjayM this was very helpful explanation. Thanks also @Himanshu1998.