OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

#N/A error

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
squishen
Newbie
Newbie


Joined: 15 Jun 2012
Posts: 3

PostPosted: Fri Jun 15, 2012 10:30 am    Post subject: #N/A error Reply with quote

I have rather long =SUM function in Calc and all my arguments in the string seem to be correct. When I enter the string, everything seems to check out and it gives me a result in the cell. However, when I save the file, close it, then re-open it, the cell just says #N/A. In the bar up top it says =NA().
Any ideas what I'm doing wrong? Is my string too long? I've pasted it below for reference. I've verified that I have a valid value in every cell referenced.

=SUM($'2011'.J2;-SUM(E3:E5);D6;D8;-SUM(E9:E10);D11:D22;D23-2000;D24;D27:D35;-SUM(E37:E39);D40:D59;D60-2000;-SUM(E62:E64);D65:D72;D73-2500;D74:D84;D86+1053;D87:D91;-SUM(E92);D93;-SUM(E95:E97);D98:D100;-SUM(E101:E102);D103:D107;D109:D117;D118-100;D119:D123;-SUM(E125:E129);D130:D149;D151+1200;D152;-SUM(E155:E157);D158:D159;D160-8000;D161:D169)
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Fri Jun 15, 2012 9:02 pm    Post subject: Reply with quote

Hi squishen

1) There is a function =NA(), this function has no arguments, and returns the error value #N/A but how and why it should turn up out of the blue on your sheet is beyond me!
2) The documentation says that the limit to a SUM function is, "up to 30 numbers or ranges/arrays of numbers whose sum is to be calculated". Certainly the limit to that picked up by edit (F2) of the SUM is 30, but I was able create a larger (but perhaps not so variable) function that worked fine, so perhaps the documentation lags behind the programming which is not surprising.
3) Your formula worked fine according to my tests but did not give the same reaction to opening and closing.

Having said all that unhelpful stuff, on looking at your formula I did get the impression of a list of debits and credits (positive and negative) with the occasional adjustments thrown in. If there is no text in these columns could not-
Carryover from previous year plus SUM(column D) minus SUM(column E) plus/minus adjustments (perhaps 3rd column) be used? (at least for ease of reading).
Otherwise I am unable to be of any assistance - good luck.
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
squishen
Newbie
Newbie


Joined: 15 Jun 2012
Posts: 3

PostPosted: Sat Jun 16, 2012 9:33 am    Post subject: Reply with quote

Thanks for the reply, Ozzie. Yes, it's a mystery. I think I'll try the multi-column approach. Thanks for that suggestion.
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Sat Jun 16, 2012 1:57 pm    Post subject: Re: #N/A error Reply with quote

Most likely the software supports the "extended" use of the function (more parameters than the documentation says is permitted), but it is cut off somehow when saved or reloaded. I guess you're working on a structured approach now, but if you still have your original file it would be interesting to know more.

This formula should do exactly the same, but it is "within specs". Try it and report back with your findings, if you will...
=$'2011'.J2+SUM(D6;D8;D11:D24;D27:D35;D40:D60;D65:D84;D86:D91;D93;D98:D100;D103:D107;D109:D123;D130:D149;D151:D152;D158:D169)-SUM(E3:E5;E9:E10;E37:E39;E62:E64;E92;E95:E97;E101:E102;E125:E129;E155:E157)-12347

Also, what software title and version number are you on?
(Libre Office, OpenOffice.org, or other;version 3.3/3.4/other.)
Back to top
View user's profile Send private message
squishen
Newbie
Newbie


Joined: 15 Jun 2012
Posts: 3

PostPosted: Tue Jun 19, 2012 8:22 am    Post subject: Reply with quote

I think you're right about that, keme (too many parameters causing it to be cut off when saved). Your new formula indeed worked. Thanks for that!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group