Results 1 to 5 of 5

20120323, 15:59 #1
 Join Date
 Dec 2009
 Location
 Worcestershire
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20120323, 16:31 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,898
 Thanks
 420
 Thanked 1,585 Times in 1,434 Posts
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.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20120324, 07:30 #3
 Join Date
 Dec 2009
 Location
 Worcestershire
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks for the heads up RetiredGeek. I've played around with the ROUND function and got things working as I want. Many thanks.
Last edited by cavasta; 20120324 at 13:33.

20120326, 12:41 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,396
 Thanks
 164
 Thanked 634 Times in 602 Posts
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

20120327, 03:19 #5
 Join Date
 Dec 2009
 Location
 Worcestershire
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Zeddy  that's great! Appreciate your help. I like the little touch with the variable discount rate you've added in E1.
Cheers!