Results 1 to 4 of 4

Conditional formatting with icons using a formula

This is a discussion on Conditional formatting with icons using a formula within the Excel Questions forums, part of the Question Forums category; I want to use the 3 flag icon set as a conditonal format based on a formalus relating to a ...

  1. #1
    Board Regular
    Join Date
    May 2008
    Posts
    56

    Default Conditional formatting with icons using a formula

    I want to use the 3 flag icon set as a conditonal format based on a formalus relating to a value in another cell in my spreadsheet.

    I am struggling with the formula.

    I want a green flag in cell H1 if the value in cell H1 is in the range of .9 to1.1 times the value in cell A1.

    If the value in H1 is zero I want a yellow flag in H1

    If the value in H1 is neither green of yellow I want a red flag in H1


  2. #2
    New Member
    Join Date
    Apr 2009
    Posts
    12

    Default Re: Conditional formatting with icons using a formula

    According to the help file you can only use Icon Sets with rules that evaluate to a number. If I understand your question you need to apply a rule that evaluates to True/False:
    =AND(H1>0.9*$A$1,H1<1.1*$A$1)

    You can use this rule (and similar for the other conditions) and format the cell/font in red/yellow/green but cannot use the flags.

    Matt
    Useful as a short piece of string

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,584

    Default Re: Conditional formatting with icons using a formula

    Bob

    My understanding of the icon sets is also that you cannot use them directly as you want. However, if you really want the flags and not just coloured cells as suggested by Matt, you could investigate a work-around something like this.

    1. Use the fomula shown in G1 and copy it down.

    2. Apply the flag icon set to the column G cells.

    3. Select the column G cells then Conditional Formatting| Manage Rules...|select the rule|Edit Rule...|Show icon only|OK|Apply|OK

    I can't show the flags with my screen shot so I have manually coloured the column G cells with the colour of the flag that appears after following the above steps.

    Flags

     ABCDEFGH
    11      1.05
    22      3
    33      0
    44      3.98
    55      3

    Spreadsheet Formulas
    CellFormula
    G1=IF(H1=0,0,IF(ABS(1-H1/A1)<=0.1,1,-1))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  4. #4
    New Member
    Join Date
    Apr 2009
    Posts
    12

    Default Re: Conditional formatting with icons using a formula

    Nice workaround Peter.

    Bob, notice that the difference in referencing column A depending on whether you are referring to a fixed value in A1 ($A$1) or just the value in columnA for each row (A1).

    Matt

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com