Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Conditional formating - excel 10

    I want to include a colored icon within a cell that has a calculated number (average $ per receipt per day). The conditional formatting would be a formula that would be, if the daily average $ per receipt per day is greater than or less than the monthly average $ per receipt by X% then a yellow or red icon would appear depending on if it is greater than or less than 5% or 10%.

    Could anyone tell me how this could be accomplished?

    Thank you.

  2. #2
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    if I understand this correctly you don't want to change cell's appearance but want some sort of object to change color depending on cell value instead? if yes you can do this using stack chart. let me know if this is what you require and I will upload example for you.

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts

    Conditonal formatting with colors

    I was able to fill the cells conditionally with the colors you specified at the percentages you indicated but was unable to achieve the same results with icon sets. If you can use a color fill as opposed to icons then here you go. Cells A2 through A24 are conditionally formatted based on the value of cell B2.

    HTH,
    Maud
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    See attached file.
    A red dot is displayed if the daily average value in column D is more than 10% above or below the monthly average in cell [D4]
    A yellow dot is displayed if the daily avearge is more than 5% above or below the monthly average in cell [D4]
    If the daily average is within 5% of the monthly average, then no dot is displayed.

    You can adjust values and see the dots change depending on the new monthly average, and daily average values.

    zeddy
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    That is correct. (attached sample worksheet "CONDITIONAL FORMATING WITH IN A FORMULA CELL AX.xlsx)
    If the formula cells in column D (D5 thru D16)has a COLORED DOT ICON (preferable) or the cell fills in with a color if greater than or less than a
    predetermined criteria based on the percentage difference from the total (cell D4). If possible, since Cells in D10 thru D12 have no values in the "#" or "CASH $" no conditional formatting is
    required.

    The conditional formatting is to be completed only in Column D. Hopefully this is possible.

    i would appreciate your upload.

    Thank you.
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Wink

    Quote Originally Posted by MNN View Post
    That is correct. (attached sample worksheet "CONDITIONAL FORMATING WITH IN A FORMULA CELL AX.xlsx)
    If the formula cells in column D (D5 thru D16)has a COLORED DOT ICON (preferable) or the cell fills in with a color if greater than or less than a
    predetermined criteria based on the percentage difference from the total (cell D4). If possible, since Cells in D10 thru D12 have no values in the "#" or "CASH $" no conditional formatting is
    required.

    The conditional formatting is to be completed only in Column D. Hopefully this is possible.

    i would appreciate your upload.

    Thank you.
    I hope I got it right. Included are two options, one with cells filled conditionally and other using stack chart. hope it will help.
    see attached. Copy of CONDITIONAL FORMATING WITH IN A FORMULA CELL AX.xlsx

Posting Permissions

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