To Do: Add tests for Vitamin Floor constraint.
If you read through this and notify me of any bugs or improvements I thank you in advance.
Good luck in Brewing.
Using Spreadsheet:
Enter ingredients in rows 28 to 44. Check relevant values. Any warnings in rows 21 to 26?
Building this spreadsheet:
Start with data: Create rows 1-3, 28-44 and 88-102
Available Sugar formulas in rows 46-60
Sugar consumption formulas in rows 71 and 72
Vitamins formulas in rows 74 and 75
Intermediate calculations in rows 77-80
Beer final characteristics: rows 6-19
Beer failures states in rows 21 to 26. The formulas here are Excel conditional formatting. If value is true it changes the color of the cell.
The first recipe is in column 'C'. Just copy/paste cell range C6:C80 to E6:E80. Keep your eyes open for any mistakes related to absolute/relative cell referencing.
Description:
Rows 1 to 3 contain yeast characteristics
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
| 1 | Y41 | |||||||||||||
| 2 | Alcohol Ceiling | Glucose Floor | Maltose Floor | Vitamin Floor | Vita Consumed | Orange | Banana | Cherry | Date | Honey | Nutmeg | Cinnamon | Grassy | Nasty |
| 3 | 351 | 1 | 68 | 185 | 0.4957 | 0.07 | 0.02 | 0.017 | 0.014 | 0.008 | 0.028 | 0.024 | 0.14 | 0.014 |
| A | B | C | |
| 6 | Alcohol Produced | =SUM(C71:C72) | |
| 7 | Color | =SUMPRODUCT(C30:C35,$C$90:$C$95)+SUMPRODUCT(C39:C44,$C$97:$C$102) | |
| 9 | Vitamins | =C74-C75 | |
| 11 | Glucose | =C54-C71 | |
| 12 | Maltose | =MAX($C$3,C68-C72+$C$3) | |
| 14 | Barley | =SUMPRODUCT(C30:C35,$E$90:$E$95)+SUMPRODUCT(C39:C44,$E$97:$E$102) | |
| 16 | Honey | =ROUNDUP(SUMPRODUCT(C30:C35,$H$90:$H$95)+SUMPRODUCT(C39:C44,$H$97:$H$102)0) | |
| 17 | Nutmeg | =ROUNDUP($K$3*C6,0) | |
| 18 | Cinnamon | =ROUNDUP($L$3*C6,0) | |
| 19 | Tannin | =ROUNDUP(SUMPRODUCT(C30:C35,$F$90:$F$95)+SUMPRODUCT(C39:C44,$F$97:$F$102),0) |
| A | B | C | |
| 21 | Cloying =(C11+(C12/2))>C17+C18+C19 | ||
| 22 | Bitter =(C11*2)+C12<C19+(C17+C18/5) | ||
| 23 | Caustic =(C11*6)+(C12*3)<C17+C18 | ||
| 24 | Grassy =(C6*M3)>100 | ||
| 25 | Moldy (No formula yet) | ||
| 26 | Nasty =(C6*N3)>100 |
| A | B | C | |
| 28 | Begin | ||
| 30 | Honey | 0 | |
| 31 | Raw Malt | 0 | |
| 32 | Light Malt | 0 | |
| 33 | Medium Malt | 0 | |
| 34 | Dark Malt | 0 | |
| 25 | Burnt Malt | 0 | |
| 37 | End | ||
| 39 | Honey | 0 | |
| 40 | Raw Malt | 0 | |
| 41 | Light Malt | 0 | |
| 42 | Medium Malt | 0 | |
| 43 | Dark Malt | 0 | |
| 44 | Burnt Malt | 0 |
| A | B | C | ||
| 46 | Glucose | |||
| 48 | Honey | =(C30*$A$90)+(C39*$A$97) | ||
| 49 | Raw Malt | =(C31*$A$91)+(C40*$A$98) | ||
| 50 | Light Malt | =(C32*$A$92)+(C41*$A$99) | ||
| 51 | Medium Malt | =(C33*$A$93)+(C42*$A$100) | ||
| 52 | Dark Malt | =(C34*$A$94)+(C43*$A$101) | ||
| 53 | Burnt Malt | =(C35*$A$95)+(C44*$A$102) | ||
| 54 | =SUM(C48:C52) | |||
| 55 | =SUMPRODUCT(C30:C35,$A$90:$A$95)+SUMPRODUCT(C39:C44,$A$97:$A$102) | Should equal C:54 |
| A | B | C | ||
| 56 | Maltose | |||
| 58 | Honey | =(C30*$B$90)+(C38*$B$96) | ||
| 59 | Raw Malt | =(C31*$B$91)+(C39*$B$97) | ||
| 60 | Light Malt | =(C32*$B$92)+(C40*$B$98) | ||
| 61 | Medium Malt | =(C33*$B$93)+(C41*$B$99) | ||
| 62 | Dark Malt | =(C34*$B$94)+(C42*$B$100) | ||
| 63 | Burnt Malt | =(C35*$B$95)+(C43*$B$101) | ||
| 64 | =SUM(C59:C63) | |||
| 65 | =SUMPRODUCT(C30:C35,$B$90:$B$95)+SUMPRODUCT(C39:C44,$B$97:$B$102) | Should equal C:64 |
| A | B | C | ||
| 67 | Available Glucose | =C54-$B$3 | Sum of all Glucose minus the Glucose Floor | |
| 68 | Available Maltose | =C64-$C$3 | Sum of all Maltose minus the Maltose Floor | |
| 69 | Available Sugars | =C67+C68 |
| 71 | Glucose Consumed | =C54-$B$3 | ||
| 72 | Maltose Consumed | =MIN($A$3-C67,C68) |
| 74 | Vitamins Produced | =ROUND(SUMPRODUCT(C30:C35,$D$90:$D$95)+SUMPRODUCT(C39:C44,$D$97:$D$102),0) | ||
| 75 | Vitamins Consumed | =ROUND(C6*$E$3,0) |
| 77 | Glucose + Maltose/2 | =(C11+(C12/2)) | ||
| 78 | Tannin+Cinnamon+Nutmeg | =C17+C18+C19 | ||
| 79 | Glucose*2+Maltose | =(C11*2)+C12 | ||
| 80 | Tannin + (Cinnamon+Nutmeg)/5 | =C19+(C17+C18/5) |
| 88 | Glucose | Maltose | Color | Vitamins | Barley | Tannin | Grassy | Honey |
| 90 | 10 | 0 | 0 | 0.835 | 0 | 0 | 0 | 0.95 |
| 91 | 1 | 5 | 1 | 10.58 | 12 | 6 | 12 | 0 |
| 92 | 2 | 10 | 1.5 | 8.33 | 6 | 3 | 1.5 | 0 |
| 93 | 2 | 10 | 3 | 5.58 | 6 | 2 | 0 | 0 |
| 94 | 2 | 10 | 6 | 4.17 | 6 | 1.5 | 0 | 0 |
| 95 | 0 | 2 | 12 | 0 | 0 | 0 | 0 | 0 |
| 97 | 10 | 0 | 0 | 4.75 | 0 | 0 | 0 | 16.665 |
| 98 | 1 | 5 | 0.17 | 63.50 | 2 | 1 | 2 | 0 |
| 99 | 2 | 10 | 0.25 | 50 | 1 | 0.5 | 0.25 | 0 |
| 100 | 2 | 10 | 0.50 | 33.50 | 1 | 0.33 | 0 | 0 |
| 101 | 2 | 10 | 1 | 25 | 1 | 0.25 | 0 | 0 |
| 102 | 0 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |