A | B | C | D | E | F | G | H | |
1 | MATERIALS MADE BY EACH FACTORY | |||||||
2 | Factory Type | Output Type (used by FACTORY DETAILS table) | Output amount (used by FACTORY DETAILS table) | |||||
3 | NutFactory | Nut | 10 | |||||
4 | FactoryThatMakesNutFactory | NutFactory | 3 | |||||
5 | ScrewFactorySmall | Screw | 6 | |||||
6 | ScrewFactoryLarge | Screw | 11 | |||||
7 | ||||||||
8 | ||||||||
9 | FACTORY DETAILS | |||||||
10 | Factory | NutFactory | FactoryThatMakesNutFactory | FactoryThatMakesNutFactory | ScrewFactorySmall | ScrewFactoryLarge | ||
11 | OutputType | Nut | NutFactory | Nut | Screw | Screw | ||
12 | FactoryID | NutFactoryOutputNut | FactoryThatMakesNutFactoryOutputNutFactory | FactoryThatMakesNutFactoryOutputNut | ScrewFactorySmallOutputScrew | ScrewFactoryLargeOutputScrew | ||
13 | OvertimeActivationInHours | 11 | 3 | 9 | 8 | 1 | ||
14 | RegularOutput | 2 | 4 | 12 | 11 | 3 | ||
15 | OvertimeOutput | 4 | 13 | 10 | 5 | 2 | ||
16 | ||||||||
17 | ||||||||
18 | MATERIALS OUTPUT | |||||||
19 | Hours Worked (that day) | Nut | Screw | NutFactory | FactoryThatMakesNutFactory | ScrewFactorySmall | ScrewFactoryLarge | |
20 | Day 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 |
21 | Day 1 | 10 | 400 | 210 | 390 | 1 | 1 | 1 |
22 | Day 2 | 5 | 750 | 515 | 585 | 1 | 1 | 1 |
23 | Day 3 | 2 | 890 | 637 | 625 | 1 | 1 | 1 |
<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>
OK, so I have 3 tables on 3 different sheets in a workbook. I've simplified the problem above. I need to shorten the formulas in C21:H23.
The formula is C21 is:
C20+(IFERROR(IF($B21 < (INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,1)&"Output"&C$19,$A$12:$F$12,0))),INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,1)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,1)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)*LOOKUP(INDEX($A$3:$A$6,1),$E$19:$H$19,$E20:$H20),0))+
(IFERROR(IF($B21 < (INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,2)&"Output"&C$19,$A$12:$F$12,0))),INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,2)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,2)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)*LOOKUP(INDEX($A$3:$A$6,2),$E$19:$H$19,$E20:$H20),0))+
(IFERROR(IF($B21 < (INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,3)&"Output"&C$19,$A$12:$F$12,0))),INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,3)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,3)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)*LOOKUP(INDEX($A$3:$A$6,3),$E$19:$H$19,$E20:$H20),0))+
(IFERROR(IF($B21 < (INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,4)&"Output"&C$19,$A$12:$F$12,0))),INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,4)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,4)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)*LOOKUP(INDEX($A$3:$A$6,4),$E$19:$H$19,$E20:$H20),0))
There's a large repeating section (differences in bold), that I think can be replaced by something more space-efficient.
What the formula does:
- For every Factory Type, first check to see if Hours are high enough for Overtime Output.
- If yes, there's a specific overtime formula that needs to be run to calculate the Output for that Factory.
- If Hours are low enough (to not activate Overtime), then RegularOutput is used with a specific regular formula to calculate output.
- Then multiply that output by the number of that specific Factory.
- Finally, all the outputs from all Factories are summed together (and the amount from the previous day is added).
NOTES:
- The tables I am using are far larger than these, which makes it necessary to shorten the formulas.
- The formulas to calculate output depending if OvertimeActivationInHours is activated, are very different. I used simple placeholders above.
- The formula is complicated by the fact that there are factories that make factories. So for the example of "FactoryThatMakesNutFactory" we need to take into account the Nut Factories it makes (FactoryThatMakesNutFactoryOutputNutFactory), PLUS the Nuts generated by those new Nut Factories (FactoryThatMakesNutFactoryOutputNut).
- The values in "MATERIALS MADE BY EACH FACTORY" are used by "FACTORY DETAILS" table, which is why some of those values don't appear in the formula.
Any suggestions on how to condense this formula?