Payout based on hours rate and designation

[GoJakie]

Dear Friends,
There are two criteria where I am unable to write a formula and need help. I want to update the Payout column with a formula based on number of hours worked by the employee and also based on their role/designation. Please help !

******** language="JavaScript" ************************************************************************>

Book1
	A	B	C	D	E	F
1
2		Hours		Rate
3		From	To	Manager	Officer
4		< 50:00:00		100	150
5		50:01:00	75:00:00	125	180
6		75:01:00	85:00:00	150	210
7		85:00:00	95:00:00	175	240
8		> 95:01:00		200	270
9
10		Employee	Role	Hours	Payout
11		Emp1	Manager	52:52:00
12		Emp2	Manager	101:23:00
13		Emp3	Officer	00:00
14		Emp4	Manager	76:09:00
15		Emp5	Officer	44:25:00
16		Emp6	Manager	75:00:00
17		Emp7	Officer	85:00:00
18		Emp8	Officer	93:39:00
19		Emp9	Officer	111:53:00
20		Emp10	Manager	80:11:00
21
Sheet1

 

[GoJakie]

Awaiting assitance. Appreciate some help here. Thank you.

 

[GoJakie]

Awaiting assitance. Appreciate some help here. Thank you.

Please help

 

[GoJakie]

Awaiting assitance. Appreciate some help here. Thank you.

I have posted the same question on google groups https://groups.google.com/forum/#!topic/microsoft.public.excel.worksheet.functions/nyIFAGPQuZc
Kindly assist

 

[GoJakie]

I have posted the same question on google groups https://groups.google.com/forum/#!topic/microsoft.public.excel.worksheet.functions/nyIFAGPQuZc
Kindly assist

No responses from anywhere. Please help me !

 

[GoJakie]

I am eagerly awaiting first reply from someone. I am assuming I may have not explained my problem correctly. Please let me know if you need more info. Appreciate some

 

[GoJakie]

Please help

 

[GoJakie]

Will this require Macro? I tried all kinds of IFs and other other formulas with no success

 

[misrasomendra]

Hi GoJakie,

Please see below, one propose solution.

Excel 2010
	D	E	F	G	H	I	J	K
2	Hours		Rate			Hours	Rate
3	From	To	Manager	Officer			Manager	Officer
4	< 50:00:00		100	150		00:00:00	0	0
5	50:01:00	75:00:00	125	180		50:00:00	100	150
6	75:01:00	85:00:00	150	210		75:00:00	125	180
7	85:01:00	95:00:00	175	240		85:00:00	150	210
8	> 95:01:00		200	270		95:00:00	175	240
9							200	270
10	Employee	Role	Hours	Payout
11	Emp1	Manager	52:52:00	125
12	Emp2	Manager	101:23:00	200
13	Emp3	Officer	0:00:00	0
14	Emp4	Manager	76:09:00	150
15	Emp5	Officer	44:25:00	150
16	Emp6	Manager	75:00:00	125
17	Emp7	Officer	85:00:00	210
18	Emp8	Officer	93:39:00	240
19	Emp9	Officer	111:53:00	270
20	Emp10	Manager	80:11:00	150
Sheet1

I am proposing to layout you criteria as yellow cells. Formula used in Payout is

=INDEX($J$4:$K$9,SMALL(IF(FREQUENCY(F11,$I$4:$I$8),ROW($I$4:$I$9)-ROW($I$4)+1),1),MATCH(E11,$J$3:$K$3,0))
copy down as required.

Note: this is an array formula so must enter with Ctrl+Shift+Enter, and not just enter.

Regards,