Forum Discussion

ElliotDT's avatar
ElliotDT
Copper Contributor
Apr 16, 2024

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

Good Afternoon,

 

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

I am trying to create a spreadsheet which removes a lot of admin work. 

In this example I am trying to simplify working the cost of material out depending whether it is a Blank cost or Kilo/Coil cost. 

I have a drop down list create where you pick from Blank or KG. Depending on this choice I would like the Unit cost formula to change. 

 

Blank cost formula is - (Unit price / Parts per blow) - Scrap reclaim

Coil cost formula is -  (Gross weight / Parts per blow * KG Cost) - Scrap reclaim

 

Cheers

Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 16, 2024

    ElliotDT 

    IF(dropdown="Blank", UnitPrice, GrossWeight*KgPrice)/PartsPerBlow-ScrapReclaim

     

    Substitute the appropriate cell references.

  • m_tarler's avatar
    m_tarler
    Steel Contributor
    Apr 16, 2024
    You want to use an IF function. try:
    =IF(A1="KG", [gross weight]*[KG cost], [Unit price]) / [parts per blow]
    so basically the IF statement will check the [condition] and then return either of the following parts based on if the result is true or false. In your case you have [part per blow] in both cases so I pulled that out of the IF function. if [KG cost] and [Unit price] are both in the same call/column (i.e. if it is KG then that cell contains KG cost and if it is Blank then it contains Unit price) then the formula could be
    =IF(A1="KG", [gross weight], 1) * [KG cost & Unit price cell] / [parts per blow]
    if you give a sample sheet it would be easier to be more specific.
  • joelb95's avatar
    joelb95
    Brass Contributor
    Apr 16, 2024

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

Resources