I have researched this and tried many suggested solutions that I found but nothing seems to work.
I have a spreadsheet with 12 pages labeled Jan to Dec.
I am trying to keep a running total of an item each month (ie Year To Date). For each month there may or may not be a value entered in the item cell. If there has been no entry since the start of Jan I want the total cell to be blank until there is an entry.
The item “Entry” cell is N54 and the “Total” YTD cell is O54 in each of the 12 sheets.
I want to add the “Entry” cell from the current month to “Total” cell of the previous month.
I have tried many ways to accomplish this but my current method is as follows;
Since Jan is the start of the year there is no previous entry I used =IF(N54>0,N54,"")
For the rest of the year I have =IF (AND($Jan.O54="", N54=""),"",$Jan.O54+N54) with of course “Jan” being replaced with “Feb” and then “Mar” etc as the year proceeds.
My problem is if the previous month “Total” has no value I get #VALUE!.
I understand that “” is supposed to equate to “0” but it does not seem to be so. I have tried different formulas but nothing seems to work. I have also tried " " and {} but that did not work either. It seems if I try to add the current “Entry” cell to the previous “Total” cell that does not contain an entered value and is blank except for the formula the error occurs.
I hope my explanation of my problem is understandable and any suggestions that would get this to work would be appreciated. I am using Version: 6.1.2.1 (x64).
Posting questions as wiki doesn’t make sense.
You posted formulas containing the comma as parameter delimiter. This only works locale dependent. Please consider this thread and prefer the semicolon delimiting the parameters of functions. This works locale independent.
@Lupp, the issue of comma/semicolon as separator is raised on Ask many times.
In my en-GB installation under Tools>Options>LibreOffice Calc>Formula, the separators are defined as , , ; respectively. Pressing ‘Reset Separator Settings’ does not change them (i.e., they are not “internationalized”).
The upshot is that even if I enter a formula using ; as a separator, upon accepting the formula (pressing Enter or leaving the cell) they are immediately converted to ,…
…When they are subsequently copied and pasted into a LO question, therefore, they appear as ,.
Yes, I could change all the separators to ;, but that would make writing/reading of formulae counter-intuitive for me (and, I suspect, for many en-xx language users).
I cannot tell for sure if the decimal-point (and in current silly localisations the parameter-comma then) has a majority by users. It surely has a minority by locales.
Writing in English is gravely “counter-intuitive” for me.
Global cooperation is necessary. It’s worth to take some trouble.
If “Westerners” cannot create common standards and stick to them, the next ones won’t be “en-xx” but Chinese. Sufficiently intuitive for you?
As interesting and important as this discussion on period/comma/semicolon may be how is it pertinent to answering my question on #VALUE! and solving my problem?
Is the question above your question?
In what way did my answer miss your problem?
Do you get the #Value! error for something like =SUM(K5;Sheet5.P13)?
Are you sure it isn’t just passed forward from one of the references? SUM() ignores text but nor errors!
Your answer missed because it showed a formula that worked if both cells were on the same page, mine were on different pages as I stated and I could not get the proper version of your formula that would work. I now have been given a solution, same as yours but with coding that will work on different pages.
I thank you for your input and assistance Lupp, you seem to really understand how spreadsheets work but you have to understand that there are a lot of users who are not as proficient and are just muddling through. Just because someone comments on your answer does not mean they are attacking you it may mean they do not understand as well as you do. Thanks again.
SUM() ignores text. Addition with the operator + does not and returns therefore an error if an operand is not a number. Empty text returned by a formula ist text nonetheless. Only blank cells are treated with the value 0. If O54+N54 returns #VALUE! due to at least one of the operands being text, SUM(N54:O54) or SUM(N54;O54) would return what you want.
===Edit1 2018-11-26 21:00 CET===
Any otherwise correct arithmetic expression or function referencing cells or ranges containing errors will pass on an error. Also SUM() will do so because an error not is text which would be ignored.
Concerning functions there are a few important exceptions. These have in common to expressly address the possibility of an error:
ISERROR(), ISNA(), ISNUMBER(), ISTEXT(), ISBLANK()
Or to obviouly not depend on valid results:
COLUMN(), ROW(), SHEET() and the plural forms
Or to be specifically made for handling errors:
IFNA(p1; p2), IFERROR(p1; p2) which both are prepared to cope with an error for p1, but will pass on an error coming via p2.
I received the following from Lupp but since my additions are not side by side but on different sheets I am not sure how to implement it. Every iteration I tried give and error or does not add correctly.
SUM() ignores text. Addition with the operator does not and returns therefore an error if an operand is not a number. If O54+N54 returns #VALUE! due to at least one of the operands being text, SUM(N54:O54) or SUM(N54;O54) would return what you want.
As to posting as a Wiki, I did not understand what it meant and could find no way to remove it. Thanks for pointing out my error.
If cells are on different sheets, you need to add sheet name to each cell’s reference, for example SUM($Jan.O54;$Feb.N54)
Thank you SM_Riga, I had used the sheet name in other formulas and I thought I had tried this before but it could not have been correct because this works. Maybe the semicolon is the difference? Thanks so much. I can now fix the holes in my wall where I was banging my head for the last week.Thanks again so much and thanks also to Lupp for the explanation of the difference between “SUM” and “+”.