Arrows indicating position change

Chiefee

New Member
Joined
Feb 6, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
Hi,

After some great help on my last problem i have 1 more and hopefully my last!

I have created a league table and i am hoping to show coloured arrows to indicate a players movement when scores change each week. I can compare the rankings from separate weeks and have made up the 2nd week scores to trial but i am unsure how to link it to the correct player within the league table.

A super touch to it would be to show how many positions they had changed to, say up 2, down 5, or same along with the colour arrows.

a=Any help is very much appreciated.

Paul.
Cell Formulas
RangeFormula
D7:D26D7=INDEX($L$7:$L$26,MATCH(LARGE($R$7:$R$26-ROW($R$7:$R$26)/COUNT($R$7:$R$26),ROW(D7)-ROW(D$7)+1),$R$7:$R$26-ROW($R$7:$R$26)/COUNT($R$7:$R$26),0))
E7:E26E7=VLOOKUP($D7,$L$7:$Q$26,2,0)
F7:F26F7=VLOOKUP($D7,$L$7:$Q$26,3,0)
G7:G26G7=VLOOKUP($D7,$L$7:$Q$26,4,0)
H7:H26H7=VLOOKUP($D7,$L$7:$Q$26,5,0)
I7:I26I7=VLOOKUP($D7,$L$7:$Q$26,6,0)
J7:J26J7=SIGN(R7-S7)
L7:L26L7='Players & Money'!C5
M7:P26M7=SUM('Week 1:Week 40'!C6)
Q7:Q26Q7=SUM(M7*4,N7*3,O7*2,P7*1)
R7:R26R7=RANK(Q7,$Q$7:$Q$26,1)+COUNTIFS($Q$7:$Q$26,Q7,$M$7:$M$26,"<"&M7)+COUNTIFS($Q$7:$Q$26,Q7,$M$7:$M$26,M7,$N$7:$N$26,"<"&N7)+COUNTIFS($Q$7:$Q$26,Q7,$M$7:$M$26,M7,$N$7:$N$26,N7,$O$7:$O$26,"<"&O7)+COUNTIFS($Q$7:$Q$26,Q7,$M$7:$M$26,M7,$N$7:$N$26,N7,$O$7:$O$26,O7,$P$7:$P$26,"<"&P)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J7Other TypeIcon setNO
J7:J26Other TypeIcon setNO
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,432
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
DEFGHIJKLMNOPQRS
6Player Name4 Pointers3 Pointers2 Pointers1 PointersPointsPlayer Name4 Pointers3 Pointers2 Pointers1 PointersPointsRank2nd week rank
7Player 127755640Player 132573538
8Player 136856640Player 233483743
9Player 1156413592Player 355324392
10Player 145837570Player 4377552156
11Player 20942456-2Player 5662450149
12Player 43775529Player 6634445105
13Player 56624505Player 74672501313
14Player 74672500Player 822643011
15Player 167344492Player 9276243812
16Player 176433450Player 1042423224
17Player 66344455Player 1156413591816
18Player 35532437Player 127755642020
19Player 9276243-4Player 136856641919
20Player 19534542-7Player 145837571717
21Player 18436441-9Player 1552353757
22Player 15523537-2Player 167344491210
23Player 23348371Player 176433451111
24Player 1325735-5Player 18436441615
25Player 10424232-2Player 19534542714
26Player 82264300Player 209424561618
Master
Cell Formulas
RangeFormula
J7:J26J7=SUMIFS($R$7:$R$26,$L$7:$L$26,D7)-SUMIFS($S$7:$S$26,$L$7:$L$26,D7)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J7:J26Other TypeIcon setNO


1613579720960.png
 

Chiefee

New Member
Joined
Feb 6, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
How about
+Fluff 1.xlsm
DEFGHIJKLMNOPQRS
6Player Name4 Pointers3 Pointers2 Pointers1 PointersPointsPlayer Name4 Pointers3 Pointers2 Pointers1 PointersPointsRank2nd week rank
7Player 127755640Player 132573538
8Player 136856640Player 233483743
9Player 1156413592Player 355324392
10Player 145837570Player 4377552156
11Player 20942456-2Player 5662450149
12Player 43775529Player 6634445105
13Player 56624505Player 74672501313
14Player 74672500Player 822643011
15Player 167344492Player 9276243812
16Player 176433450Player 1042423224
17Player 66344455Player 1156413591816
18Player 35532437Player 127755642020
19Player 9276243-4Player 136856641919
20Player 19534542-7Player 145837571717
21Player 18436441-9Player 1552353757
22Player 15523537-2Player 167344491210
23Player 23348371Player 176433451111
24Player 1325735-5Player 18436441615
25Player 10424232-2Player 19534542714
26Player 82264300Player 209424561618
Master
Cell Formulas
RangeFormula
J7:J26J7=SUMIFS($R$7:$R$26,$L$7:$L$26,D7)-SUMIFS($S$7:$S$26,$L$7:$L$26,D7)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J7:J26Other TypeIcon setNO


View attachment 32360
Thank you for your help once again.

It almost works. im not sure if my ranking table is getting confused as the majority of arrows are correct but the odd one is 1 position out of what the arrow states. My method was to copy the current rank R7:R26 each week to Column S.

By the attached chart you hav displayed, players 11 & 16 i think should have a green arrow and player 20 a red arrow.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,432
Office Version
  1. 365
Platform
  1. Windows
Oops, forgot to set the icons properly, it should be
1613589544510.png

1613589572450.png
 
Solution

Chiefee

New Member
Joined
Feb 6, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
That seems to have solved it. I shall give it full test run soon to make sure it all works smoothly... thank again!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,432
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top