Conditional Formatting: Can't get red dot to show up

So I am trying to set up some conditional formatting using formulas the red/yellow/ green icons. The green and yellow icons seem to be capturing correctly for some reason I can't get the red icon to show up.

Conditional format.PNG

Here is the formulas I put in. I want it to be green if they exceed their goal, yellow if they reach 80% of the goal, and red for anything lower.

results.PNG

As you can see, the fourth column should be red, as they did not meet 80% of the goal, but it still shows up as yellow. I can't figure out how to include the red icon in this table. And I am already aware that there needs to be a separate rule for each line.

Any advice would be appreciated!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Looks like the rule is only applied to the first cell, since only the first cell shows the icon with the tick and the cells below show icons without the ticks.

Make sure that the cells have only one conditional format and make sure that the CF is applied to all the cells you are interested in.

Do you know that you don't have to create individual rules for each row? Select all rows, create a rule with icon sets and it will apply that ONE rule to all rows.

Your rule in row 5 uses percent, not formula in the definition of the yellow value, so it differs from the rest.

All the other rules work correctly, as in their individual definitions.

for example row 7 is yellow because =L7-L7*0.8 = 5.6 and the value 20 is not smaller than that, so yellow is applied, not red. Red will be applied if the value is less than 5.6.

What is the problem? Do you expect to see something different? Why? Can you explain your rules in words?

Appreciate the response!

In this case, when using icon sets, you do have to do an individual rule for each row (unless there's something I'm not understanding). You can't use relative references with icon sets, so it automatically references cell $L$3 and compares it to every row.

error_message.PNG

As for row 7. I can see now that my math is off, thanks for pointing that out. I should have been using 0.2 instead of 0.8 (and I made sure it said formula instead of percentage). I think that was causing my issues because I am now able to get the red icons to appear. Thank you!

If I'm misunderstanding that error message and there is an easier way to do conditional formatting with icon sets and apply it to an entire row. Could you explain that a little bit?

Thanks again!

You could use a helper formula in a separate column to the left of TPH, use a formula to return 3, 2, or 1, depending on your evaluation of the difference between TPH and Goal, then use just one rule on the helper column.

2019-01-18_13-32-03.png

The row-relative calculation is just one formula copied down.

=IF(L3>=M3,3,IF(L3>M3-M3*0.8,2,1))

The rule is just one rule based on the cell value and the type is set to Number.

Much quicker to set up than having individual icon set rules for each row.

Clear all CF rules from K3:K26
With K3:K26 selected, choose Cond Fmt: Icons
Formula for Green: >= 0.85*$L$3
Formula for yellow: >= 0.55*$L$3