How to Combine Dollar Variance and Percentage Variance in One WPS Spreadsheets Cell
Question details
The user wants to display a dollar variance and its percentage variance together in one cell for monthly and year-over-year sales analysis.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a monthly or year-over-year sales report and needs a compact cell showing both absolute and percent change.
- Observed behavior
- A single cell should show a combined text value such as $12,345 (23.4%) instead of two separate numeric values.
Before you start, confirm which cells contain your current and prior period sales values, and check whether the prior period value can be zero so you can add IFERROR from the beginning.
Combine Dollar and Percentage Variance with a TEXT Formula in WPS Spreadsheets
Use this method when you need a quick worksheet formula that displays both variances in one cell without Power Pivot.
The formula converts the dollar variance and percentage variance to text with the TEXT function and joins them with an ampersand. The result is a text label, not a numeric value, so keep the original sales cells if you need further calculations.
Launch WPS Office, open your Excel or WPS workbook, and select the worksheet that contains current and prior period sales values.
Select the target cell, for example D2, where you want the dollar and percentage variance result to appear.
Type =TEXT(B2-C2,"$#,##0")&" ("&TEXT((B2-C2)/C2,"0.0%")&")" and press Enter. Adjust B2 and C2 to your current and prior period cells.
If the prior period value can be zero, use =IFERROR(TEXT(B2-C2,"$#,##0")&" ("&TEXT((B2-C2)/C2,"0.0%")&")","N/A") so the cell does not show a division error.
Use Helper Columns for Numeric Variance Values and Combine Them in a Display Cell
Use this method when you also need the numeric dollar and percentage variances for charts, conditional formatting, or further calculations.
Create a Power Pivot DAX Measure in Microsoft Excel
Use this method when your report is built in Microsoft Excel with Power Pivot or the Data Model and you need a reusable measure rather than a worksheet formula.
Combine Variance Results Directly in WPS Spreadsheets
WPS Spreadsheets can display the dollar variance and percentage variance in one cell with a simple TEXT and concatenation formula—no Power Pivot add-in required. It keeps monthly and year-over-year sales reporting compact and readable.
- 1. Open WPS Spreadsheets: Launch WPS Office, open your Excel or WPS workbook, and select the worksheet containing current and prior period sales.
- 2. Select the result cell: Click the cell where you want the combined variance, for example D2.
- 3. Enter the variance formula: Type =TEXT(B2-C2,"$#,##0")&" ("&TEXT((B2-C2)/C2,"0.0%")&")" and press Enter.
- 4. Copy the formula across the report: Drag the fill handle down or across to apply the formula to the rest of your monthly or year-over-year report.
Frequently Asked Questions
Can I combine dollar and percentage variance in one cell without Power Pivot?
Yes. In WPS Spreadsheets, use a formula such as =TEXT(B2-C2,"$#,##0")&" ("&TEXT((B2-C2)/C2,"0.0%")&")" to join the two values in one cell.
What should I do if the prior period value is zero?
Wrap the formula in IFERROR, for example =IFERROR(TEXT(B2-C2,"$#,##0")&" ("&TEXT((B2-C2)/C2,"0.0%")&")","N/A"), so the cell returns N/A instead of a division error.
Can I use the combined variance cell in a chart or further calculation?
No, the combined cell is text. Keep separate numeric helper columns for the dollar and percentage variances if you need to chart them or use them in other formulas.
Can I change the format so negative variances appear in parentheses?
Yes. Adjust the TEXT format code, for example use "$#,##0;($#,##0)" for the dollar variance and "0.0%;(-0.0%)" for the percentage variance.
Is this formula compatible with Microsoft Excel?
Yes. TEXT, IFERROR, and ampersand concatenation are standard formulas that work in both WPS Spreadsheets and Microsoft Excel .xlsx workbooks.
