Excel Forum

Logical Formulas

Shameen

New Member

Members

Forum Posts: 2

Member Since:
February 5, 2017

Offline

February 5, 2017 - 5:16 pm

I am looking for help to apply conditional formatting (logic formulas) on the attached sheet where I would like to apply conditions in one cell as follows. At the same time, some of the criticality will be low and and ingerty will be high. I would like to return the anwer on the group cell

If any HIGH CRITICAL station is RED, then group = RED.
If greater than or equal to 50% of the HIGH CRITICAL stations are AMBER (and no less than 2) then Group = RED
If greater than or equal to 70% of MEDIUM CRITICAL stations are RED 9 (and no less than 3) then Group = RED
If less than 50% of HIGH CRITICAL stations are AMBER & greater than or equal to 70% of MEDIUM CRITICAL stations are AMBER then Group = RED
If any HIGH CRITICAL station is AMBER, then Group = AMBER
If any MEDIUM CRITICAL station is RED, then Group = AMBER
If greater than or equal to 50% of MEDIUM CRITICAL stations are AMBER (and no less than 3), then Group = AMBER
If greater than or equal to 70% of LOW CRITICAL stations are RED (and no less than 2), then Group = AMBER
If less than 50% of MEDIUM CRITICAL stations are AMBER & greater than or equal to 70% of LOW CRITICAL stations are AMBER then Group = AMBER
All other conditions that do not fit into the above scenarios, the Group = GREEN.

Regards,

Shameen

Frans Visser

Haarlem - The Netherlands

VIP

Members

Forum Posts: 349

Member Since:
April 21, 2015

Offline

February 7, 2017 - 8:38 pm

Hi Shameen,

It's a lot you ask 🙂

I looked at your worksheet (thanks for that), but I'm not sure about two things:

Where do you decide if a station is High Critical? Is that your BI column?

And what do you mean by 'Group' (Your point 1: if any high critical station is red, then group=red). Is that the whole of the BJ column??

For what I see now I think you want too much different kind of conditions in one performance, but maybe we can find a workaround, depends on if we can understand what you want and where you want that colors.

I also see an error in BH20, but that's not your question, isn't it?

Shameen

New Member

Members

Forum Posts: 2

Member Since:
February 5, 2017

Offline

February 9, 2017 - 10:58 pm

Hi Frans,

Thanks for the reply. Some how I manged to get the results I am looking except the two conditions works abnormal. I am attaching here with the file once again which you might be able to help.

Regards,

Shameen