Conditional formatting color fill based on nested if statement

Simpler than that

In the CF rules, choose "Format only cells that contain" then set cell value Equal to, then choose C2, E2 or G2 for each rule/format.

I've attached an updated sheet to show what I mean.

Just a thought though...

What happens if you have two stores with the same price? You would need to change the order of the CF rules and tick the "stop if true" option.

Hi Tracey,

For your "equal price" you would need another rule, this time with a formula:

=COUNTIF($C2:$C2,$I2)>1

This needs to be the first rule with a "stop if true" check box so that it doesn't try to apply any other rules if you have more than one match. I've created this in the attached updated file so you can see it in action (I used pale grey in the format just so you could see it in action).

I'm not sure what you want to see in your "price per" column. Do you want the lowest one to be highlighted? If that's the case, would it be better to have an additional "Best price per" column and use a similar rule to your Best Price?

Shirley

Hi Tracey,

I think I see what you mean. All the Price Per calculations on your sample were the same so I think that was what confused me

On the attached example I've added a new Alt Best Price column which still shows the best price as before, but the conditional formatting is based on the Price Per figure. I still have the COUNTIF as the first rule in case you have 2 with the same value, although I've change it slightly to take into account the minimum Price Per unit.

The other colours use a formula for the CF instead of the original method. For Tesco for example, the formula is:

=MIN($D2,$F2,$H2)=$H2

so checking the minimum price per against each store.

I changed the Aldi pears Price per formula from C4/8 to C4/24 just to demonstrate the difference. It's the one highlighted in red.

Just as an aside, would it be better to have your divisor as a column alongside each store rather than embedding these in the formulas? I'm just thinking it would be easier to see the differences and also make it easier for you to change them. I've added a sheet called Alternative to show what I mean

You can just delete those columns Tracey. I just used them so I could easily see the formulas you'd used in your original sheet - they don't actually do anything

I'm guessing the =FORMULATEXT function wasn't available in Excel 2007 - I honestly can't remember when it was introduced (I'm using 2016) so must have been a later addition.

Could you update your profile to indicate you're using 2007? A lot of people don't have access to Excel 2003 anymore (me included!) so sometimes you won't get answers to your posts if people think that's what you're using

Originally Posted by shirleyxls

I'm guessing the =FORMULATEXT function wasn't available in Excel 2007

The FORMULATEXT function was introduced in Excel 2013.

Hi Tracey,

No problem. I've added another simple rule "Format only cells that contain" and set that to no fill if the cell value is equal to 0. I've set this as "stop if true" so it won't try to apply any of the other formats based on minimum values. I've put that on both your original Sheet 1 and the Alternative sheet.

Updated version attached.

Shirley

Tony - thanks for the update on when the FORMULATEXT function was introduced. I find it really useful when I'm analyzing/auditing spreadsheets and it's invaluable for documentation.

Or in the words of Homer Simpson "Doh"

My pleasure to help out... just shout if you're stuck with anything

Shirley