conditional formatting - Icon set

ashani

Board Regular
Joined
Mar 14, 2020
Messages
204
Office Version
  1. 365
Platform
  1. Windows
Hi
i'm using the conditional formatting Icon set option to to have arrows with the help of the formula below, which is working fine. However, when both cell values are the same than I want Amber dot but it's coming up as Red dot. Is there anyway I can fix it ?

=IF(OR(C5>B5,C5<B5),1,0)

I'm looking for the following :

C5 higher than B5 = show green dot
c5 lower than b5 = show red dot
c5 equals to b5 = amber dot

thank you
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,681
Office Version
  1. 365
Platform
  1. MacOS
does this work for you
=IF(C5>B5,2,IF(C5<B5,0,1))

XL2BB doe not seem to show icons, so added an image

Book7
BCDE
4
52010
61202
720201
8
Sheet1
Cell Formulas
RangeFormula
D5:D7D5=IF(C5>B5,2,IF(C5<B5,0,1))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:D23Other TypeIcon setNO
 

Attachments

  • Screenshot 2021-03-31 at 12.17.31.png
    Screenshot 2021-03-31 at 12.17.31.png
    19.2 KB · Views: 1
Last edited:

ashani

Board Regular
Joined
Mar 14, 2020
Messages
204
Office Version
  1. 365
Platform
  1. Windows
hi Etaf

thank you for the quick response.

It works perfectly - the only thing is if B5 value is zero 0 and C5 value is zero 0 = is it possible to have red dot instead of amber ?

Thank you once again.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,681
Office Version
  1. 365
Platform
  1. MacOS
i hsve added an OR(AND( to also test if both C5 and B5 = 0
Also added if C5 and B5 is blank
BUT what happens if Just , C5 or B5 i blank - thats seen as zero

=IF(AND(C5="",B5=""),"",IF(C5>B5,2,IF(OR(AND(C5=0,B5=0),C5<B5),0,1)))
 

Attachments

  • Screenshot 2021-03-31 at 12.46.09.png
    Screenshot 2021-03-31 at 12.46.09.png
    37.2 KB · Views: 1

ashani

Board Regular
Joined
Mar 14, 2020
Messages
204
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

yeah that's correct if both cell is blank than it will be 0
thank you
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,681
Office Version
  1. 365
Platform
  1. MacOS
you are welcome

BUT if one cell is blank, that will also measure as zero
 

Attachments

  • Screenshot 2021-03-31 at 13.10.47.png
    Screenshot 2021-03-31 at 13.10.47.png
    43.6 KB · Views: 1

ashani

Board Regular
Joined
Mar 14, 2020
Messages
204
Office Version
  1. 365
Platform
  1. Windows
amazeing - thank you so much
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,681
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,545
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top