## Forum Discussion

# 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

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

Substitute the appropriate cell references.

- m_tarlerSteel ContributorYou 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.- ElliotDTCopper ContributorHi,

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

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