Calculate consistency incentive

[Sharada]

Hi,

I have an 2 excel files
1. Base Data file that contains records of employees according to their designation arranged in different worksheets.
2. CollatedDataSource containing the sales numbers for each month arranged territory wise in one worksheet and the consistency payout matrix in the second worksheet

I need to calculate consistency incentive payouts each month. For instance, for June, if a sales rep has achieved his target (Datasource file - Sales Data tab, Col J -L) in Apr, May & June then his consistency count is 3. If he achieves in May & June, then 2 and if only in June then 1. If he achieves Apr, May and not in June, then his consistency month count gets reset to 0. This consistency has to be checked every month till March. So each month, if the sales rep is achieving then in Dec his consistency month count should be 12. Then on basis of his consistency month count and slab (base data file), the incentive numbers need to be looked up from the consistency tab

Is there any way to do this in VBA?

Please help!

Have attached the sample datasource file and base data files for reference.

 

[bines53]

Hi

So I'll be able to understand, you can specify the desired values you attached example.


David

 

[Sharada]

Hi

So I'll be able to understand, you can specify the desired values you attached example.


David

Hi David,

Thanks so much for your response and apologies for my delayed reply. Have attached a sample file with the desired output.
Likewise this has to be calculated for 2000 - 3000 employees. Please review and let me know if you need more clarity

Regards
Sharada

 

[bines53]

Hi Sharada,

I see a file twice consistency of 3, and once a consistency of 2, what solution do you want exactly, is the greatest consistency, or the all consistency ?

David

 

[Sharada]

Hi Sharada,

I see a file twice consistency of 3, and once a consistency of 2, what solution do you want exactly, is the greatest consistency, or the all consistency ?

David

Hi David,

The example I gave here is for one employee. If we consider the data from April to Aug, this employee has achieved >= 100% in April, May & June hence in June the consistency will be 3 (4th Col). But in July he does not achieve the target (< 100) and so the consistency is reset to 0. Again in August the Consistency counter is 1 . Hope I'm making sense

April 100 1 1
May 101 1 2
June 120 1 3
July 99 0 0
Aug 101 1 1

 

[Sharada]

Hi David,

The example I gave here is for one employee. If we consider the data from April to Aug, this employee has achieved >= 100% in April, May & June hence in June the consistency will be 3 (4th Col). But in July he does not achieve the target (< 100) and so the consistency is reset to 0. Again in August the Consistency counter is 1 . Hope I'm making sense

April 100 1 1
May 101 1 2
June 120 1 3
July 99 0 0
Aug 101 1 1

So depending on the month, the consistency count has to be calculated. But need to factor the entire year while devising it in VBA

 

[bines53]

Hi Sharada,

If I understand correctly, you select a few months of testing, and you want to get the answer by last month?

David

 

[Sharada]

Hi Sharada,

If I understand correctly, you select a few months of testing, and you want to get the answer by last month?

David

Yes, that's right.. so if I'm calculating for July then I will need to check for consistency from April to July

 

[bines53]

Hi Sharada,

Check the formula, if that's okay.

=(MATCH(F11,A3:L3,0)-MATCH(E11,A3:L3,0)+1)-IFERROR(AGGREGATE(14,6,COLUMN(A3:L3)/(((INDEX(A4:L4,,MATCH(E11,A3:L3,0)):INDEX(A4:L4,,MATCH(F11,A3:L3,0)))>=100)=FALSE),1),0)