Conditional Formatting with an Icon Set

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
330
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
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
330
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
45,918
Office Version
365
Platform
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
330

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
330
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
45,918
Office Version
365
Platform
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
330
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
45,918
Office Version
365
Platform
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,606
Messages
5,512,392
Members
408,889
Latest member
2vbr

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top