Forum Discussion

MC777605's avatar
MC777605
Copper Contributor
Mar 16, 2023

Using drop down menus to multiply formulas with substituted text.

I'm trying to create a sheet that calculates carbon emissions based on mileage driven and type of car, i.e., Petrol, Diesel etc. 

 

Each type of engine and size has a different conversion factor.

 

I want to multiply the mileage driven by the conversion factor for the correct engine size and type using a drop down menu that matches the engine size and type to the conversion factor and * by the mileage.

 

I have created a drop down list with each engine type and conversion factor, and am multiplying it by the formula shown below:

 

=C4*SUBSTITUTE(F4,"Medium Diesel:","") 

 

However, I have to manually edit the text in "" if I change the value in the dropdown list.

 

Is it possible to make the formula dynamic as per the selection in the dropdown, for example, if the drop down displays the value for Small Diesel, the formula would change to: 

 

=C4*SUBSTITUTE(F4,"Small Diesel:","")

 

Thanks 

 

 

excel
Formulas and Functions

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Mar 18, 2023

    MC777605 

    One possible way to make your formula dynamic is to use the CHOOSE and MATCH functions instead of SUBSTITUTE.

    The CHOOSE function returns a value based on an index number, and the MATCH function returns the index number of a value in a list.

    For example, if you have a list of engine types and conversion factors in cells A1:B5 like this:

    Engine type

    Conversion factor

    Small Diesel

    0.12

    Medium Diesel

    0.15

    Large Diesel

    0.18

    Small Petrol

    0.14

    Medium Petrol

    0.17

    And you have a drop down list in cell F4 that allows you to select one of the engine types, then you can use this formula to multiply the mileage in C4 by the corresponding conversion factor:

    =C4*CHOOSE(MATCH(F4,A1:A5,0),B1,B2,B3,B4,B5)

    This formula will automatically adjust the conversion factor based on the selection in F4.

     

    I hope this helps you with your task. 

Resources