Forum Discussion
Solved
Mathematical formula based on drop-down list
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-d...
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.
- 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.
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.
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?
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.
