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

Hiding Error Results

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


Joined: 01 Mar 2006
Posts: 125
Location: Chico, CA

PostPosted: Sat Mar 18, 2006 12:20 pm    Post subject: Hiding Error Results Reply with quote

I have long formulas in some of my spreadsheet cells that do a search. It is normal for them to return nothing. The problem is that in that case it returns an error. I currently use the following to hide these errors.

=IF(ISERR(LongFormula);"";LongFormula)

As you can see I have to repeat the long formula twice. Is there any other way to do this?

Number formats have formats for positive, negative, and zero. It would be nice to extend these formats to include text and have good results and error results. Maybe something like:

PositiveFormat;ZeroFormat;Negative Format;ErrorFormat

where the first 3 would be ignored for text fields.
_________________
Dennis Marks
Back to top
View user's profile Send private message Visit poster's website
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Sat Mar 18, 2006 4:24 pm    Post subject: Reply with quote

Use defined names.
  • Insert > Name > Define
  • Type LongFunction in the Name box
  • Paste your long function in the Assigned to box - taking care that you use relative and absolute references ($s in the addresses) to generalise the formula as needed.
then in the cells type =IF(ISERR(LongFormula);"";LongFormula) as you have in the post.

Note that if you always hide the error, then you can do that (use the if statement) when you define the name and then just type =HiddenErrorFunction in your cells.

Another possibility is to convert your long formula to a macro. (This will not port to other spreadsheet programs - if that is an issue)
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
denmarks
OOo Enthusiast
OOo Enthusiast


Joined: 01 Mar 2006
Posts: 125
Location: Chico, CA

PostPosted: Sun Mar 19, 2006 7:49 am    Post subject: Reply with quote

Quote:
Paste your long function in the Assigned to box - taking care that you use relative and absolute references ($s in the addresses) to generalise the formula as needed.
then in the cells type =IF(ISERR(LongFormula);"";LongFormula) as you have in the post.


I have thought about doing that but I don't understand one thing. Since the same named formula will be used in many cells, what do I put as a relative address in the "named" formula? Since the named formula is not assigned to a starting cell how can the relative addresses be maintained when used in multiple cells?/

For example if a reference of $A$1 is used there is no problem but if the address is B2 for a named formula used in A1 how would it know to adjust to D4 when used in C3. Named formulas have no base.

Does it use whatever cell is selected at the time the name is created as the base?
_________________
Dennis Marks
Back to top
View user's profile Send private message Visit poster's website
denmarks
OOo Enthusiast
OOo Enthusiast


Joined: 01 Mar 2006
Posts: 125
Location: Chico, CA

PostPosted: Sun Mar 19, 2006 8:06 am    Post subject: Reply with quote

I just answered my own question. It does use the active cell as the base for the relative cells when the name is created.
_________________
Dennis Marks
Back to top
View user's profile Send private message Visit poster's website
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Mar 19, 2006 10:15 am    Post subject: Re: Hiding Error Results Reply with quote

denmarks wrote:
I have long formulas in some of my spreadsheet cells that do a search. It is normal for them to return nothing. The problem is that in that case it returns an error. I currently use the following to hide these errors.

=IF(ISERR(LongFormula);"";LongFormula)

As you can see I have to repeat the long formula twice. Is there any other way to do this?


Still typing a reference twice, but it is not unusual to place a list of formulas in another place [even another sheet] and reference them from there. So a long formula could at least reduce to $Sheet2.A1 for example.

? I haven't tried this.

David.
Back to top
View user's profile Send private message
denmarks
OOo Enthusiast
OOo Enthusiast


Joined: 01 Mar 2006
Posts: 125
Location: Chico, CA

PostPosted: Sun Mar 19, 2006 12:10 pm    Post subject: Reply with quote

I've cut the file size in half by replacing most of the repeated formulas. Since this is a calendar, most formulas are repeated 37 times (the maximum number of cells needed for a monthly perpetual calendar). I never knew that formulas could be stored this way. The help files should make it clearer. You could even create functions as long as they could be resolved to a very long formula. The input for the functions would have to be in set places relative to the function cell.
_________________
Dennis Marks
Back to top
View user's profile Send private message Visit poster's website
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Sun Mar 19, 2006 4:17 pm    Post subject: Reply with quote

denmarks wrote:
I never knew that formulas could be stored this way.
I found out by reading one of JohnV's or Villeroy's posts (can't remember which - but thanks whoever).
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
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