#### Katwick

##### Active Member

**Google Sheets Recipe Creation using a Solver**

The Sorceress in my live world took a Sabbatical this week, so I'm using the Diamond Ingredients from the Beta World. I was going to get around to posting in the Beta Forum eventually, so here we go. There's a ton of discussion at US Forum - Cauldrons 103

You can always download a current copy of the Google Sheet from Katwick Cauldrons > Production. Each week, once the Diamond Ingredients are known, several relevant recipes are posted, plus anything else that's interesting.

Katwick Cauldrons runs on vanilla Google Sheets, as the front end for a Frontline Systems server farm, so you can run the Solver on a rather modest desktop computer. The spreadsheet itself is accessible from a Mobile, but it's pretty painful.

The important feature is WHAT we solve for, and how. This data set is a Low Cost, Modest Success, Military Series, with 50 Recipes. 1-25 of them are restricted to 8 Ingredient Types, no diamonds, while the other 1-25 Recipes use all 12 Ingredient Types, with diamonds as needed.

We're optimizing (Solving) using the following criteria:

- The
**AVERAGE of the Check-Boxed Effects**. Note that the Average, by itself, doesn't make the Effects the same size, it just tells the Solver which Effects should get some extra attention.- If you Check-Box a single ingredient, you're going to get an Expensive Recipe with a very high Success Rate for that particular effect.
- If you check several Effects you'll get a spread that's USUALLY a lot cheaper, but with modest success rates.
- In the following analysis we've selected all 5 Military Effects, plus Orcs, and all of them except 17_HM (Health Mercenaries), were actually at the top of the Success list somewhere along the way, and 17_HM was hanging in there at 2nd or 3rd.
- 01_C_ coins was actually first at one point, because of spillover, although it was not selected.

- The
**Factored Values**are flattened if you check the optional "Low Cost, Modest Success" box, which we did for this analysis. We're using Square of the Sum / Sum of the Squares (a Spline Fit) to prevent any one Effect from hogging the Brew Pot. - The
**Ingredient Type Totals**are always part of the solution criteria, Square of the Sum / Sum of the Squares again, so there's a rigorous methodology for how many of each Type of Ingredient will be allowed. You don't need to cap the Ingredient Types. - Witch Points are calculated after the Ingredient Mix is already established, because using the Total Number of Ingredients as a cost cap pretty much does the same thing as a Witch Point cap, and the Amounts are far more accessible for the differential equations that are used in the nonlinear analysis. The Solver usually takes less than 15 seconds.
- To keep the formulas from erroring out (most of the time) there's a +1 in numerators that might otherwise be 0, and there's a +1E-15 (a very small fudge factor) in denominators that might otherwise be 0.

Along with being pretty, this chart and the associated Witch Points breakout quite clearly illustrate that using 12 Ingredient Types allows us to use fewer of each Ingredient Type, 12/8 is half again more Ingredient Types, which dramatically reduces how many Witch Points are required. The end game players may be able to throw Witch Points at the Brew Pot, but early and mid-game players have to be very careful. _____________________________ | _______________________________________________________________________ |

When we're only using 8 Ingredient Types, 17_HM gets nothing at all. | When we're using 12 Ingredient Types, 17_HM is right up there with the Big Boys. |

12,491 Witch Points if we only use 8 Ingredient Types. | 2,276 Witch Points, plus 150 Diamonds, if we use all 12 Ingredient Types. |

Let's do the math: (12,491 - 2,276 Witch Points) / (6 Diamond Ingredients) = 10,215 / 6 = 1702.5 Witch Points or else 25 Diamond. If you're a low to mid-level player, that's pretty attractive, as 1700 Witch Points per week is the approximate allotment for a player with only 10 Diplomas.

We've picked off a recipe for each of the Effects that we check-boxed. 17_HM never made it to the top of the list, but 01_C_ caught it on spillover. You can review all 50 Recipes by downloading the Spreadsheet, or by peeking at Every Military & Orcs Low Cost 21_23_31_34, which is a static webpage that Google Sheets allows us to publish.

Target | Actual | Rate | WPs | $$ | Dpl | ## | 11 | 12 | 13 | 14 | 21 | 22 | 23 | 24 | 31 | 32 | 33 | 34 | ____Date____ | Title_with_an_optional_screenshot_Link,_or_perhaps_a_HoneyDew_List. |

Multiple | 01_C_ | 5.51% | 300 | 25 | 20 | 7 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 2023-05-15 | Every Military & Orcs Low Cost 21_23_31_34 (Beta World) #07 |

Multiple | 03_SB | 6.07% | 400 | 75 | 20 | 11 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 2023-05-15 | Every Military & Orcs Low Cost 21_23_31_34 (Beta World) #11 |

Multiple | 07_ST | 5.18% | 150 | 25 | 20 | 4 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 2023-05-15 | Every Military & Orcs Low Cost 21_23_31_34 (Beta World) #04 |

Multiple | 09_OR | 5.00% | 50 | 0 | 20 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2023-05-15 | Every Military & Orcs Low Cost 21_23_31_34 (Beta World) #01 |

Multiple | 11_SM | 6.67% | 400 | 50 | 20 | 10 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 2023-05-15 | Every Military & Orcs Low Cost 21_23_31_34 (Beta World) #10 |

Multiple | 16_HT | 5.70% | 150 | 0 | 20 | 3 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 2023-05-15 | Every Military & Orcs Low Cost 21_23_31_34 (Beta World) #03 |

Last edited: