How to Create Tend Arrows with a Function

10/31/18 Edited 12/09/19

I have a cross sheet table with data displayed on it that works fine. Just wondering how I can display a trend arrow using a function for Up, Down and Across.

 

Basically, I am measuring KPI performance against the number of days since the last time we had for example a Lost Time Injury or as is known as an LTI. This work fine with Paul's help. I now have a column which calculates what the number of days were the day before, so I can determine if its the Same, More, or Less.

In the next Column I want to display the Trend as a arrow.

This data is presented on a dashboard so that managers know their performance in regards to Health and Safety?

Table looks like this

[Type of Event], [Days Since Last Event], [Day Before], [Arrow]

The Italic Columns are extracted via cross sheet reference.

 

Regards

 

Steve

Comments

  • Hi Steve,

    Each arrow has its on value like "Up", "Sideways", etc. So all you need to do is to create nested IF formula and place it within column set to Symbols.

    You can easily check what values they has by placing different arrows in column and enter =value(cell with arrow). Then you'll be able to create your formulas.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Steve,

    =IF([Days Since Last Event]@row = [Day Before]@row; "Sideways"; IF([Days Since Last Event]@row > [Day Before]@row; "Up"; "Down"))    

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

    =IF([Days Since Last Event]@row = [Day Before]@row, "Sideways", IF([Days Since Last Event]@row > [Day Before]@row, "Up", "Down"))

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

    I hope this helps you!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thanks Andree,

    Works Perfectly.

    Maybe you need to put the comment in that after you have put the function in you change the column to the Symbols and select the arrows you want to use.

    Kind Regards

    Steve Moss

  • Thanks,

    Marcin,

    Andree sorted it perfectly for me.

     

    Kind Regards

     

    Steve

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Great!

    Happy to help!

    Great suggestion regarding the type of symbols and it's important to choose the correct one because otherwise, the formula needs to be updated to reflect the values for other symbols.

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.