Microsoft Excel - Calculate monthly incentive with the help of macro

would like to calculate month’s incentive to the sales rep. and Supervisor with the help of macro. Result need to come in sheet2 (Amount obtained for each Representatives and Supervisor). Data contains in sheet one. Can somebody give a solution?

Calculation as given below

5 representatives and 1 supervisor are here

If Sales amount =>5000 then Scheme Amount = representatives X 100 (5*100 =500)

In Second sheet result should come as follows

If all representatives are present then in sheet2 representative Column Scheme Amount/ representatives presented. (500/5=100) 100 each for (all) representatives

If any representative or representatives which are absent then in sheet2 representative Column scheme amount/ representatives present.

If any representative takes Half Day then he will get half of Scheme Amount

If Supervisor is present he should get alone amount of 100 as scheme amount.

If Supervisor is half day then he will get half of 100 scheme amount

If he is absent then he do not get scheme amount.

Legend

P – Present

H – half Day

A – Half Day

Sheet1

Date

Sale Amount

Scheme Amount

Rep1

Rep2

Rep3

Rep4

Rep5

Supervisor

01/01/2012

7600

A

P

P

H

P

P

Legend

02/01/2012

6500

P

P

P

P

P

P

P   =

Present

03/01/2012

4000

P

P

P

P

H

H

H  =

Half Day

04/01/2012

3500

P

P

P

P

P

P

A   =

Absent

05/01/2012

8000

P

A

A

P

P

P

06/01/2012

3500

P

P

P

H

A

P

07/01/2012

9000

A

H

P

P

P

P

08/01/2012

12000

P

P

P

P

P

P

09/01/2012

6500

P

P

P

P

P

H

10/01/2012

7500

P

P

P

P

P

p

11/01/2012

8000

P

P

P

P

P

p

12/01/2012

9000

P

H

P

A

P

p

13/01/2012

5500

P

P

P

P

P

H

14/01/2012

6500

P

P

H

P

P

h

15/01/2012

8500

P

P

P

P

P

p

Sheet2

Date

Sale Amount

Scheme Amount

Rep1

Rep2

Rep3

Rep4

Rep5

Supervisor

01/01/2012

7600

02/01/2012

6500

03/01/2012

4000

04/01/2012

3500

05/01/2012

8000

06/01/2012

3500

07/01/2012

9000

08/01/2012

12000

09/01/2012

6500

10/01/2012

7500

11/01/2012

8000

12/01/2012

9000

13/01/2012

5500

14/01/2012

6500

15/01/2012

8500

John,

are you working with a set scheme amount of 500 or a set fee of 100 per rep for a sale?  as I read you post carefully it seems like you are taking the number of reps hired x 100 to = 500 then looking at how many are actually there on the day of the sale for example only 4 are present on the sale day and one supervisor. 
if you are working with 500 dollars each rep will get 125 500/4=125 and the supervisor will get 500
or
if you are working with 100 per rep avail the 4 x 100 = 400 and so each rep will get 100 and the supervisor will get 400
this is an easy fix we just need to understand your scheme a little better.  (or at least I need to understand a little better)

Thanks Don

Sir, if there is sale 5000 or more then 100 for each rep. here 5 reps employed. so it will be 500. If any of the rep take half day then he will get half of hundred and his half divided to other four person. If one rep is absent his amount divided for other 4 rep.
If supervisor is present then he will get alone 100 only. If he take half then he will get half of 100. If he take leave then he did not get anything.
 

Hello John
Perhaps you could have given details of the answers that you want in the tables.
Do these answers look correct?
Scheme    Rep1    Rep2    Rep3    Rep4    Rep5    Supervisor
Amount                        
516.67    0.00    0.00    166.67    166.67    83.33    100
600.00    100.00    100.00    100.00    100.00    100.00    100
0.00    0.00    0.00    0.00    0.00    0.00    0
0.00    0.00    0.00    0.00    0.00    0.00    0
600.00    166.67    0.00    0.00    166.67    166.67    100
0.00    0.00    0.00    0.00    0.00    0.00    0
537.50    0.00    62.50    125.00    125.00    125.00    100
600.00    100.00    100.00    100.00    100.00    100.00    100
550.00    100.00    100.00    100.00    100.00    100.00    50
600.00    100.00    100.00    100.00    100.00    100.00    100
600.00    100.00    100.00    100.00    100.00    100.00    100
537.50    125.00    62.50    125.00    0.00    125.00    100
550.00    100.00    100.00    100.00    100.00    100.00    50
500.00    100.00    100.00    50.00    100.00    100.00    50
600.00    100.00    100.00    100.00    100.00    100.00    100

Harry

Correct sir, Thank you very much

Hi John,
It didn't need a macro.  See the attached file.
john1.zip
Regards
Harry

THANK YOU SIR
CAN WE DO THIS WITH MACRO

Hi John,
Yes it can but is there a particular reason why you wish to use a macro?
And which particular parts should be macro and which by formula?
Harry

John sorry I did not see your answer until harry got you fixed up.

However I do have one thing for you looking at what harry did you need to make one small change.
on your first sheet in colum C change your formula from =IF(B3>5000,500,0)
 to =IF(B3>=5000,500,0) to get credit for a sale that is 5000.
and drag it down.

And as for needing a macro why do you need a macro? 

Don

Good catch Donald.  I seem to have missed seeing the = sign in the original post.
Regards
Harry

Lucky catch Harry, lucky.. 

:) thanks

Sir one more help I need in this matter. If sales more than =6000 then incentive need to make 600. 
If the calculation make by macro then I can modify it.

Thanks and regards

Hi John,
You can still do that by formula.
=IF(B3>=6000,600,IF(B3>=5000,500,0))
Enter in C3 and copy down.  The rest remains the same.
Regards
Harry