Function Problems

Run Different Excel Calculations Based on a Drop-Down Selection

Amos GikundaAmos Gikunda Nov 18, 2026 870 views

Question details

Use a drop-down selection to determine which calculation a spreadsheet performs.

Run Different Excel Calculations Based on a Drop-Down Selection
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to select a numeric option from a drop-down list and calculate a result from another cell.
Observed behavior
The formula should apply the calculation associated with the selected option.
Before you start

Choose distinct values for each drop-down option and decide which calculation each value should trigger. In this example, A1 holds the selection and B1 holds the amount.

Solution 1Recommended

Use a Drop-Down List with a Nested IF Formula

Use this method to apply one of two calculations based on a numeric selection.

1
Create the list

Select cell A1, open Data > Data Validation, and choose List under Allow.

2
Enter the options

In the Source box, enter 1,2 and click OK. Select either 1 or 2 from the drop-down in A1.

3
Enter an amount

Type the amount to calculate in cell B1.

4
Add the formula

In C1, enter =IF(A1=1,B1*10%,IF(A1=2,B1*15%,0)). Option 1 calculates 10% of B1, option 2 calculates 15%, and any other value returns 0.

5
Test both choices

Use the drop-down in A1 to switch between 1 and 2 and check the result in C1. Replace the percentages or calculations in the formula with the ones you need.

Use a Drop-Down List with a Nested IF Formula
Equivalent numeric values: Excel treats 10% and 0.1 as the same numeric value, so a formula cannot distinguish them as numeric selections. Use distinct text labels or separate identifiers if they must trigger different calculations.
Calculate from a selection

Create Drop-Down Calculations in WPS Spreadsheet

Use data validation and an IF formula in WPS Spreadsheet to calculate different results from a drop-down choice.

  1. 1. Open a spreadsheet: Open WPS Spreadsheet and create a workbook or open an Excel file.
  2. 2. Add the drop-down: Select A1, open Data > Data Validation, choose List, and enter 1,2 as the source.
  3. 3. Enter an amount and formula: Type an amount in B1, then enter =IF(A1=1,B1*10%,IF(A1=2,B1*15%,0)) in C1.
  4. 4. Check the result: Choose 1 or 2 from the drop-down in A1 and verify the corresponding result in C1.
Apply different calculations based on a selected list valueOpen and save Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and data validation controlsWork in a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can I use text labels instead of numbers in the drop-down?

Yes. Enter labels such as Standard and Premium in the Data Validation list, then use a formula such as =IF(A1="Standard",B1*10%,IF(A1="Premium",B1*15%,0)).

Why can't Excel treat 10% and 0.1 as different choices?

Both represent the same numeric value in Excel, regardless of how they are displayed. Use distinct text labels or identifiers when the choices need different formulas.

How do I add another calculation to the formula?

Add another value to the Data Validation list in A1, then add another IF condition before the final 0 in the formula. For many options, consider IFS or SWITCH if your Excel version supports them.

What happens if A1 is blank or contains an unexpected value?

The example formula returns 0 because neither condition matches. Replace the final 0 with another result, such as "Select an option", if you prefer a message.