Ask Your Question

[SOLVED] I keep getting #VALUE! [closed]

asked 2018-11-26 00:26:39 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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: (x64).

edit retag flag offensive close merge delete


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 gravatar imageLupp ( 2018-11-26 01:19:37 +0200 )edit

@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 ,...

BigRAl gravatar imageBigRAl ( 2018-11-26 12:52:56 +0200 )edit

...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).

BigRAl gravatar imageBigRAl ( 2018-11-26 12:53:11 +0200 )edit

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?

Lupp gravatar imageLupp ( 2018-11-26 14:45:53 +0200 )edit

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?

Charlie GoodDog gravatar imageCharlie GoodDog ( 2018-11-26 16:31:57 +0200 )edit

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!

Lupp gravatar imageLupp ( 2018-11-26 20:53:27 +0200 )edit

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.

Charlie GoodDog gravatar imageCharlie GoodDog ( 2018-11-26 21:34:37 +0200 )edit

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.

Charlie GoodDog gravatar imageCharlie GoodDog ( 2018-11-26 21:38:37 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-11-26 01:13:28 +0200

Lupp gravatar image

updated 2018-11-26 21:09:26 +0200

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:
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.

edit flag offensive delete link more

answered 2018-11-26 01:33:51 +0200

Charlie GoodDog gravatar image

updated 2018-11-26 16:33:55 +0200

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.

edit flag offensive delete link more


If cells are on different sheets, you need to add sheet name to each cell’s reference, for example SUM($Jan.O54;$Feb.N54)

SM_Riga gravatar imageSM_Riga ( 2018-11-26 17:40:06 +0200 )edit

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 "+".

Charlie GoodDog gravatar imageCharlie GoodDog ( 2018-11-26 21:21:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-11-26 00:26:39 +0200

Seen: 90 times

Last updated: Nov 27 '18