How to Create Tend Arrows with a Function

Steve Moss

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

Marcin P

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å

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

Steve Moss

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

Steve Moss

Thanks,

Marcin,

Andree sorted it perfectly for me.

 

Kind Regards

 

Steve

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.