Symbol Possible In IF Formula?

Topic

New Reply

I want to create an IF formula that will place a symbol (either up arrow or down arrow) in a cell depending on the value in a referenced cell compared to the previous day’s value. Is this possible? How? What cell format? Robert

Reply | Quote

Replies

How about:

=IF(G6>0,”p”,”q”)

using Wingdings 3.

This will provide up and down arrows.

Reply | Quote

How about:

=IF(G6>0,”p”,”q”)

using Wingdings 3.

This will provide up and down arrows.

Thanks, Nathan. Works like a charm!
Robert

Reply | Quote

Thanks, Nathan. Works like a charm!
Robert

or try this expansion. it is something fancy.

Reply | Quote

or try this expansion. it is something fancy.

Hi Prasad,

Please would you mind telling me how you get the symbols into the formula. I’m interested to know if a symbol could be used in the same cell as text and numbers.

I would like to have a cell populated with a symbol followed by ” “&sum(A1-B1)& ” places”. Now that would be fancy.

Reply | Quote

You can copy the symbols out of the “character map” and use them within the formula. I got these straight out of the Arial Narrow font.
▲♠♣♥

Reply | Quote

You can copy the symbols out of the “character map” and use them within the formula. I got these straight out of the Arial Narrow font.
▲♠♣♥

Thanks Mike, but how do I get to the character map (in vista)?

Reply | Quote

Hi, found the character map, eventually…

Am I correct in my finding that it is NOT possible to show a character in the same cell as text and a value (concatenated) ?

Reply | Quote

As long as the symbols are part of the font set you are using, you can use the symbols as part of the formula. You cannot have two different font types as part of a formula result. The attached picture shows the symbols being used in a formula. The font is Ariel.

Reply | Quote

Wahey, Thanks Mike.

Finally, is it possible to conditionally format a cell based on whether the value contains ▲, ►, or ▼ ?

Reply | Quote

Or you can simply use (A1=▲) as your test.

Reply | Quote

Or you can simply use (A1=▲) as your test.

This is true if the ▲ is the entire result of the formula. Since VagasNath was inquiring about the concatenation of the symbol with more text, the A1=▲ would fail since the cell contains more than the ▲.

Reply | Quote

Sure, you could use: =FIND(A1,”▲”)>0 as your criteria for the conditional formatting. Or,if an IF() formula is driving the ▲, you could use the “TRUE” part of the formula. For example, if your formula is IF(A1>6,▲,””) you can use =A1>6 as the criteria for your conditional formatting formula.

Reply | Quote

OK.

Or,if an IF() formula is driving the ▲, you could use the “TRUE” part of the formula. For example, if your formula is IF(A1>6,▲,””) you can use =A1>6 as the criteria for your conditional formatting formula.

Yes, the symbol is driven by an IF() formula which involves a Vlookup to another sheet.

Am I correct in fearing that I have run out of options?

Reply | Quote

Thanks both, I’m still struggling slightly.

Ignore the 237 & 72, the +6 and +1 are as I would expect.

The formula in P16 resulting in -145 is:

=IF(VLOOKUP(R12,’Admin Players’!$C:$AS,20,0)>0,”▲”&VLOOKUP(R12,’Admin Players’!$C:$AS,20,0),IF(VLOOKUP(R12,’Admin Players’!$C:$AS,20,0)<0,"▼"&-VLOOKUP(R12,'Admin Players'!$C:$AS,20,0),VLOOKUP(R12,'Admin Players'!$C:$AS,20,0)))

This should be red, so should the -39 and -7. The red zero should not be red as it is neither 0.

Any idea’s what I am doing wrong?

Reply | Quote

I *think* I see my error. I was’nt sure what the relevance of the 6 was, and assumed that it had something to do with the symbol. I think I understand [stupidme].

Reply | Quote

For this formula:

=IF(VLOOKUP(R12,’Admin Players’!$C:$AS,20,0)>0,”▲”&VLOOKUP(R12,’Admin Players’!$C:$AS,20,0),IF(VLOOKUP(R12,’Admin Players’!$C:$AS,20,0)0

This portion should be used for the Red conditional formatting
IF(VLOOKUP(R12,’Admin Players’!$C:$AS,20,0)0
where A1 is the current cell

Reply | Quote

Hi Mike,

Thanks for your help here, it’s really appreciated.

The easy one:

=FIND(A1,”▲”)>0
where A1 is the current cell

does not produce any result for me at all.

The more difficult one does not work as CF cannot reference other worksheets, only the current worksheet.

Am I missing something?

Reply | Quote

I must apologize. I had the formula backwards.

The formula should be:
=FIND(“▲”,A1)>0 not =FIND(A1,”▲”)>0

Reply | Quote

Thanks Mike!

Reply | Quote

Thanks Mike!

would you mind posting your file to share this
I am still lost as to how to have the symbol in the formula

thanks

Reply | Quote

An alternative suggestion…

If the formatting depends only on whether the number is positive, zero or negative, you can do it directly in the cell format. This is simpler than using conditional formatting, but very much more limited in what it can do.

I’ve also used the Arial font from the character map to produce the formula “[Green]▲0;[Red]▼0;[Blue]►0”. This formats positive numbers in green with a ▲, negative numbers in red with a ▼, and zeros in blue with a ►. You could also add another section at the end (separated by a semicolon) for blanks.

(Looks like I was beaten to it by Rory!)

Reply | Quote

I’ve attached a sample file. I used the RAND() function to display the values except for the MNO stock.

Reply | Quote

FWIW, you can simplify a lot and use a custom number format rather than conditional formatting and IF formulas. See attached (cell C1 has the custom format depending on whether it’s positive, negative or 0)

Reply | Quote

FWIW, you can simplify a lot and use a custom number format rather than conditional formatting and IF formulas. See attached (cell C1 has the custom format depending on whether it’s positive, negative or 0)

Thanks, but where and how do I get the symbol into the formula?

Reply | Quote

Thanks, but where and how do I get the symbol into the formula?

I did it by going to Insert | Symbol, and selecting my required symbols out onto the worksheet. Once I had the symbols in the ws, I copied them into my formula’s.

I’m starting to wish that I had not taken this route now though, I’ve created several problems that I now have to find workarounds for.

That’s progress grrrrrrr..

Reply | Quote

I did it by going to Insert | Symbol, and selecting my required symbols out onto the worksheet. Once I had the symbols in the ws, I copied them into my formula’s.

I’m starting to wish that I had not taken this route now though, I’ve created several problems that I now have to find workarounds for.

That’s progress grrrrrrr..

That’s what I am doing and find it really cumbersome
I am yet to try the barron’s method

Reply | Quote

Which is basically what I did…

Reply | Quote

I like this (much simpler) alternative very much.

Cheers

Reply | Quote

FYI, your VLOOKUP formula is also very inefficient since it refers to 43 entire columns of data when you are only interested in 2 of them:
VLOOKUP(R12,’Admin Players’!$C:$AS,20,0)
could be:
=index(‘Admin Players’!$W:$W,match(R12,’Admin Players’!$C:$C,0))
or preferably limit the actual ranges rather than referring to entire columns.

Reply | Quote

FYI, your VLOOKUP formula is also very inefficient since it refers to 43 entire columns of data when you are only interested in 2 of them:
VLOOKUP(R12,’Admin Players’!$C:$AS,20,0)
could be:
=index(‘Admin Players’!$W:$W,match(R12,’Admin Players’!$C:$C,0))
or preferably limit the actual ranges rather than referring to entire columns.

Thanks for your input Rory, i’ll take a look at that when I overcome my other outstanding issue’s.

Reply | Quote

From the Character Map within Windows as I said in this post above [post=”791673″]Post 791673[/post]

To find it in XP
Start
Programs
Accessories
System Tools

Reply | Quote

Pl have a look on attachment. I am struggling with formating cell to % in 2 decimal places along with up/down arrow. Any suggestion.

Reply | Quote

Pl have a look on attachment. I am struggling with formating cell to % in 2 decimal places along with up/down arrow. Any suggestion.

Seem like it can’t be formatted as percentage due to adding in the symbol as its have become text
How’s about this if you need to include percentage symbol in the result?

Reply | Quote

It would be much easier to put the symbols into the number format too, as in the attached. The percent format won’t work as you have it because the data in the cell is not a number.

Reply | Quote

It would be much easier to put the symbols into the number format too, as in the attached. The percent format won’t work as you have it because the data in the cell is not a number.

Thanks Rory. I was trying to approach the same way but no such custom list is found. Is there a way to create custome lists and then make them available in formating box?

Reply | Quote

Thanks Rory. I was trying to approach the same way but no such custom list is found. Is there a way to create custome lists and then make them available in formating box?

Got it, I think. One can create a list in Format –> style, but how????

Reply | Quote

It would be much easier to put the symbols into the number format too, as in the attached. The percent format won’t work as you have it because the data in the cell is not a number.

I dug myself a few holes following this thread, making improvements to a wb and later realising I had created restrictions.

I now see however that I can use symbols and text in a cell, whilst keeping the cell numeric for calculations, AND without conditional formatting.

It’s been a bumpy ride, but I thank you very much!! I’ve learned lots here and it’s made my wb much slicker!

Reply | Quote

Once you have selected Custom in the left hand list, you can just type your own format codes. They should then automatically be added to the available formats for the workbook.

Reply | Quote

Once you have selected Custom in the left hand list, you can just type your own format codes. They should then automatically be added to the available formats for the workbook.

Are they workbook specific Or stored as excel default?

Reply | Quote

They are workbook specific. You would need to add them to book.xlt (if you have one) to have them available by default for new workbooks.

Reply | Quote

They are workbook specific. You would need to add them to book.xlt (if you have one) to have them available by default for new workbooks.

Thank you very much all for valuable tutorial.

Reply | Quote