Forum Discussion
creating a top of the charts board
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.
NickMy 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.
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 )
