• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If statement that includes Trending arrows

E

ELL

New Member
I have three cells, E4, E5, and E6. I cannot get an if statement that works to show the following:
If E4 equals F4, then circle,
If E4 is greater than F4, up arrow
If E4 is less than F4, down arrow

I would like the arrows to have color, like Up is green and down is red. But, I cannot get it to work.
I put the Circle, Up Arrow, and Down Arrow in cells, and tried to make the fomula refer to those cells, but I cannot get it to work.

Can anyone advise, it seems to me if would be so simple.
Also, I tried conditional formatting/rules and didn't have luck.
I'm sure I'm doing something not quite right.

Thanks for help.
 
Hi, ELL!

You have 2 choices.

a) Use a font that handles characters like those, and place this formula:
=SI(E4=F4;"¡";SI(E4>F4;"á";"â")) -----> in english: =IF(E4=F4,"¡",IF(E4>F4,"á","â"))
Font Windings, characters extracted using the Character Map program that comes with Windows.

b) Use a conditional format condition, Icon Sets, More Rules, Apply Format to All Cells Upon its Values (1st option). Format Style as Icon Sets, Icon Style as it is, and in the Icon for each option select your own icon for each one. Use this formula in those cells:
=SI(E4=F4;0;SI(E4>F4;1;-1)) -----> in english: =IF(E4=F4,0,IF(E4>F4,1,-1))

Just advise if any issue.

Regards!
 
SirJB7 said:
Hi, ELL!

You have 2 choices.

a) Use a font that handles characters like those, and place this formula:
=SI(E4=F4;"¡";SI(E4>F4;"á";"â")) -----> in english: =IF(E4=F4,"¡",IF(E4>F4,"á","â"))
Font Windings, characters extracted using the Character Map program that comes with Windows.

b) Use a conditional format condition, Icon Sets, More Rules, Apply Format to All Cells Upon its Values (1st option). Format Style as Icon Sets, Icon Style as it is, and in the Icon for each option select your own icon for each one. Use this formula in those cells:
=SI(E4=F4;0;SI(E4>F4;1;-1)) -----> in english: =IF(E4=F4,0,IF(E4>F4,1,-1))

Just advise if any issue.

Regards!
Click to expand...


I have tried, but I do not get arrows. I used Wingdings. I've attached the file. I'm not sure what is hanging things up.
 

Attachments

Hi, ELL!
You have a circular reference at cell D21. It uses a formula that references itself. Could you check and correct it since I don't know which values are you trying to compare?
Regards!
 
SirJB7 said:
Hi, ELL!

You have 2 choices.

a) Use a font that handles characters like those, and place this formula:
=SI(E4=F4;"¡";SI(E4>F4;"á";"â")) -----> in english: =IF(E4=F4,"¡",IF(E4>F4,"á","â"))
Font Windings, characters extracted using the Character Map program that comes with Windows.

b) Use a conditional format condition, Icon Sets, More Rules, Apply Format to All Cells Upon its Values (1st option). Format Style as Icon Sets, Icon Style as it is, and in the Icon for each option select your own icon for each one. Use this formula in those cells:
=SI(E4=F4;0;SI(E4>F4;1;-1)) -----> in english: =IF(E4=F4,0,IF(E4>F4,1,-1))

Just advise if any issue.

Regards!
Click to expand...
I get an error message stating 'You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets.

Hmmm, not sure what I am doing wrong.
The sheet I sent, I just want to show when M1 is equal to, greater than, or less than M, and when M2 is equal to, greater than, or less than M1, and so on.
 
SirJB7 said:
Hi, ELL!
You have a circular reference at cell D21. It uses a formula that references itself. Could you check and correct it since I don't know which values are you trying to compare?
Regards!
Click to expand...

I am trying to get arrow to show the following
M1=m, then circle. M1 greater than m, then up arrow. M1 less than M, then down arrow.
I need this trend to repeat to compare trend M2 w/M1, and then again M3 w/M2. Are things improve, same, ...

I get error message when I try to apply conditional formats, etc.
 

Attachments

Hi, ELL!
Which cell references (and values) in your actual uploaded file correspond to those of your 1st post referenced as E4, E5, E6 and F4?
Regards!
 
SirJB7 said:
Hi, ELL!
Which cell references (and values) in your actual uploaded file correspond to those of your 1st post referenced as E4, E5, E6 and F4?
Regards!
Click to expand...


Well, I've grown my worksheet
For M1 - (it compares to M)
E21=F21, then circle
E21 is less than F21, then down arrow
E21 is greater than F21, then up arrow

For M2 (it compares to M1)
D21=E21, then circle
D21 is less than E21, then down arrow
D21 is greater than E21, then up arrow

For M3 (it compares to M2)
C21=D21, then circle
C21 is less than D21, then down arrow
C21 is greater than D21, then up arrow
 
Hi, ELL!
Row 21 doesn't have any values.
Regards!

EDITED

PS: Let us do in the hard but effective way. Would you please update your file and upload it again indicating for each cell for which you want to place symbols, which an X, Y, Z (circle, up, down)? I'm unable to understand your model.
 
Hi, ELL!
Glad you solved it, but if you have any issue to implement it, come back here and tell us.
Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
SirJB7 said:
Hi, ELL!
Give a look at the uploaded file.
Regards!
Click to expand...


If I want to reverse the arrows, do I just
SirJB7 said:
Hi, ELL!
Glad you solved it, but if you have any issue to implement it, come back here and tell us.
Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
Click to expand...

Just curious, can I add color to the arrows, so they are red, green, and the circle is blue?
 
Hi, ELL!

Check the uploaded file. It's the same as previous but with:
a) C21:K21, font of fixed colors, red & green, independent of cell values
b) M21: P21, CF (conditional format), 3 conditions (zero yellow, positive green, negative red)
c) R5:U5 & R10:U10, CF (conditional format), icons set (zero yellow circle, positive green arrow, negative red arrow)

Three methods, three different implementations. You choose.

Note that formulas for c) should be changed from the Windings characters to 0, 1 and -1.

Regards!
 

Attachments

SirJB7 said:
Hi, ELL!

Check the uploaded file. It's the same as previous but with:
a) C21:K21, font of fixed colors, red & green, independent of cell values
b) M21: P21, CF (conditional format), 3 conditions (zero yellow, positive green, negative red)
c) R5:U5 & R10:U10, CF (conditional format), icons set (zero yellow circle, positive green arrow, negative red arrow)

Three methods, three different implementations. You choose.

Note that formulas for c) should be changed from the Windings characters to 0, 1 and -1.

Regards!
Click to expand...


You are the master :-) Thank you. I used b option and I love it, works perfectly.
 
You must log in or register to reply here.
Back
Top