Use Excel IF or SWITCH to Calculate Four Forecast Tiers
Question details
Calculate a forecast amount by mapping four text categories to different percentages.

- 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.
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.
Use SWITCH to map each forecast tier to a percentage
Use this concise formula when your Excel version supports SWITCH.
Select C2 and enter =B2*SWITCH(A2,"Uncommitted",0.20,"Upside",0.40,"At Risk",0.60,"Committed",0.80,0).
Press Enter, then drag the fill handle from C2 down the column to calculate the remaining forecast amounts.

Use nested IF formulas for the four forecast tiers
Use nested IF when SWITCH is unavailable or you prefer an IF-based formula.
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. Open your worksheet: Open WPS Spreadsheets and select Open to load your Excel workbook.
- 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. 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. Apply and save: Drag the fill handle from C2 down the column, then select File > Save to save the workbook.

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.




