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
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) |