Function Problems

Use Excel IF or SWITCH to Calculate Four Forecast Tiers

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Calculate a forecast amount by mapping four text categories to different percentages.

Use Excel IF or SWITCH to Calculate Four Forecast Tiers
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains forecast categories and amounts that need to be adjusted according to each category.
Observed behavior
The desired result is an amount multiplied by the percentage assigned to its text category.
Before you start

Place the forecast category in A2 and a numeric amount in B2, or adjust the cell references in the formulas. The examples use 20%, 40%, 60%, and 80% for the four tiers; replace those rates with your own if needed.

Solution 1Recommended

Use SWITCH to map each forecast tier to a percentage

Use this concise formula when your Excel version supports SWITCH.

1
Enter the formula

Select C2 and enter =B2*SWITCH(A2,"Uncommitted",0.20,"Upside",0.40,"At Risk",0.60,"Committed",0.80,0).

2
Apply it to other rows

Press Enter, then drag the fill handle from C2 down the column to calculate the remaining forecast amounts.

Use SWITCH to map each forecast tier to a percentage
Default result: The final 0 assigns a zero result to unrecognized categories, including a blank category. Check the text in column A if a result is unexpectedly zero.
WPS Spreadsheets

Calculate forecast tiers in WPS Spreadsheets

Use a nested IF formula in WPS Spreadsheets to apply a percentage to each forecast category and calculate the adjusted amount.

  1. 1. Open your worksheet: Open WPS Spreadsheets and select Open to load your Excel workbook.
  2. 2. Check the input columns: Put the forecast category in A2 and its numeric amount in B2, or adjust the references in the formula.
  3. 3. Calculate the forecast: Select C2, enter =B2*IF(A2="Uncommitted",0.20,IF(A2="Upside",0.40,IF(A2="At Risk",0.60,IF(A2="Committed",0.80,0)))), and press Enter.
  4. 4. Apply and save: Drag the fill handle from C2 down the column, then select File > Save to save the workbook.
Map four forecast tiers to separate percentages with nested IF.Open and edit Microsoft Excel XLS and XLSX files.Fill formulas down a column for multiple forecast rows.Use a familiar spreadsheet interface for free.
microsoft office alternative - wps office

Frequently Asked Questions

What happens if the forecast category is not recognized?

Both example formulas use 0 as the default percentage, so the result is zero. Check the text in A2 or change the final default value if you want a different result.

How do I return the percentage instead of the forecast amount?

Remove B2* from the start of either formula. For example, enter =SWITCH(A2,"Uncommitted",0.20,"Upside",0.40,"At Risk",0.60,"Committed",0.80,0), then format the result cell as a percentage.

Why does a category return zero when it looks correct?

Check A2 for misspellings or extra spaces. You can use TRIM(A2) in place of A2 in the formula to remove leading and trailing spaces.

What can I use if Excel does not recognize SWITCH?

Use the nested IF formula instead. SWITCH is available in newer Excel versions, including Excel 2019 and Microsoft 365.