Conditional formating – excel 10

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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. 33279-Copy-of-CONDITIONAL-FORMATING-WITH-IN-A-FORMULA-CELL-AX

Reply | Quote

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

Reply | Quote

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

Reply | Quote