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

Copper Contributor

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

5 Replies

@ElliotDT 

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

 

Substitute the appropriate cell references.

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.

@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"
    )
)
Hi,
Thank you for your response,
I have attached a sample sheet below.
With the IF function, how do you write it as IF Blank and then IF Coil?

https://1drv.ms/x/s!AqQ4oTh829BSvFKs-FNmp7nqpIaI?e=YMjfHn

Cheers

@ElliotDT 

In K5:

=H5/F5*IF(D5="Blank", 1, E5)-J5

Fill down.