Help With Formula

The Shadowman

New Member
Joined
May 5, 2021
Messages
34
Office Version
  1. 2019
Platform
  1. Windows
Firstly, let me explain that I am a novice when it come to Excel 2019. Can somebody please tell me if the following is possible. I have asked before, but got no response, due, I think to my poor explanation of the problem.

I have two columns CY and DG both comprising 15 rows. CY is a list of names that move up and down automatically as scores are placed in Column DG. Column DG gets its information from this formula =RANK(LARGE($BD$26:$BD$40,ROWS(CY$26:CY26)),$BD$26:$BD$40). What I want to do is, in a new column, place "up and down " arrows that show the movement of the league ie as CY names move up or down. I have tried IF formulas, they get the arrows in place, but they do not move with the corresponding name, they just stay still. If you can help me achieve this goal I would be very grateful.

Thanks in advance
Robert
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi Shadowman,

If you only have one set of rankings then you will need to use VBA to store the old set, calculate the new, insert the arrows.

If you have old and new sets of rankings then you can use a Conditional Format with icons to indicate higher/lower ranking.
NOTE: The icons don't show in an XL2BB post but you can see them on the screencap following.

TheShadowman.xlsx
IJK
4Old RankNew Rank
525 
641 
768 
833 
957 
1076 
Sheet1
Cell Formulas
RangeFormula
K5:K10K5=I5-J5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K5:K10Other TypeIcon setNO


The CF might look like this.

1636832018445.png
 
Upvote 0
Hi Toadstool. Thanks for the detailed reply. Unfortunately it doesn't work for me. I think this is because of the way I have set the spreadsheet up. I have uploaded an SC which I hope will throw some light on my problem. The column with the names in puts the leader at the top and so on down to last place. The position column give the position of the player 1-15th place so I have two columns with changing data, and I fear that may be where the problem lies. Using your suggested method puts green arrows in all postions. Anyway, have a look at the picture and see if you can make sense of it.

Thanks for trying

Robert
mrexcel.jpg
 
Upvote 0
Please confirm
  1. What is in column C rows 26 to 40
  2. What is in column BD rows 26 to 40
  3. What is in column CY rows 26 to 40
  4. Into which column the red INDEX is placed
  5. Into which column the blue RANK is placed
 
Upvote 0
1. C 26 - 40. Names (part of grand total ie shows each week's score separately)
2. BD 26 - 40. Gives Grand Total of ten weeks play D26 - R26
3. CY 26 - 40. Gives position in league
4. Red INDEX Puts Names next to Positions (CY26 - 40)
5 Blue RANK. CY26 -40 (position)

I have uploaded a a picture of the whole sheet to give you a better idea - I hope it helps
Mrexcel1.jpg
 
Upvote 0
I can't tell which columns have the weekly scores but I've put them in columns D to M, so the Grand Total goes in column BD as
Excel Formula:
=SUM(D26:M26)
To tell if the position has changed you need the previous week scores, so column BE contains last weeks total by subtracting the last week with any score. The AGGREGATE gives a 4 as the last week with any number and the INDEX retries that number to subtract from the Grand Total
Excel Formula:
=BD26-INDEX($D26:$M26,AGGREGATE(14,6,COLUMN($D$26:$M$26)-COLUMN($C$26)/($D$27:$M$40>0),1))

I've added columns CZ and DA to calculate Position last week and then column CX compares the position last week against this week and displays a positive for improvement, zero for no change and negative for a move to a lower position

TheShadowman.xlsx
CDEFGHIJKLMNBCBDBEBFCOCWCXCYCZDA
25Name12345678910Grand TotalGT Last WeekLeader BoardPositionPosition Last WeekLeader Board Last Week
26Bob112340000002016Lois211Paul
27Sue1461230000003532Zoey322Tom
28Tim2113170000003316Tom-132Lois
29Jim7224130000004633Paul-334Kate
30Kate5161630000004037Fred155Zoey
31Zoey7218150000005136Mike366Fred
32Tom10142060000005044Jim077Jim
33Mike0919200000004828Bill288Sue
34Dave199170000003619Kate-599Mike
35Harry000000000000Dave21010Bill
36Bill1911200000004121Sue-31110Nate
37Nate811260000002721Tim21212Dave
38Lois4221890000005344Nate-21313Bob
39Fred01816150000004934Bob-11413Tim
40Paul18151700000005050Harry01515Harry
NewWay
Cell Formulas
RangeFormula
BD26:BD40BD26=SUM(D26:M26)
BE26:BE40BE26=BD26-INDEX($D26:$M26,AGGREGATE(14,6,COLUMN($D$26:$M$26)-COLUMN($C$26)/($D$27:$M$40>0),1))
CX26:CX40CX26=MATCH($CO26,$DA$26:$DA$40,0)-(ROW()-ROW($CX$25))
CY26:CY40CY26=RANK(LARGE($BD$26:$BD$40,ROWS($CY$26:$CY26)),$BD$26:$BD$40)
CZ26:CZ40CZ26=RANK(LARGE($BE$26:$BE$40,ROWS($CZ$26:$CZ26)),$BE$26:$BE$40)
DA26:DA40DA26=INDEX($C:$C,AGGREGATE(15,6,ROW($C$26:$C$40)/($BE$26:$BE$40=LARGE($BE$26:$BE$40,ROWS(CP$26:CP26))),COUNTIF(CZ$26:CZ26,CZ26)))
CO26:CO40CO26=INDEX($C:$C,AGGREGATE(15,6,ROW($C$26:$C$40)/($BD$26:$BD$40=LARGE($BD$26:$BD$40,ROWS(CP$26:CP26))),COUNTIF(CY$26:CY26,CY26)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
CX26:CX40Other TypeIcon setNO
 
Upvote 0
Solution
As I previously said, XL2BB doesn't display icons but this screenshot demonstrates the Conditional Format setting.

If you want the movement numbers hidden then you could narrow the column width or change the format to display white text on a white background.

1636920893609.png
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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
Back
Top