table agents

[Guest]

I have agents who find workers for us (small personnel company)
I have a list of agents with their reg.numbers :
tab.1 - Agents
a1 a2 a3 a4 a5 a6 a7
name reg.No jan feb march apr may
M.L. 1
M.K. 2
L.O. 3


And in another sheet I have a list of workers, who were contracted by
agents, with hours they worked in month.
tab. 2 - Workers
work hours in months
a1 a2 a3 a4 a5
a6 a7
name Agent's reg.No jan feb march apr
may
John 1 130 130 150
120 130
Jim 2 80
110
Jane 1 100 30 120
135
Clark 3 23 125
80 190
Al 2 120 120
80 300
Sue 3 120 120
120

If a worker has more than 300 hours together, in this month when he reaches
this, his agent deserves $100 reward. But the agent doesn't get any more many
for this worker, only once for one worker.
So agent M.L. (Reg.No - 1) will get money for contracting John in March and
for Jane in April. But he will get money for them only in this months, no
matter how much they will work in the future. Agent M.K. will get money in
April, because "his" Al has passed 300 hours in this month. He won't get any
more money for him in may.

Well, and now for the question. What should I use to put in the table 1., in
column of each month. I want to find out, how many agent's workers have just
passed the 300 hours limit in this month (excluding workers who did so in
previous months).
So in this case, M.L. will have in 100 in March, 100 in April, M.K: 100 in
april and L.O.: 200 in may (because of Clark and Sue). All other cells in
Tab.1 will be 0.
Answering this would be very helpful for me.

 

[Guest]

See one crack at this using non-array formulas
as responsed in your earlier thread ..

A sample construct is available at:
http://www.savefile.com/files/2697802
AutoCalc_AgentsCommissions.xls


---

 

[Guest]

Tweak to formula in Sheet3, in line:

In O2:
=IF(MAX(C2:N2)<300,"",INDEX($C$1:$N$1,MATCH(300,C2:N2,1)+1))

Make it as

In O2:
=IF(MAX(C2:N2)<300,"",IF(ISNUMBER(MATCH(300,C2:N2,0)),INDEX($C$1:$N$1,MATCH(300,C2:N2,0)),INDEX($C$1:$N$1,MATCH(300,C2:N2,1)+1)))

Revised sample available at:
http://savefile.com/files/2697802
AutoCalc_AgentsCommissions_2.xls


---