Results 1 to 5 of 5
  1. #1
    New Lounger
    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 Im going wrong with a formula and my conditional formatting please? See the attached file to see how Ive got every thing set up (explained below). Sorry for the length of this post...

    I have a worksheet where Im 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 doesnt. Ive 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 shops 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 As prices
    Column C: Shop Bs prices before discount
    Column D: Shop Bs prices after discount
    Column E: amount of shop Bs 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 doesnt 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 Bs 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 shops 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 Im working on the conditional formatting.)

    Ive 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 Ive set up the conditional formatting in column B with reference to the values in column F, I cannot get shop As 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?
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 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

  3. #3
    New Lounger
    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; 2012-03-24 at 14:33.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 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
    Attached Files Attached Files

  5. #5
    New Lounger
    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!

Posting Permissions

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