Excel 3 If(And Comparison Result

[DeltaShell]

I am desperate for some help with a formula to add up paid equivalent hours.
8 hours is min and least amount paid, 8-12 hours paid @ 1.5X and over 12 paid @ 2x
and I'd like it all to be in one formula.... Is this even doable. ??

Formula
If D26 is equal or less than 8, then 8
If D26 is greater than 8 but less than 12 then E26-D26*1.5+8
If d26 is greater than 12 then =(E26-D26)-4*2)+14

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	B	C	D	E	F	G
25	Start	Finish	Total Hrs Worked	OT Hours		Equivalent hours
26	6.5	12.4	5.9	0		8
27	5.5	15.8	10.3	2.3		11.45
28	4.5	17.8	13.3	5.3		16.6
29	9.3	20.6	11.3	3.3		12.95
30	7	21	14	6		18
31	8.5	19.5	11	3		12.5
32	2.5	12.7	10.2	2.2		11.3
Sheet2

Cell Formulas
Range	Formula
D26:D32	D26	=C26-B26
E26:E32	E26	=MAX(0,D26-8)
G26:G32	G26	=IF(D26<=8,8,IF(D26<=12,D26*1.5-4,D26*2-10))

I used some algebra to shorten your formulas a bit, so check them out, but I think they work according to your description.

 

[DeltaShell]

Welcome to the MrExcel forum!

Try:

Book1
	B	C	D	E	F	G
25	Start	Finish	Total Hrs Worked	OT Hours		Equivalent hours
26	6.5	12.4	5.9	0		8
27	5.5	15.8	10.3	2.3		11.45
28	4.5	17.8	13.3	5.3		16.6
29	9.3	20.6	11.3	3.3		12.95
30	7	21	14	6		18
31	8.5	19.5	11	3		12.5
32	2.5	12.7	10.2	2.2		11.3
Sheet2

Cell Formulas
Range	Formula
D26:D32	D26	=C26-B26
E26:E32	E26	=MAX(0,D26-8)
G26:G32	G26	=IF(D26<=8,8,IF(D26<=12,D26*1.5-4,D26*2-10))

I used some algebra to shorten your formulas a bit, so check them out, but I think they work according to your description.

 

[DeltaShell]

WOW, Thanks so much for the speedy response. So much shorter. !!
Brilliant!