# Thread: Formula and conditional formatting problem

1. ## Formula and conditional formatting problem

Can someone tell me where I’m going wrong with a formula and my conditional formatting please? See the attached file to see how I’ve got every thing set up (explained below). Sorry for the length of this post...

I have a worksheet where I’m comparing the prices of items in two different shops – shop A and shop B. Shop B offers a 15% discount to staff members but shop A doesn’t. I’ve set the sheet up to show the item on sale, the price in shop A, and the price in shop B before the discount, after the discount, and the amount of the discount. I am in the process of setting up conditional formatting for column B to show which shop is the cheapest, most expensive, or where there is no difference between the two shop’s prices. In the case of shop B, cheapest means the price after the discount. The columns are laid out thus:

Column A: Item
Column B: Shop A’s prices
Column C: Shop B’s prices before discount
Column D: Shop B’s prices after discount
Column E: amount of shop B’s discount
Column F: helper column for conditional formatting.

What I want to do is set up the conditional formatting in column B so that:
when shop A is cheaper, the price turns green
when shop A is dearer, the price turns red
when the price in A and shop B is the same, the price turns orange

If shop B doesn’t stock an item and shop A does, I will enter “n/a” in column C
Column D has the formula =IF(ISTEXT(C3),"",IF(C3>0,C3-(C3*15/100),"")).
The “ISTEXT(C3)” refers to “n/a” in column c (if entered).
“(C3*15/100)” refers to the 15% staff discount on shop B prices.

Column E has the formula =IF(ISTEXT(C3),"",IF(C3>0,(C3*15/100),"")). This calculates the amount of staff discount available on shop B’s items.

Column F is a helper column for the conditional formatting in column B and contains the following formula:

=IF(B3>D3,"R",IF(B3<D3,"G",IF(B3=D3,"O",IF(C3="n/a","G",IF(C3="","G",""))))).

Which means, if Shop A is more expensive than shop B (after discount), the corresponding cell in column F will show “R”. If shop A is cheaper, a “G” will show, if there is no difference between the two shop’s prices, “O” will show, and if “n/a” is entered in column C, “G” will show. If there is no data in column C, column F will be blank. (The letters “R”, “G”, and “O” simply refer to red, green and orange, and act as a simple mnemonic for me when I’m working on the conditional formatting.)

I’ve got three conditional formatting conditions set up for column B:

Condition 1: =IF(F3="R",TRUE,FALSE) turns font red if true.
Condition 2: =IF(F3="G", TRUE, FALSE) turns the font green if true.
Condition 3. =IF(F3="O",TRUE,FALSE) turns the font orange if true.

My problem is this: I cannot get the formula in column F to show “O” when the prices of the two shops are the same. And because I’ve set up the conditional formatting in column B with reference to the values in column F, I cannot get shop A’s prices to turn orange when they are the same price as shop B. Everything else works as I want it to.

Can someone with a larger brain than mine help me out please?

2. CAVASTA,

One thing you should definately do it to include your calculations where you are dividing and / or multiplying by fractions within Round() functions otherwise an equal test is going to give you the problem of very small differences, i.e. 5.00000001 <> 5.000000000001 so an equality test won't work (even if they both show as 5.00 due to formatting of the cells) if you round them both to 2 decimal places 5.00 = 5.00 no problemo! You could even do this with your comparisons but it shouldn't be necessary if you do it in your calculated fields used in the comparisons.

3. Thanks for the heads up RetiredGeek. I've played around with the ROUND function and got things working as I want. Many thanks.

4. Hi Cavasta

I have simplified your conditional formats from:
Condition 1: =IF(F3="R",TRUE,FALSE) turns font red if true.
Condition 2: =IF(F3="G", TRUE, FALSE) turns the font green if true.
Condition 3. =IF(F3="O",TRUE,FALSE) turns the font orange if true.
to..
Condition 1: =F3="R" turns font red if true.
Condition 2: =F3="G" turns the font green if true.
Condition 3. =F3="O" turns the font orange if true.
..and I simplified some of your formulas.
Also, just for fun, I let you specify the discount rate in cell [E1] so you can see the changes in your formats when you put say, 10%, 15%, 20% etc for the rate.

see attached book

zeddy

5. Thanks Zeddy - that's great! Appreciate your help. I like the little touch with the variable discount rate you've added in E1.

Cheers!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•