Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Monday, 24 June 2013

Excel #VALUE! error in simple formula

I've come across the dreaded "space in a cell" scenario quite a few times in client Excel spreadsheets.  Some seem to think that [space]+[enter] is the same as [delete]. The trouble is that if you try to perform any arithmetic using the cell reference alone you'll get a #VALUE! error.
With the formula =A2+B2-C2 you'll get the error if you put non-numeric text in any of the three cells.

There are several different strategies I employ in these types of scenario. 

One observation is that the Sum() function will treat a space (or any text for that matter) as zero. So, instead of A2+B2-C2 you can write the formula as Sum(A2)+Sum(B2)-Sum(C2) or Sum(A2,B2)-Sum(C2). Note that you cannot write Sum(A2,B2,-C2) or a space character in cell C2 will provoke a #VALUE! error - it tries to calculate the negative of a space character before the Sum function has had a chance to turn it into a zero for arithmetic purposes. An element of error proofing is thus applied to your spreadsheets if you always wrap individual cell references with Sum().

However, you'll often come across this where it may be very time consuming to change an existing spreadsheet to employ this strategy.  In these cases I find that the Edit | Goto (F5 key) followed by Special... and then selecting the Constants radio button in combination with only the Text tick box identifies all cells with text in them.  The ones that look empty are the ones with spaces in them.  Delete the spaces and things should start working again.

Last, but not least, if there ought not to be any valid space characters in the highlighted cells, I would use search and replace.  A single space character goes in the "Find what:" field and the "Replace with:" field is left empty. 

I hope this helps.

No comments:

Post a Comment