A client has sent a csv file that I have saved as both excel and csv. I want to start manipulating the data, but simple formulas are not responding.
I am trying to input a very simple formula in B3 to get a running balance. A3+B2-C2
I get an error value, and I have worked out that its down to B2 being empty. If I put a zero in the cell, it fixes that line. I have reformatted all cells to number, but this hasnt worked
Does anyone know how to get the sheet to default back to the norm of seeing an empty cell as NIL?
You mean that if there is no number in a particular cell then the formula is returning an error message, but if you put a 0 in the cell then you get a correct result?
The issue is that a totally empty (null) cell is not numeric so Excel isn't able to process them,
Try this
Enter the number 1 in an unused cell.
Right click the cell and copy
Highlight the range of cells that you need fixed
right click and choose Paste Special
In the Operation dialogue select multiply
press ok
any field that previously had a value (numeric or text) will remain with the values as previous
any null call will now contain a zero
all of your Excel functions will work again
sorted,
kind regards,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Just because you cannot see what is there does not mean to say that it is empty.
Do the multiply by 1 that I did and if it doesn't turn to zero then there was something recognisable in it already.
The only fields that will be affected by my fix are the ones that there are a problem with.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
Yes Pauline, an empty cell seems to be causing the issue. I have never seen that before. Usually Excel just recognises it as NIL
Sean, no joy I'm afraid? I can see using paste special did something, as it changed entries with .00 to match the no decimal of 1, but there's still an error message until I manually put a number in a blank cell (I have been inputting NIL, but it works with any number)
If not too confidential do you want to pm the spreadsheet over to me and I'll have a look at whats wrong with it.
I will explain the fault not just fix it as that would be no good to you for the future,
kind regards,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
I can send you a portion of it, to work on. A portion that gives no confidential information away. I can apply the remedy to the original, then.. how do I send it on here??
sorry for the delay, was just responding to the VAT question where I think that Mark and myself are agreeing with each other but somehow it feels like a disagreement?
either format is fine (I'll be using Excel 2007 on this machine).
kind regards,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
all fixed. If you check your email you should be able to apply that in future if you hit this specific problem and if that doesn't work then it would be the original sollution from above that you had null values so use the multiply by 1 option.
kindest regards,
Shaun.
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
There are clever ways to do it but as this is something that I had encountered before all I did was click on one of the cells and attempt a backspace.
If you cannot backspace then its empty. If you can backspace then there is a space in the cell.
a rather nasty one vaguelly linked to this type of error that I have encountered in the past was a space in front of the number. Excel lets you know that there is something amiss by the field being left rather than right alligned but in the case in question the user had thought that the report looked strange so had alligned everything to the right not thinking that there might be a reason that some of the fields looked strange!
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
I found if you click on the little tool tip that appears when the cell with the formula is selected and click on Show Calculation Steps, the first step that appears shows you that there is a space in the first cell, in the form of " ".
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>.
One observation is that the Sum() function will treat a space (or any text for that matter) as zero. So, instead of A3+B2-C2 you can write the formula as Sum(A3)+Sum(B2)-Sum(C2) or Sum(A3,B2)-Sum(C2). Note that you cannot write Sum(A3,B2,-C2). 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.
I hope this helps.
-- Edited by bro0010 on Sunday 23rd of June 2013 01:06:39 PM
