Is it possible for a formula to change depending on the choice from a drop down list

ElliotDT 

Depending upon your users and/or future plans, you are probably better off using either the choose or switch commands. Something like =let(cost_of_materials_method, *cell reference*, switch(cost_of_materials_method, "Blank Method", (unit_price/parts_per_blow), "Cost Coil Method", ((gross_weight/parts_per_blow)*kg_cost),"Invalid Costing Method")). This way you are making it explicit that your reference is being used as a choice/switch between possible alternatives rather than some sort of comparison. I did not define each variable in the let because I don't know how your data is structured, but if anything is a reference to a cell address (e.g. "A1"). you should give it a name in your let definitions. Following this technique, it will be absolutely clear what the formula inputs are.

A somewhat better choice would be to define your costing methods as a function - either within the let or using the name manager. Assuming these costing methods are used elsewhere, the name manager is the better choice, but here is a fully conceived let formula using dummy addresses (global variables in column B, item specific variables in row 2):

=LET(
    blank_method, 
        LAMBDA(
            parts_per_blow, unit_price, 
            unit_price / parts_per_blow
    ),
    cost_coil_method, 
        LAMBDA(
            parts_per_blow, kg_cost, gross_weight,
            ((gross_weight / parts_per_blow) / kg_cost)
    ),
    parts_per_blow, B2,
    unit_price, B3,
    kg_cost, B4,
    gross_weight, F2,
    method_choice, G2,
    SWITCH(
        method_choice,
        "Blank Method", blank_method(parts_per_blow, unit_price),
        "Cost Coil Method", cost_coil_method(parts_per_blow, kg_cost, gross_weight),
        "Invalid Costing Method"
    )
)