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

12 month rolling data - How do I produce a single arrow Icon to represent the total trend.

cma407

New Member
I have a work spreadsheet which shows absenteeism by month. I want to see a single Icon (Arrow) to show the primary trend across the most recent 12 months of data; for example, if the absenteeism trend line is declining - a single downward arrow. if the absenteeism over 12 months is increasing - a single upward arrow; and, if the absenteeism is consistent (for example 1 day each month) - a horizontal arrow.

I know how to use sparklines, but I need a formula to vary between the three options/icons in a single column due to large number of staff represented. The challenge is that my workplace has restricted use of macros and Visual Basic.

I'd be grateful for any assistance. I've attached a simple file to simply reflect five fictitious staff. Column N is where I'd like the formula to produce the arrow corresponding with the trendline (Note: I have simply inserted Icons as I'd like them to appear).

Thank you in advance,
Chris
 

Attachments

  • Example_Absenteeism_Sheet.xlsx
    30 KB · Views: 7
cma407
Test ...
for cell N2 =IF(O2>M2,"↓",IF(O2=M2,"→","↑"))
for cell O2 =TREND(B2:L2,B$1:L$1,M$1,TRUE)
and copy both as needed
 
Hi thanks for your reply velum. I've applied these formulas, but manipulating the table data to produce the opposite trend is not affecting the result in N2. As you can see for "Bill" in the revised spreadsheet and chart, the trend is now up, but the arrow icon has not changed to reflect this.
 

Attachments

  • Example_Absenteeism_Sheet.xlsx
    31 KB · Views: 8
It is a pity the up/down conditional formatting icons are the wrong colour for your purpose.

This solution is more of an 'app' built on Excel as a platform than a standard Excel solution. The name 'absences' is a relative reference to a row of data. The formula itself is given a name, using name manager, that refers to
= SUM( INDEX( TREND(absences), {2;1} ) * {1;-1} )
This takes the second value of the trend line and subtracts the first. The arrows are generated by the number format
"↗";"↘";"→"
and coloured using conditional formatting.

An alternative version of 'trend' is given by
= SIGN(ROUND(SUM(INDEX(TREND(absences),{2;1})*{1;-1}),1))
This rounds the trend gradient to eliminate small values and uses SIGN to give [ 1, 0,-1] rather than somewhat random appearing numbers.
 

Attachments

  • Absenteeism Sheet (PB).xlsx
    34.5 KB · Views: 9
No, I'm looking at the overall trend from B1 through M1. If this 12 month range is trending up, the up arrow, trending down, then down arrow etc.
 
It is a pity the up/down conditional formatting icons are the wrong colour for your purpose.

This solution is more of an 'app' built on Excel as a platform than a standard Excel solution. The name 'absences' is a relative reference to a row of data. The formula itself is given a name, using name manager, that refers to
= SUM( INDEX( TREND(absences), {2;1} ) * {1;-1} )
This takes the second value of the trend line and subtracts the first. The arrows are generated by the number format
"↗";"↘";"→"
and coloured using conditional formatting.

An alternative version of 'trend' is given by
= SIGN(ROUND(SUM(INDEX(TREND(absences),{2;1})*{1;-1}),1))
This rounds the trend gradient to eliminate small values and uses SIGN to give [ 1, 0,-1] rather than somewhat random appearing numbers.


Hi Peter, Thanks for this solution. However, I've attempted pasting each of these into column N, (and removing the apostrophe), but to no avail. The arrows don't seem to change for me even if I adjust the data. I do need the fractions to appear in the data also, as they refer to FTE (which is the full-time equivalence in hours lost to absenteeism).
 
Last edited:
cma407
My sample compares range B1:L1 to the last know M-column result.
How would You need to trend it?
As per the example charts I've prepared under the table (which don't appear on my employer's dashboard), the trend is the full twelve months. It is not a comparison against the last month. It is a rolling 12 month summary. As the 12th month expires (for example eight days taken during June last year) in June this year, the trend may tilt downwards if the employee has not been absent as frequently.

This gives our team leaders and indication (by employee) if absenteeism is trending down, up or stable. Therefore, the arrow needs to reflect the direction of the twelve-month trend line as indicated on the charts below.

I saw an older post and there didn't appear to be a result. It's a challenge that is turning my hair grey!!
 
cma407
Sooner or later, everyone will have grey hair ...

Okay - You want to see 'UP', if even trend is 0.0001% greater than value to which compare.

Next sample, without any colors - which You didn't wish.
 

Attachments

  • Example_Absenteeism_Sheet.xlsx
    31 KB · Views: 6
This is a modified version of vletm's earlier workbook with conditional formatting.
(First time I have used direct cell referencing in 5 years :eek:!)
 

Attachments

  • Example_Absenteeism_Sheet (V).xlsx
    32.4 KB · Views: 8
Please accept my apologies. Perhaps I should have set my own house in order before I started playing with other people's solutions :oops:.
I developed the earlier workbook using Office 365 insider and assumed that I knew how the solution would work on a standard installation.
It turns out that my name 'trend' (an array calculation that gives a single value) only gives the correct value when referenced if it is array-entered over more than a single cell :confused:!

I also used the opportunity to base the trend calculations on the dates given rather than the default sequence, giving a new formula for 'trend'
= SUM( TREND(absences,month,interval) * {-1,1} )
Perhaps I should now check that back on Office 365 insider?
 

Attachments

  • Absenteeism Sheet (PB).xlsx
    33.8 KB · Views: 15
Peter Bartholomew
Some phrases are useful and those would give correct image
... some ... hmm? ... something else ( eg easy, simple, 10 minute job ... ) . ;) Okay
Hi Peter, Thank you so much for your time and this solution. It will make life considerably easier as we have hundreds of employees and the data can become overwhelming.
I'm much in debt to you. Regards, Chris.
Peter Bartholomew
Some phrases are useful and those would give correct image
... some ... hmm? ... something else ( eg easy, simple, 10 minute job ... ) . ;) Okay
Hi velum, It looks like both you and Peter have steered me in the right direction in finding this solution. Thank you so much for your time and consideration of my initial request.

regards,
Chris
 
I appreciate your help earlier this year. It has enabled me to create named ranges and to produce a rolling trend (arrow) icon to reflect a range of data.

I have my arrow icons now appearing to the right of the pivot table lines; each line displaying totals (grouped, and showing a maximum of eight weeks).

However: when using the slicers to filter data, and there is no outcome for a particular week, the number of pivot table columns reduce the trend icons don't display as the named range has varied.

Q: Is there any way of getting the named range to recognise and adjust to a lesser number of columns my pivot table?


I'd be grateful for your advice again.
Thanks kindly,
Chris
 
I am only a very occasional user of the pivot table and, like you, have problems with the changes of layout that happen as data selections vary. I thought I had seen a setting that forces blank rows and columns to be displayed but that seems to not to apply to regular PTs. In the past, I have got sufficiently fed-up with the changing layout that I set out the layout I wanted on a fresh sheet and used GETPIVOTDATA to populate my new table from the pivot table. It is important to replace the hard-wired text strings that Excel produces by default with references. I use named references and array formulas but that is simply the way I work.

Any Pivot Table users out there with better ideas?
 
I am only a very occasional user of the pivot table and, like you, have problems with the changes of layout that happen as data selections vary. I thought I had seen a setting that forces blank rows and columns to be displayed but that seems to not to apply to regular PTs. In the past, I have got sufficiently fed-up with the changing layout that I set out the layout I wanted on a fresh sheet and used GETPIVOTDATA to populate my new table from the pivot table. It is important to replace the hard-wired text strings that Excel produces by default with references. I use named references and array formulas but that is simply the way I work.

Any Pivot Table users out there with better ideas?
Thanks for this info Peter. Again, greatly appreciated.
 
Back
Top