creating a top of the charts board

%3CLINGO-SUB%20id%3D%22lingo-sub-1924128%22%20slang%3D%22en-US%22%3Ecreating%20a%20top%20of%20the%20charts%20board%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1924128%22%20slang%3D%22en-US%22%3E%3CP%3Ehey%20guys%2C%20I%20produce%20a%20leaderboard%20each%20month%20for%20our%20top%20salespeople.%20its%20been%20run%20the%20same%20way%20for%20years.%20but%20I%20wanted%20to%20give%20it%20an%20overhaul%20but%20coming%20up%20against%20my%20poor%20excel%20know-how.%20I%20want%20to%20create%20a%20%22top%20of%20the%20charts%22%20league%20with%20each%20individual%20results%20with%20an%20arrow%20up%20and%20down%20to%20show%20if%20they%20have%20moved%20in%20the%20charts%20month%20vs%20month%2C%20ideally%20with%20a%20figure.%20eg.%20UP6%20or%20DOWN3%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20it%20will%20be%20more%20motivating%20to%20our%20newer%20starters%20to%20see%20if%20they%20are%20moving%20up.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20is%20how%20I%20have%20the%20table%20set%20up%20so%20far.%20I%20receive%20the%20raw%20data%20once%20a%20month%20and%20I%20create%20a%20simple%20pivot%20table%20to%20show%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22656px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22115px%22%20height%3D%22111px%22%3Esales%20person%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%22111px%22%3Edeals%20year%20to%20date%20(Nov)%3C%2FTD%3E%3CTD%20width%3D%2263px%22%20height%3D%22111px%22%3EMonth%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%22111px%22%3EPositon%3C%2FTD%3E%3CTD%20width%3D%2267px%22%20height%3D%22111px%22%3Esales%20person%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%22111px%22%3Edeals%20year%20to%20date%20(Oct)%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%22111px%22%3EMonth%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2268px%22%20height%3D%22111px%22%3EPosition%3C%2FTD%3E%3CTD%20width%3D%2265px%22%20height%3D%22111px%22%3EChange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22115px%22%20height%3D%2257px%22%3ESales%20Person%20A%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E808021.9%3C%2FTD%3E%3CTD%20width%3D%2263px%22%20height%3D%2257px%22%3EOct%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2267px%22%20height%3D%2257px%22%3ESales%20Person%20A%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E769494.2%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3ESept%3C%2FTD%3E%3CTD%20width%3D%2268px%22%20height%3D%2257px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2265px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22115px%22%20height%3D%2257px%22%3ESales%20Person%20B%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E764431.1%3C%2FTD%3E%3CTD%20width%3D%2263px%22%20height%3D%2257px%22%3EOct%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2267px%22%20height%3D%2257px%22%3ESales%20Person%20C%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E646092.8%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3ESept%3C%2FTD%3E%3CTD%20width%3D%2268px%22%20height%3D%2257px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2265px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22115px%22%20height%3D%2257px%22%3ESales%20Person%20C%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E656019.5%3C%2FTD%3E%3CTD%20width%3D%2263px%22%20height%3D%2257px%22%3EOct%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2267px%22%20height%3D%2257px%22%3ESales%20Person%20B%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E544869.5%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3ESept%3C%2FTD%3E%3CTD%20width%3D%2268px%22%20height%3D%2257px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2265px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22115px%22%20height%3D%2257px%22%3ESales%20Person%20D%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E450066.2%3C%2FTD%3E%3CTD%20width%3D%2263px%22%20height%3D%2257px%22%3EOct%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2267px%22%20height%3D%2257px%22%3ESales%20Person%20E%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E432537.1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3ESept%3C%2FTD%3E%3CTD%20width%3D%2268px%22%20height%3D%2257px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2265px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22115px%22%20height%3D%2257px%22%3ESales%20Person%20E%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E443488.1%3C%2FTD%3E%3CTD%20width%3D%2263px%22%20height%3D%2257px%22%3EOct%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2267px%22%20height%3D%2257px%22%3ESales%20Person%20F%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E426903.7%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3ESept%3C%2FTD%3E%3CTD%20width%3D%2268px%22%20height%3D%2257px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2265px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22115px%22%20height%3D%2257px%22%3ESales%20Person%20F%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E442634.9%3C%2FTD%3E%3CTD%20width%3D%2263px%22%20height%3D%2257px%22%3EOct%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2267px%22%20height%3D%2257px%22%3ESales%20Person%20E%3C%2FTD%3E%3CTD%20width%3D%2275px%22%20height%3D%2257px%22%3E322833.9%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2257px%22%3ESept%3C%2FTD%3E%3CTD%20width%3D%2268px%22%20height%3D%2257px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2265px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1924128%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1925040%22%20slang%3D%22en-US%22%3ERe%3A%20creating%20a%20top%20of%20the%20charts%20board%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1925040%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F878881%22%20target%3D%22_blank%22%3E%40nick1870%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%20the%20sequence%20of%20your%20columns%2C%20just%20because%20it%20made%20more%20sense%20to%20me%20to%20show%20the%20change%20in%20connection%20with%20the%20most%20recent%20month's%20results.%20The%20idea%20behind%20the%20formula%2C%20though%2C%20could%20still%20work%20in%20the%20layout%20you%20originally%20had.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20no%20doubt%20also%20a%20great%20example%20of%20where%20the%20new%20LET%20function%20would%20be%20useful.%20I%20didn't%20use%20it%20because%20I%20have%20no%20experience%20yet%2C%20but%20for%20my%20own%20learning%20I%20will%20go%20and%20try%20it.%20It%20would%20make%20the%20formula%20less%20lengthy%2C%20since%20it%20would%20no%20longer%20be%20necessary%20to%20repeat%20the%20%22MATCH%22%20function%20so%20many%20times.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1925260%22%20slang%3D%22en-US%22%3ERe%3A%20creating%20a%20top%20of%20the%20charts%20board%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1925260%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F878881%22%20target%3D%22_blank%22%3E%40nick1870%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20same%20solution%2C%20except%20using%20LET%20to%20shorten%20the%20formula.%20My%20first%20opportunity%20to%20use%20that%20new%20function.%3C%2FP%3E%3CP%3EHere's%20the%20original%3A%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3DIFS(%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-60px%22%3EMATCH(A2%2C%24F%242%3A%24F%247%2C0)%3DD2%2C%22Steady%22%2C%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-60px%22%3EMATCH(A2%2C%24F%242%3A%24F%247%2C0)%3CD2%3E%3C%2FD2%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-60px%22%3EMATCH(A2%2C%24F%242%3A%24F%247%2C0)%26gt%3BD2%2C%22Up%22%26amp%3BTEXT(MATCH(A2%2C%24F%242%3A%24F%247%2C0)-D2%2C%220%22)%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20here%20it%20is%20using%20%3CSTRONG%3ELET%3C%2FSTRONG%3E%2C%20which%20is%20a%20way%20to%20take%20that%20function%3CSTRONG%3E%20MATCH(A2%2C%24F%242%3A%24F%247%2C0)%3C%2FSTRONG%3E%20and%20replace%20it%20with%20%3CSTRONG%3EMtchVal%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3E%3DLET(MtchVal%2CMATCH(A2%2C%24F%242%3A%24F%247%2C0)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%3CSTRONG%3EIFS(%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-90px%22%3E%3CSTRONG%3EMtchVal%3DD2%2C%22Steady%22%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-90px%22%3E%3CSTRONG%3EMtchVal%3CD2%3E%3C%2FD2%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-90px%22%3E%3CSTRONG%3EMtchVal%26gt%3BD2%2C%22Up%22%26amp%3BTEXT(MtchVal-D2%2C%220%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-60px%22%3E%3CSTRONG%3E)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSTRONG%3E)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1925328%22%20slang%3D%22en-US%22%3ERe%3A%20creating%20a%20top%20of%20the%20charts%20board%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1925328%22%20slang%3D%22en-US%22%3EMathetes%2C%20thank%20you.%20I%20will%20study%20this%20tomorrow.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20will%20see%20if%20I%20can%20spot%20the%20sequence%20and%20apply%20it%20to%20my%20original%20with%2080%20sales%20people.%20Thanks%20again%20for%20providing%20the%20answer.%3CBR%20%2F%3E%3CBR%20%2F%3ENick%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

hey guys, I produce a leaderboard each month for our top salespeople. its been run the same way for years. but I wanted to give it an overhaul but coming up against my poor excel know-how. I want to create a "top of the charts" league with each individual results with an arrow up and down to show if they have moved in the charts month vs month, ideally with a figure. eg. UP6 or DOWN3 

 

I hope it will be more motivating to our newer starters to see if they are moving up. 

 

this is how I have the table set up so far. I receive the raw data once a month and I create a simple pivot table to show:

 

sales persondeals year to date (Nov)MonthPositonsales persondeals year to date (Oct)Month PositionChange
Sales Person A808021.9Oct1Sales Person A769494.2Sept1 
Sales Person B764431.1Oct2Sales Person C646092.8Sept2 
Sales Person C656019.5Oct3Sales Person B544869.5Sept3 
Sales Person D450066.2Oct4Sales Person E432537.1Sept4 
Sales Person E443488.1Oct5Sales Person F426903.7Sept5 
Sales Person F442634.9Oct6Sales Person E322833.9Sept6 

 

7 Replies

@nick1870 

 

I changed the sequence of your columns, just because it made more sense to me to show the change in connection with the most recent month's results. The idea behind the formula, though, could still work in the layout you originally had.

 

This is no doubt also a great example of where the new LET function would be useful. I didn't use it because I have no experience yet, but for my own learning I will go and try it. It would make the formula less lengthy, since it would no longer be necessary to repeat the "MATCH" function so many times.

@nick1870 

 

Here's the same solution, except using LET to shorten the formula. My first opportunity to use that new function.

Here's the original:

=IFS(

MATCH(A2,$F$2:$F$7,0)=D2,"Steady",

MATCH(A2,$F$2:$F$7,0)<D2,"Down"&TEXT(D2-MATCH(A2,$F$2:$F$7,0),"0"),

MATCH(A2,$F$2:$F$7,0)>D2,"Up"&TEXT(MATCH(A2,$F$2:$F$7,0)-D2,"0")

)

 

And here it is using LET, which is a way to take that function MATCH(A2,$F$2:$F$7,0) and replace it with MtchVal

=LET(MtchVal,MATCH(A2,$F$2:$F$7,0),

IFS(

MtchVal=D2,"Steady",

MtchVal<D2,"Down"&TEXT(D2-MtchVal,"0"),

MtchVal>D2,"Up"&TEXT(MtchVal-D2,"0")

)

)

Mathetes, thank you. I will study this tomorrow.

I will see if I can spot the sequence and apply it to my original with 80 sales people. Thanks again for providing the answer.

Nick

@nick1870 

 

My solution does depend on the fact that your prior month is sorted in order by their rank in that month. (That's what the MATCH function looks at, coming up with their prior rank based on where they were in the array)

 

It would also be possible to look at the prior rank number and do the math comparing current rank with that number......

 

And, of course, presuming that there are new hires and turnover, you could add other conditions to the IFS function to cover "New" -- no need for a label to apply to somebody who has left employment.

@mathetes 

Following up on your ideas!  I have not relied on either the sort order or any existing calculation of the ranking for the prior month.  Individuals that were not active within the previous month are ranked at n+1 for comparison purposes.  The text and colours are all produced by custom number formats.

= LET(
  priorPosns, RANK(PriorDeals, PriorDeals),
  prior, 
    XLOOKUP(
      person,
      priorList,
      priorPosns,
      1+MAX(priorPosns) ),
  change, prior - currentPosition#,
  change )

@Peter Bartholomew 

 

I never cease to be amazed at how many ways Excel offers to accomplish any given task.

 

In this case, I could replicate (with a bit more practice on my own) the formula itself. But it never would have occurred to me to use custom formatting in the manner you do; nor, frankly, do I really understand it.

 

Again, however, I suspect that conditional formatting could serve the same purpose, less elegantly perhas, but yet again, Excel holds multiple ways to get from A to Z.

@mathetes 

I know nothing of the OP's knowledge of Excel but I was confident that you could follow and might be interested.  Following the (dubious) principle "If a thing is worth doing, it is worth doing to excess", I could set different number formats from a set of conditional formats.  Who says the numbers never lie, in Excel what you see is not necessarily what you have.

image.png

Worse than that, it is also possible to use LET to perform the calculations that might otherwise be held in a helper range, but then display both the helper range and the final calculation as a single dynamic formula.  It is like a program module written to a cell.

= LET(
  currentPos, RANK(currentDeals, currentDeals),
  priorPosns, RANK(PriorDeals, PriorDeals),
  prior, XLOOKUP(person, priorList, priorPosns,
    1+COUNTA(priorList) ),
 change, prior - currentPos,
 IF({1,0}, currentPos, change) )