Sparklines and icon sets

[Kevin222]

Dear Excel Folks,

I am trying to create a worksheet to show trends in students performance in a fitness test. I want to display icons to show whether their present attempt was better, in line with or worse than their previous effort.

So for example if score 2 was better than score one it would display an upward arrow, if it was the same it would be a flat arrow and if worse than the score before it would be a downwards arrow

I also want to show this as a sparkline at the end to show their overall trend in their performance.

Is there a way of using icon sets to compare their score against the previous score?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Score 1[/TD]
[TD]Score 2[/TD]
[TD]Score 3[/TD]
[TD]Score 4[/TD]
[TD]Trend (Sparkline)[/TD]
[/TR]
[TR]
[TD]Student 1[/TD]
[TD]1.1[/TD]
[TD]1.3[/TD]
[TD]1.7[/TD]
[TD]1.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 2[/TD]
[TD]2.2[/TD]
[TD]2.2[/TD]
[TD]2.7[/TD]
[TD]3.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student 3[/TD]
[TD]1.8[/TD]
[TD]2.3[/TD]
[TD]2.2[/TD]
[TD]2.1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Many Thanks

 

[Caleeco]

I would suggest using Conditional Formatting to achieve this. Define the following relationships:
-1 = Down Arrow
0 = Flat Arrow
1 = Up Arrow

You would need a column of cells inbetween each cell to display the arrow. You're pseudo code for Student 1 Score 2 would for example be:

=IF(Score1=Score2,0,IF(Score1>Score2,-1,1))

Then you would go to the CF options, and select "Show Icon Only"

Hope that made sense
Caleeco

 

[Kevin222]

Hi Caleeco,

Thankyou that works really well for showing how a student compared to their last score. However it means that the sparkline at the end includes the data from the additional icon cells. Is there a way that the sparkline can ignore the additional icon cells so just shows the trend from all the students scores?

Many thanks for your help - very much appreciated

Kevin

 

[Caleeco]

Ah good point, I forgot you were using Sparklines also. Yes you can indeed.

I'm not sure how large your spreadsheet is, or how long it would take to setup.. but the following worked on my test sheet.

1. For Student 1, select each score whilst holding down the CTRL key
2. Give this selection of cells a name (eg. S1Score), by typing in the Name Box (Just above cell A1 on the left hand side)
3. When you create a sparksline for student 1, and it asks for the data range type in =S1Score

That should then omit you conditionally formatted cells.

An alternative way would be to have a hidden sheet with just score data, and the sparkline on the main page reading off that.

Hope that helps, let me know if you need more detailed explanation.

Thanks
Caleeco