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

Copper Contributor

# 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

5 Replies

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

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

Substitute the appropriate cell references.

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

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.

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

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

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

Hi,
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

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

In K5:

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

Fill down.