Another INDEX/MATCH type question (maybe)?

[Roghaltz]

Hi All,
Would love everyone's input on the following situation. We have a quarterly sales contest across our sales force. Management is experimenting with different incentives and created a model to predict who will win the sales contest. Other key facts

1. There are between 6 and 12 participants each quarter (varies randomly)
2. Management considers that model correct if the winner is among the top 25% of those predicted for that quarter (i.e. if there are 8 participants, then if the salesman whose actual sales rank is 1 in that quarter, had a predicted sales rank of 1 or 2, then the model is correct. If 12 participants, then a predicted rank of 1,2 or 3 would be correct). If not a multiple of three, rounding is fine.

Excel 2010

	A	B	C	D	E	F
Salesman	Quarter	Predicted SalesRank	Actual SalesRank	Prize	Model Correct?

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]2014 Q1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]2014 Q1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]a3[/TD]
[TD="align: center"]2014 Q1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]..[/TD]
[TD="align: center"][/TD]
[TD="align: center"]..[/TD]
[TD="align: center"]..[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]a8[/TD]
[TD="align: center"]2014 Q1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]2014 Q2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]2014 Q2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]a3[/TD]
[TD="align: center"]2014 Q2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]..[/TD]
[TD="align: center"][/TD]
[TD="align: center"]..[/TD]
[TD="align: center"]..[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]a8[/TD]
[TD="align: center"]2014 Q2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]2014 Q3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]2014 Q3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]a3[/TD]
[TD="align: center"]2014 Q3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]..[/TD]
[TD="align: center"][/TD]
[TD="align: center"]..[/TD]
[TD="align: center"]..[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]a10[/TD]
[TD="align: center"]2014 Q3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]640[/TD]
[TD="align: center"]Y[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]a1[/TD]
[TD="align: center"]2014 Q4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]a2[/TD]
[TD="align: center"]2014 Q4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]a3[/TD]
[TD="align: center"]2014 Q4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]..[/TD]
[TD="align: center"][/TD]
[TD="align: center"]..[/TD]
[TD="align: center"]..[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]a12[/TD]
[TD="align: center"]2014 Q4[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]960[/TD]
[TD="align: center"]Y[/TD]

</tbody>

Sheet1​

Management would like to know two things:
A. Was the model correct for the last 3 quarters (on a rolling basis) There are many quarters
B. What is the total prize for the 3 quarter period? Total prize is basically the product of the number of salesman over the last 3 quarters times a constant (i.e. Q1 = 8 participants, Q2 = 8, Q3 = 10 implies 8x8x10 = 640)

I figured out a very long brute force method that involves the creation of a column for each salesman, but was hoping there was a more elegant solution.

Thanks in advance.

 

[svendiamond]

So you need to calculate the number of salesmen for that quarter:

=COUNTIF(B:B,B2) 'which would be eight for 2014 Q1.

Top 25% ranks of that:

=COUNTIF(B:B,B2)/4 'which would be 2 (only ranks 1 and 2 are in the top 25%)

Now... you only need to calculate the "model correct" column for the winner or do you want to calculate it for all contestants? If you only need the winner then this should work in cell F2 and fill down:

=IF(D2=1,IF(C2<=(COUNTIF(B:B,B2)/4),"Y","N"),"")

 

[Roghaltz]

Thanks Sven. I think I have a handle on the prize calculation using what you suggest.

As to the Model Correct calculation: I am defining the model as being correct only when it is correct for the last 3 quarters in a row. So I need to determine if the model is correct for that quarter and the previous two quarters.

Any thoughts?

Thanks.

 

[svendiamond]

Well, my first thought would be to add a helper column and if you get three Y's in a row in column F, then "Y" -- and go by that new column (G, I suppose).

 

[Roghaltz]

Sounds good, not sure why I didn't think of that...could be too much eggnog...

Thanks