value calculation

[pyclen]

Hi All,

I have a calculation that I would like to automate.
I need to calculate cost to make something however that cost depends on time needed. Also, the cost per hour is reduced when a 3rd or more shifts are needed (up to 10, if more than 10 I need to calculate by hand anyway).
Is there a way to calculate the cost using two different hourly rates depending on the length of time needed?

Any help is greatly appreciated

value calculation.xlsx
	B	C	D	E	F	G
3		length of shift (h)	8
4
5			1st, 2nd shift	3rd - 10th shift
6		Cost per shift ($)	1000	750
7
8
9			1st example		2nd example
10		items per hour	100		100
11		items to be made	1000		3000
12		time needed	10		30
13
14		total cost	2000		4000	3500
15					'wrong calculation'	correct calculation
Sheet1

Cell Formulas
Range	Formula
D12,F12	D12	=IF(OR(D10="",D11=""),"",D11/D10)
D14,F14	D14	=IF(D12="",0,CEILING(D12,8)/8*1000)
G14	G14	=2*(D6+E6)

 

[Snakehips]

@pyclen Maybe like below?

Book1
	C	D	E	F
3	length of shift (h)	8
4
5		1st, 2nd shift	3rd - 10th shift
6	Cost per shift ($)	1000	750
7
8
9		1st example		2nd example
10	items per hour	100		100
11	items to be made	1000		3000
12	time needed	10		30
13
14	total cost	2000		3500
Sheet1

Cell Formulas
Range	Formula
D12,F12	D12	=IF(OR(D10="",D11=""),"",D11/D10)
D14,F14	D14	=IF(D12="",0,LET(FT,ROUNDUP(D12/$D$3,),(MIN(FT,2)*$D$6)+(FT-2)*$E$6))

 

[pyclen]

@pyclen Maybe like below?

Book1
	C	D	E	F
3	length of shift (h)	8
4
5		1st, 2nd shift	3rd - 10th shift
6	Cost per shift ($)	1000	750
7
8
9		1st example		2nd example
10	items per hour	100		100
11	items to be made	1000		3000
12	time needed	10		30
13
14	total cost	2000		3500
Sheet1

Cell Formulas
Range	Formula
D12,F12	D12	=IF(OR(D10="",D11=""),"",D11/D10)
D14,F14	D14	=IF(D12="",0,LET(FT,ROUNDUP(D12/$D$3,),(MIN(FT,2)*$D$6)+(FT-2)*$E$6))

Excellent, that will work
thank you very much for the help

This board really is so very helpful and I as a somewhat novice (I know enough to damage ) can learn so much from reading all the posts and answers

 

[Snakehips]

@pyclen Just realised that the above formula fails to give the correct result for a single shift!!

Please revise to be

 =IF(D12="",0,LET(FT,ROUNDUP(D12/$D$3,),(MIN(FT,2)*$D$6)+(MAX(0,FT-2))*$E$6))

 

[pyclen]

Thank you so much for letting me know.
I really appreciate you going back and coming up with a corrected version.
Enjoy your weekend