• 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.

Symbols in formulas

D

DashboardNovice

Member
http://chandoo.org/wp/2008/09/03/excel-kpi-dashboard-percentile-3/

On the website above, I downloaded the percentile dashboard. If I select KPI 3 and check the formula in cell H7 and press F2 to enter Edit Mode I see a drop down of a bunch of symbols.

1. Why do so many symbols drop down, even though there are only two possibilities (up arrow and down arrow)?

2. In the formula in cell H7, what does "È" and "Ç" correspond to?

3. Look at the formula in cell I8. How do you add the triangle symbols there?

When asking multiple questions like I have (even through each question is itemized and not simply lumped together like most people would do), instead of giving me 3 separate answers, i.e. one for each question, most people will give only one answer and that one answer usually pertains to the first or last question. This won't help me.

Instead, please answer ALL three questions. To help with this, consider providing your answers in itemized form, i.e. 1, 2 and 3. I will really appreciate this.

Thank you in advance.
 
1. No idea what you asking here

2. The formula in H7 is: =IF(mySortCriteria =G6,CHOOSE(Calculation!$E$8,"È","Ç"),"")
But you will notice that the cell's font is WingDings3
The "È" & "Ç" characters are displayed in Wingdings 3 as the Down and Up Arrows depending on which result the formula returns.

3. What do you want to add to I8 ? You may be better to add it to J8 instead
 
Hi ,

The answer to 1 is as follows :

H7 contains a formula , which , as you can see in the formula bar , is :

=IF(mySortCriteria =I6,CHOOSE(Calculation!$E$8,"È","Ç"),"")

If you want to know what this does , it puts one of 3 values in the cell viz. È , Ç or a blank.

However , what is needed for the cell to display is either the UP arrow or the DOWN arrow. So how do the above characters end up displaying the UP or DOWN arrows ? The answer lies in the way the cell H7 has been formatted ; the cell font is WingDings 3. This font displays the DOWN arrow if the cell contains the character È , and the UP arrow if the cell contains the character Ç.

The problem is that when you press the F2 key and go into EDIT mode , because the cell font is WingDings 3 , all of the individual characters of the above formula , are individually converted , character by character to their WingDings 3 equivalents ! Which is why you see a lot of strange characters.

Narayan
 
Last edited:
Hi, DashboardNovice!

I was just answering almost the same as Hui.

1) Please elaborate.

2) "Ç" & "È" are up & down of Windings font.

3) This is the formula in I8 cell:
=SI(mySortCriteria=I$6;"";
SI(I8>PERCENTIL(Calculation!$M$11:$M$110;Data!$E$5);"◄+";
SI(I8<PERCENTIL(Calculation!$M$11:$M$110;Data!$E$6);"◄-";""))) -----> in english:
=IF(mySortCriteria=I$6,"",
IF(I8>PERCENTILE(Calculation!$M$11:$M$110,Data!$E$5),"◄+",
IF(I8<PERCENTILE(Calculation!$M$11:$M$110,Data!$E$6),"◄-","")))

If the question is how to get the triangle symbol, then open Character Map (Start, All Programs, Accessories, System Tools), page down until almost top bottom and you'll find the 4 triangles up, left, down and right (▲►▼◄) corresponding to U+25B2, U+25BA, U+25BC & U+25C4 Unicode codes.

Does it help?

Regards!
 
SirJB7 said:
Hi, DashboardNovice!

I was just answering almost the same as Hui.

1) Please elaborate.

2) "Ç" & "È" are up & down of Windings font.

3) This is the formula in I8 cell:
=SI(mySortCriteria=I$6;"";
SI(I8>PERCENTIL(Calculation!$M$11:$M$110;Data!$E$5);"◄+";
SI(I8<PERCENTIL(Calculation!$M$11:$M$110;Data!$E$6);"◄-";""))) -----> in english:
=IF(mySortCriteria=I$6,"",
IF(I8>PERCENTILE(Calculation!$M$11:$M$110,Data!$E$5),"◄+",
IF(I8<PERCENTILE(Calculation!$M$11:$M$110,Data!$E$6),"◄-","")))

If the question is how to get the triangle symbol, then open Character Map (Start, All Programs, Accessories, System Tools), page down until almost top bottom and you'll find the 4 triangles up, left, down and right (▲►▼◄) corresponding to U+25B2, U+25BA, U+25BC & U+25C4 Unicode codes.

Does it help?

Regards!
Click to expand...


Thank you. I have never used the Character map before. But how to you use U+25C4? I tried holding Alt then when I press U I get a tone as if to indicate that I can't do that. Is this like using a keyboard shortcut to enter a symbol?
 
Hi ,

Within Excel , click on Insert , Symbol , and when you select WingDings 3 , from the Font drop-down , you can choose what ever character you want ; there are any number of UP and DOWN arrows in different styles ; one of them is ASCII code 199 decimal and the other is ASCII code 200 decimal.

Narayan
 
Hi,

To enter directly the required symbol select WingDings3 Font from Home tab. Press ALT+number combination (if you already know the code) from the number keypad and not present on top of keyboard. You will get it.

Regards,
 
Hi, DashboardNovice!

There're 3 methods, the 2 first are so cumbersome that they hardly work, the last one is using the Character Map, selecting and copying (it always work).
Check this from Microsoft, I can't make it work:
https://office.microsoft.com/en-us/...based-symbols-and-characters-HA010167539.aspx

Regarding this:
Somendra Misra said:
Press ALT+number combination (if you already know the code) from the number keypad and not present on top of keyboard.
Click to expand...
not all Unicode characters have an equivalent for being entered directly with Alt: Euro char is Unicode U-20AC and A.t-0128 but none of the 4 black arrow points have one.

Regards!
 
SirJB7 said:
Hi, DashboardNovice!

There're 3 methods, the 2 first are so cumbersome that they hardly work, the last one is using the Character Map, selecting and copying (it always work).
Check this from Microsoft, I can't make it work:
https://office.microsoft.com/en-us/...based-symbols-and-characters-HA010167539.aspx

Regarding this:

not all Unicode characters have an equivalent for being entered directly with Alt: Euro char is Unicode U-20AC and A.t-0128 but none of the 4 black arrow points have one.

Regards!
Click to expand...

Thank you to everybody who replied. All of my questions have been answered. You have been very helpful.
 
Hi, DahsboardNovice!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top