How to Use Icon Set Conditional Formatting and how to use IF formula based on different criteria In Excel 2016?

1
votes

So I want to have "Red Green and Yellow" Icon Set Conditional Formatting based on the one cell value.

Criterias' for color Scheme :

We have two columns with values - current number and operational number

The Green Color Circle (from conditional formatting Icon set) should show in separate third column if the current number is equal to or less than operational number

The Yellow Color Circle (from conditional formatting Icon Set) should show if the current number is greater than operational number but less than the operational number + buffer (which I am using is 1%)

The Red Color Circle ( from conditional formatting Icon Set) should show if the current number is greater than operational number plus the buffer percentage (1%)

I am using nested if statements to first show values as red, green and yellow and then use color scheme to color them or use icon set.

=IF((Column1+Column2)<=(Column2+Column3),"Green",IF((Column1+Column2)>=(Column2+column3),"Red"))
  1. Column 1 here is current number
  2. Column 2 is the buffer
  3. Column 3 is the operational number

The above shows me green all the time no matter value. Exceed. Also how to use icon set keeping these criterias. I know i could color the cell based on the descriptive equals to value but hard to do icon set. Any pointers, ideas appreciated.

1
excelexcel-formulaconditional-formatting
Sahil - your nested IF statements are a wreck. A good practice is to set it up so that the first IF works in both conditions before nesting. – Sam
My If statements are working fine now. I figured that logic out. Now the problem is using icon set to show green green and yellow based on the number i got in my column. (1 for Green, 0 for yellow -1 for red) But for some reason when i apply conditional formatting the excel wont show those circles. – Sahil K

1 Answers

0
votes

First, in order to get 3 results using an IF statement, here's how I would set it up:

IF([@[Current Val]]<=[@[Operational Val]],-1,IF(AND([@[Current Val]]>=[@[Operational Val]],[@[Current Val]]<=PRODUCT([@[Operational Val]],1.1)),0,1))

Note the use of AND in the middle to bracket the yellow values.

So, your table looks like this: enter image description here

I would put the IF statement in the 3rd column of your table and then just format them using the Conditional Icons. enter image description here