dash causing #VALUE error in Excel

[jmnekrkt]

My customer is submitting a spreadsheet formatted to show a dash if the value of the cell is zero. When I link to that cell for further calculation, I receive the #VALUE error. I know I can reformat the sheet or I can check for the dash or value (but that makes a really long formula). Is there any other way to get Excel to read the dash as a zero?

Thank you

 

[Dreamboat]

Not clear whether it's a formula or a format that results in a dash...

Click on one of the cells.
What is in the formula bar? Zero or a dash or a formula?

If you're trying to do a division on a zero, you'll get errors.

To cover up errors, you can do something like:

=if(iserr(MyFormula),"WhatToDisplayInsteadOfTheError",MyFormula)

To be clear: Formulas don't work on FORMATS. They work on VALUES. The only sure way to see the value is by the formula bar, and NOT by looking at the cell.

Hope this helps.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[jmnekrkt]

the are using a custom format to display a zero as a dash. my sheet directly links to the cell with the zero value but is reading the formatted dash instead of treating the value as zero. I am actually linking to several cells in a sum formula so the iserr or if("-&quot formulas are extremely long. I was hoping there was a command or conversion that would tell the formula to treat the dash as zero

 

[Dreamboat]

If they're formatting to display a dash, and you SEE a dash, then you must write your formula as if the value were zero.

Send your spreadsheet if you like. Highlight the cell where you need the formula and tell me what you're trying to do. Anne@TheOfficeExperts.com

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[jmnekrkt]

discovered that if i write the formula as =sum(a1,a2) instead of =a1 + a2, it will correctly add all cells and not result in #VALUE error. All other math functions do not produce error...