How to Create Tend Arrows with a Function
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.
-
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 EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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 EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!