Formulas for using the symbols

Sean Cox

Hello - I love the new symbol options for the columns. 

 

I'd like to use use the up / down arrows to help me track some $$ to budget info.

 

I have been trying to figure out which formula to use via the templates and I can't sort out how to do it. 

 

I know I can use conditional formatting to accomplish something similar, but I'd like to have arrows :-)

 

Sean

Travis

Hi Sean! I would be happy to help you with your formula if you wanted to give me a little more information about what you are looking for. Could you take a minute to describe exactly how you want the up/down arrows to work in relation to your budget? Thanks! 

Sean Cox

Sure thing. I have a column that is calculating the difference between two numbers. If number 2 is less than number 1 the result is negative:

 

I want the "trend" column (which has the up, down, no change arrows) to change if the number is negative or positive or has no change.

 

Is that possible?

Jason

Try this:

=IF(Diff1 > 0, "Up", IF(Diff1 < 0, "Down", "Unchanged"))

 

Where "Diff" is the column containing the difference you are evaluating. 

 

Every symbol has an associated string (text value) that can be used in a formula. The symbol drop down menu displays the text value next to the symbol. Just remember that strings must be enclosed in quotes when used in a formula. 

Kit Unger

Thanks, Jason! That formula worked perfectly.

KEL.RAY

I'm trying this and it's writing out the text "up" instead of putting in the symbol.  I tried checking the "restrict to symbols only" and that didn't work either, any ideas?

Also, is it possible to get the if formula to do the calculation itself instead of needing a separate column subtracting A from B? 

 

kjthacker

I have a use case where I want to use a pie chart to display how many issues are open, closed, and stalled. I am using RYG balls to display status. A pie chart will only use numeric values. I'd like to craft a formula that will assign all RYG balls a value and SUM the number of each color (e.g. 3 green status, 2 yellow, 4 red) so the pie chart accurately reflects the number of issues that are being tracked. Can anyone help me get started with that? 

Thanks!

Andrée Starå

Hi,

Try this.

Status RYG is the column with the RYG value.

Exchange "Red" to "Green" to count Green and the other colors.

=COUNTIF([Status RYG]:[Status RYG]; "Red")    

The same version but with the below changes for your and others convenience.

=COUNTIF([Symbol (Status RYG)]:[Symbol (Status RYG)], "Red")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting