tough problem, maybe macros will solve it

[milos]

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.

 

[Don Guillett]

Why are you posting the same question in a different subject line 30 min
later?

 

[milos]

I had some troubles with IE when sending it, I was not sure if it was sent
correctly.Sorry, I will never do it again. Promise.
Anyway, is there a possible anwer for my problem?

Don Guillett píše:

 

[Max]

Here's a crack at this using non-array formulas ..

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

Workers' data (as posted) is assumed in Sheet2, where:
Labels in A1:B1 : name, Agent's reg.No
"Month" labels are within C1:N1, viz.: jan, feb, .. dec
(data is assumed running in row2 down)

Set this up in a new Sheet3:
Labels in A1:B1 : name, Agent's reg.No
"Month" labels within C1:N1, viz.: jan, feb, .. dec

In A2: =IF(Sheet2!A2="","",Sheet2!A2)
Copy A2 to B2

In C2: =Sheet2!C2
In D2: =Sheet2!D2+C2
Copy D2 across to N2

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

Select A2:O2, fill down as far as required, say to O10
to cover the max expected extent of data in Sheet2

Cols A & B simply replicates the data in Sheet2's cols A & B
Cols C to N computes the YTD cumulation of the total hours worked per worker
(by month)
Col O returns the "earliest month" that the YTD total hours worked >= 300
per worker (if any)

In Sheet1 (the agents' table):
Labels in A1:B1 : Agt name, Agt reg#
"Month" labels within C1:N1, viz.: jan, feb, .. dec

In C2:
=IF($B2="","",100*SUMPRODUCT((Sheet3!$B$2:$B$10=$B2)*(Sheet3!$O$2:$O$10=C$1)))
Copy C2 across to N2, fill down as far as required to populate
The above will return the required results in the agents' table
(The front multiplier "100" in the formula is the agents' $100 "once-off"
reward per worker)

Adapt the ranges in the formula to suit the extent of the table in Sheet3
Note that the month labels filled within C1:N1 should be consistent in all 3
sheets

 

[Max]

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

 

[milos]

T H A N K Y O U !!! T H A N K Y O U !!! T H A N K Y O U !!!
That's exactly what I was looking for. I owe you a beer. Or two at least;-)
Well, you've made my day much nicer.


Max píše:

Here's a crack at this using non-array formulas ..

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

Workers' data (as posted) is assumed in Sheet2, where:
Labels in A1:B1 : name, Agent's reg.No
"Month" labels are within C1:N1, viz.: jan, feb, .. dec
(data is assumed running in row2 down)

Set this up in a new Sheet3:
Labels in A1:B1 : name, Agent's reg.No
"Month" labels within C1:N1, viz.: jan, feb, .. dec

In A2: =IF(Sheet2!A2="","",Sheet2!A2)
Copy A2 to B2

In C2: =Sheet2!C2
In D2: =Sheet2!D2+C2
Copy D2 across to N2

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

Select A2:O2, fill down as far as required, say to O10
to cover the max expected extent of data in Sheet2

Cols A & B simply replicates the data in Sheet2's cols A & B
Cols C to N computes the YTD cumulation of the total hours worked per worker
(by month)
Col O returns the "earliest month" that the YTD total hours worked >= 300
per worker (if any)

In Sheet1 (the agents' table):
Labels in A1:B1 : Agt name, Agt reg#
"Month" labels within C1:N1, viz.: jan, feb, .. dec

In C2:
=IF($B2="","",100*SUMPRODUCT((Sheet3!$B$2:$B$10=$B2)*(Sheet3!$O$2:$O$10=C$1)))
Copy C2 across to N2, fill down as far as required to populate
The above will return the required results in the agents' table
(The front multiplier "100" in the formula is the agents' $100 "once-off"
reward per worker)

Adapt the ranges in the formula to suit the extent of the table in Sheet3
Note that the month labels filled within C1:N1 should be consistent in all 3
sheets

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.

 

[Max]

T H A N K Y O U !!! T H A N K Y O U !!! T H A N K Y O U !!!
That's exactly what I was looking for. I owe you a beer. Or two at least;-)
Well, you've made my day much nicer.

Glad it delivered fine, milos !
Thanks for the feedback ..