Forum Discussion

ejb2019's avatar
ejb2019
Copper Contributor
Oct 09, 2024

embedding value into dropdown option

Hi, I want to embed a value into a drop-down value. 

 

I am trying to calculate the total cost of purchasing a product based on the price and the frequency. I have a column with the price and a column with the frequency of times purchased per year. Finally, I have a column with the total cost per year. For example, the price $20, purchased 2 times per year $40 for the annual cost. However, I want to select the frequency based on a drop-down box and have that be tied to a value that is inputted into the total annual cost formula. For example, the drop-down box would say quarterly, and it would be tied to the value 4 (meaning- purchasing four times per year). Selecting quarterly from the drop down frequency options would input the four into the formula. 

 

Thanks.

excel

1 Reply

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Oct 09, 2024

    ejb2019 Perhaps the example in the attached file helps you on your way. I used the SWITCH function to  switch the selection from the dropdown (frequency) with numbers that are then used in the formula.

     

    This works fine when you have a small number of options. In case it becomes more complicated, it's probably better to use a lookup table with all possible frequencies and the numbers that they represent.