Results 1 to 11 of 11

Thread: format

  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    format

    hi all,

    any idea how i can get the conditional format in the attached file using formula or other means other than manual and is there any other fancier way like adding circle. the headings are 2015 to 2019

    TIA
    dubdub
    Attached Files Attached Files
    Last edited by dubdub; 2013-08-03 at 13:37.
    TIA
    dubdub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    DubDub,

    It can be done with conditional formatting if you can quantify what thresholds define what colors. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks RetiredGreek for your reply, i my self is confused about it, how about the following
    green if cell value is <=2 or -2
    amber if cell value is <=5 or -5
    red if cell value >5 or -5

    let us see if that going to get the attachment color.
    Last edited by dubdub; 2013-08-03 at 14:42.
    TIA
    dubdub

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    DubDub,

    Ok here's a workbook with the conditional formatting applied.

    The rules (slightly changed because of %) are:

    =AND(A1<=0.02,A1>=-0.02) is Green
    =OR(A1>0.05,A1<-0.05) is Red
    =OR(A1<=0.05,A1>=-0.05) is Amber (sorry my shade is a bit off)


    Note: The order of the rules is mandatory as are the stop after selections.
    Conditional Formatting.JPG

    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    many thank for your help.
    i guess it is not possible to use the traffic light circle with these conditions.

    TIA
    dubdub
    TIA
    dubdub

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi dubdub

    Everything is possible in this forum.

    See my attached file for one way to get what you want.
    I used your values:
    green if cell value is <=2 or >=-2
    amber if cell value is <=5 or >=-5
    red if cell value >5 or >-5

    Open the file, change the numbers, and see the traffic lights change colour as appropriate.

    There are other ways this could be done without using adjacent columns as per my attached example.
    For instance, you could use the Excel camera tool to take snapshots of corresponding traffic light results, and overlay these on top of your data columns. But I thought that would be a little more complicated to explain.

    zeddy
    Attached Files Attached Files

  7. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2013-08-04)

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A direct way is to use conditional formatting with a 5 icon set and customize red (> .05 number), yellow (>.02 number), green (>=-.02, number), yellow (>=-.05, number), red

    This does not require extra columns or any other indirect calculations.

    Steve

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    zeddy (2013-08-04)

  10. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi dubdub

    ..top marks to Steve for letting us know you can adapt a 5 icon arrow set and change them to traffic lights.

    See attached for version as suggested by Steve, using:
    red > 5
    yellow > 2
    green >= -2
    yellow >= -5
    red < -5

    zeddy
    Attached Files Attached Files

  11. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thank you all,

    still not sure about the arrow conversion to traffic.

    dubdub
    TIA
    dubdub

  12. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    still not sure about the arrow conversion to traffic.
    Instead of choosing the traffic icons which allow only 3 possibilities (when you need 5) choose one of the icon sets with 5 criteria (like the 5 arrows, 5 quarters, 5 ratings) and when you edit the formatting, select on the icon and choose a different icon. You can use the red circle for the max and the lowest (1 and 5), the amber circle for the 2nd and 4th and the green circle for the 3rd item. This will make the "traffic" icons work for the 5 criteria in the manner you want.

    Steve

  13. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks once again for the help, and to Steve for his suggestion.
    TIA
    dubdub

Posting Permissions

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