[SOLVED] I keep getting #VALUE! [closed]
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 (pressingEnteror 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.