Conditional Formatting with an Icon Set

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
Is there anyway to write a different formula for the 4 different arrow icon sets so that they put an arrow up or down based on a different formula each value box?

I am trying to say
Highlight up arrow if cell I28-cell I24>=5
Highlight up/side arrow if cell I28-cell I24>=0, <5
Highlight down/side arrow if cell I28-cell I24<0, >-5
Highlight down arrow if cell I28-cell I24<=-5
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,282
Office Version
  1. 365
Platform
  1. Windows
Try this.

1. In the cell where you want the icon, put the formula
=I28-I24

2. If you do not want to see this formula result, make the font colour the same as the background colour.

3. Apply the basic 4-arrow icon set Conditional Formatting to the cell

4. Now: Conditional Formatting|Manage Rules...|select the icon set rule|Edit Rule...

5. For the up arrow, change the 'Type' to number and enter 5 in the value box
For the up-right arrow, change the 'Type' to number and enter 0 in the value box
For the down-right arrow, change the 'Type' to number and enter -5 in the value box

6. OK|Apply|Close
 

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
the only problem with this is, I don't want to actually subtract I28-I24. I want to leave the actual values of the cells still intact.

I am just trying to find out how far away cell I28 is from I24. if I28 is more than 5 greater than I24, then I give it the up arrow, etc, etc.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,282
Office Version
  1. 365
Platform
  1. Windows
I want to leave the actual values of the cells still intact.
1. What cells are you talking about? Y

2. What is currently in those cells?

3. What cell(s) do you want the icons to appear in?
 

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333

ADVERTISEMENT

right now I am talking about cells J23 through J28.

The conditional formatting will be applied comparing cells J24, 25, 26, 27, and 28 to cell J23. Right now inside those cells is :

3 in J23
9 in J24
17 in J25
5 in J26
9 in J27
7 in J28

what I want to be able to do is keep the value in each cell and also have the arrow to the left of the value. the arrow's direction will tell me how far above or below the value of that cell is compared to the value in cell J23.
 

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
I came up with this, but it doesn't seem to be working

for cell J25
UP ARROW when value is > ="4+$J$23"
UP-R ARROW when value is <= FORMULA (i'm guessing this formula?"4+$J$23") and >= ="$J$23"
DWN-R ARROW when < FORMULA ("$J$23") and >= =-5+$J$23
DWN ARROW when < FORMULA (=-5+$J$23)

do i need this many "=" and ""?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,282
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That basic construction seems to be working for me - assuming the expected results are what I have shown below. I cannot easily show the icon sets so I have noted in the adjacent cell the icon showing.

Are these the results you expect for this sample data?

Excel Workbook
JK
233
249up
2517up
265up right
279up
287up right
Icon Set adjustment



The rules I have are:
up arrow .............. when value is ............ >= ... =$J$23+5 ... Formula
up rightarrow ........ when < Formula and ... >= ... =$J$23 ....... Formula
down right arrow ... when < Formula and ... >= ... $J$23-5 ...... Formula
down arrow .......... when < Formula
 

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
YES!! That worked the way I want it to... seems so easy now.

What would I put for a formula to get it to say if J25 is 10% more than J23 for the up arrow??
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,282
Office Version
  1. 365
Platform
  1. Windows
yes!! That worked the way i want it to... Seems so easy now.

What would i put for a formula to get it to say if j25 is 10% more than j23 for the up arrow??
up arrow ... when value is ... >= ... =$J$23*1.1 ... Formula
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,748
Messages
5,833,470
Members
430,211
Latest member
Vandermeer

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