Use Arrows to determine if there was an improved with a conditional maybe

[karved]

Hi there,

I want to do something like a ranking for a team, but I want to have the insights of the improve of each person, so for example I have a list of 5 people and I want to add a conditional with an arrow that if a week ago the performance was 5 and this week is 7 then we will have a green arrow pointing up, if the performance was 9 and the next week is 7 then we will have a red arrow pointing down, and if there is not movement like 6 one week and 6 the other week there will be a yellow line.

Is there a way to do that? and I want to have it continuously so every week it compares the last result with the new result so I can compare directly in the view the result instead of having to look into the data individual and check each performance.

Thanks.

[CK76]

Yes, you can use Conditional Format or formula to do it. But how, will depend on your data layout and set up.

I'd recommend uploading sample workbook.

To upload a file, use "Go Advanced" button and click on "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

[karved]

OK I have uploaded

[CK76]

It isn't clear how you'd have data organized for multiple weeks...

But something like attached. I've changed Q6 formula to fetch latest date's stat, and then lookup previous week's data using Max(date column)-1.
NOTE: You will need to adjust Max(date column)-1 as needed, depending on how your data is organized/stored for each week.

Though I think you meant reverse of your initial post, based on rank ( If latest rank is 5 and previous was 7, then green arrow up).

[karved]

I see thank you so much, actually now I can work under that to fix the data, that is not the file that I will use, it was just an example but thanks to your formula now I know how to continue, so thank you so much for the help

[karved]

CK76 other question, what happen if there is no improve, and the score is the same? can it show like something in yellow? saying that there is no change, as it is right now it shows an improve even if it is the same from one score to other.

[CK76]

In that case, I'd adjust it like attached.

Formula in T6 is changed to...
=SIGN(P6-IFERROR(INDEX(TD!$AB$2:$AB$38,AGGREGATE(15,6,ROW($1:$37)/(TD!$AC$2:$AC$38=Q6)/(TD!$AF$2:$AF$38=(MAX(TD!$AF$2:$AF$38)-1)),1)),""))

This will return -1 if improved, 0 if same and 1 if down. So the icon set order is reversed.

[karved]

CK76 I don't want to take advantage of your kindness, but would you mind checking this new file, I'm attaching the file as I will use it and I have been facing several issues maybe you know how to fix them.

The first issue is that., on this dashboard we will have constant data coming So the Pivot table that I have in the sheet TD will continuously getting larger, I inserted random data to have data to show you, but the dates will continue growing so I have no idea how to get the pivot to move with the new data without affecting the other data.

Second issue that I'm facing, so with the arrows you did help me a lot!! and thank you so much for that but how can I make them to move with the data that is coming? so if I have different dates it will always compare the last date with the previous date? when we have 2 dates is easy but if it continues to gathering data it will not compare the last 2 results and I will have to move manually all the formulas.

I think I'm really abusing of your help but hey if you think is to much thank you any way, I have attached the file so you can take a look on it and help me out cause I have tried watching every single youtube video but I'm stuck in this for over 3 weeks already.

[CK76]

If pivot grows, make sure nothing else is stored in the direction the pivot grows in.

In general, I tend to keep pivot that extends column wise, in it's own sheet to avoid issues.

Then, it's matter of setting up dynamic named ranges to perform calculation. See link below.
https://exceljet.net/formula/dynamic...nge-with-index

Alternately, you can utilize data model and use Dax measures and calculated columns to return desired result.

[karved]

Hey thank you so so much that actually will help me even better. Thanks