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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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 ""?
 
Upvote 0
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
 
Upvote 0
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??
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,331
Messages
6,135,938
Members
449,973
Latest member
jarzack

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
Back
Top