Mathematical formula based on drop-down list

[Rossi_Seepersad]

Hi,

In one cell I have a dropdown list with 2 options (Numbers). And based on the on user choice on that dropdown list. I want a mathematical calculation to trigger using the number in the drop-down list to post in another cell. I cannot seem to figure this out because two different calculation needs to take place depending on choice. 

[#2]

@Rossi_Seepersad 

You can use IF(), IFS(), CHOOSE() or SWITCH().

[#3]

The drop-down list is dependent on another list. So the value is constantly changing. If I were to use choose and if function, I would need to create a calculation for every time a certain number is chosen. I was checking if there was another way to do this. I am not familiar with the switch function.

[#4]

@Rossi_Seepersad 

Could you provide detailed information?

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#5]

@Rossi_Seepersad 

---

@Rossi_Seepersad wrote:
If I were to use choose and if function, I would need to create a calculation for every time a certain number is chosen.

---

That is exactly what you asked for.

If you have five different calculations you have to check for five different numbers. How else would Excel know what calculation to perform.

Every function has help features inside Excel and there is the online help on microsoft.con.

[#6]

@Hans Vogelaar 

See attached. A3 populates the list in F8. I want the calculation to fire in G8 based on the user's choice in F8. If they select the whole number, it will be D8- the whole number from F8. If they select the percentage, it will be D8-(D8*F8).  

Since the values of F8 change with the choice of A3. The whole number and percentage also change. This would require me to create a formula for each scenario based on A3. Looking for an alternative way.  

[#7]

@Rossi_Seepersad 

You need to clarify something.

The formula can't be in G8 if you are referencing G8 itself in the formula. That creates a circular reference.

And there is neither a whole number nor a percentage but only text.

[#8]

yes i edit the response to F8

[#9 · Accepted solution]

Update: I was able to get the calculation to fire how I wanted it. @Detlef Lewin I formatted the two rows for the dropdown as numbers. However, I ran into the issue of excel seeing 0.1 and 10% as the same number in the calculation so it was throwing off the "if" logic. I needed the decimal row to be true in the "if" function and percentage to be false. To get around this I added extra zeroes to the decimals followed by a one and formatted the cells so it only displays two digits.

The function I used was "if" and "or". For "or" I put in all the possibilities to make the "if" statement true. So far this seems to be working as expected. If anyone sees any issues with this or knows of a simplified calculation, let me know.