'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Format an ISNA Formula as Accounting to Show Zero as a Dash
Question details
The user wants to format the result of an ISNA formula using Accounting style so that zero displays as a dash, but the formula is currently returning a text string instead of a number.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Using an IF and ISNA formula combination (often with VLOOKUP) to handle errors, while intending to format the numerical output with the Accounting style.
- Observed behavior
- The formula returns "0" as text. Because text values are not treated as numbers, the Accounting format fails to display the zero as a dash, and downstream calculations may encounter errors.
Ensure the cells containing your formula are set to the Accounting number format and that you have identified the exact cells returning text instead of numbers.
Remove Quotation Marks to Return a Numeric Zero
Change the text "0" to a numeric 0 in your formula so the spreadsheet recognizes it as a number and applies the Accounting format correctly.
When a number is enclosed in quotation marks in a formula, the spreadsheet evaluates it as a text string. Text strings do not respond to number formatting rules, which is why the Accounting format fails to transform the zero into a dash. Removing the quotes resolves both the formatting issue and potential calculation errors.
Click on the cell containing your IF/ISNA formula to select it.
Click into the Formula Bar at the top of the worksheet to edit the existing formula syntax.
Locate the error result value in the formula, which currently looks like "0". Delete the quotation marks so it is just 0. For example, change it from =IF(ISNA(VLOOKUP(C27,EMILY24,3,FALSE)),"0",VLOOKUP(C27,EMILY24,3,FALSE)) to =IF(ISNA(VLOOKUP(C27,EMILY24,3,FALSE)),0,VLOOKUP(C27,EMILY24,3,FALSE)).
Press Enter to save the updated formula. As long as the cell is formatted as Accounting, the zero will now automatically display as a dash.
Write and Troubleshoot Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides a powerful, intuitive environment for writing, troubleshooting, and formatting advanced formulas. It effortlessly handles IF, ISNA, and VLOOKUP functions while offering robust formatting tools.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your formulas.
- 2. Format cells as Accounting: Highlight your data range, navigate to the Home tab, and select 'Accounting' from the Number Format drop-down menu.
- 3. Update the formula: Use the formula bar to remove any quotation marks surrounding numeric fallback values in your IF functions.
Frequently Asked Questions
Why does my VLOOKUP formula return #N/A instead of a zero?
By default, VLOOKUP returns an #N/A error when it cannot find a match in the designated range. To return a zero instead, wrap your VLOOKUP in an IFERROR or IF(ISNA(...)) function and set the error value to 0.
How do I apply the Accounting format in my spreadsheet?
Select the cells you want to format, go to the Home tab, locate the Number formatting group, and select Accounting from the drop-down menu. Alternatively, you can click the dollar sign ($) icon for quick formatting.
Can I use IFERROR instead of IF and ISNA?
Yes, using IFERROR is a more modern and concise method for handling formula errors. You can simplify your formula syntax to =IFERROR(VLOOKUP(C27,EMILY24,3,FALSE), 0).
