Dear Friends
I have a pivot table whose excel output I have attached as Test.
In attached Test file , I have names against the number of sales on weekly basis.
I need to calculate amount to be paid as following condition.
Condition:
A person should have consecutive more than 10 sales for more than 1 week.
Example: Rohit
14,12,17,10,13 ie 5 consecutive weeks so it should calculate 500-100 = 400
Yuvraj: 17,15 so it is 200-100 = 100
if streak of 10+ is broken again it becomes eligible if 10+ sales is done consecutively.
Thanks in advance for your help.
Regards
Chintan