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

[SOLVED] misbehaving formula

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


Joined: 05 Apr 2012
Posts: 33
Location: United States

PostPosted: Wed Apr 25, 2012 2:24 pm    Post subject: [SOLVED] misbehaving formula Reply with quote

I've got a few cells in a spreadsheet that contain formulas but Calc doesn't recognize them as such even though they have an equal sign in front. One of the cells started working right when I hit the sum button and then changed the cell back to the formula I actually wanted. The other ones didn't fix themselves.

What happened, and how can I fix this? Please and thank you.

On the same project, I found myself wishing that I could change the default type of copy-and-paste to paste unformatted text. Is that possible?


Last edited by UmTheMuse on Mon May 07, 2012 7:27 am; edited 3 times in total
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Wed Apr 25, 2012 3:33 pm    Post subject: Reply with quote

paste-special (shift ctrl v) gives the option of pasting without formats
as to the first question is there a space or an apostrophe showing in the input line before the formula,is the tools/options/calc/view/formulas checked?
_________________
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
UmTheMuse
General User
General User


Joined: 05 Apr 2012
Posts: 33
Location: United States

PostPosted: Thu Apr 26, 2012 9:17 am    Post subject: Reply with quote

Yeah, I knew about the paste-special option. That's what I used. But if I forgot, the system would get seriously bogged down by building tons of links with flashing icons. It's more of an annoyance than a deal-breaker, though.

As to your question, no spaces or apostrophes (I typed the formulas directly). However, I did notice after posting that the equal sign was gray, unlike the rest of the field.

While I didn't check the tool menu, that should change the behavior of all the formulas in the worksheet, right? Remember that I said that some cells worked right and others didn't.

I think that I ended up deleting the cells and then the new cells worked right or something. I would still like to know what happened, though.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Apr 26, 2012 10:01 am    Post subject: Reply with quote

Number format text inhibits all evaluation of all input.
Apply any other number format and your input will evaluate to number or formula respectively.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
UmTheMuse
General User
General User


Joined: 05 Apr 2012
Posts: 33
Location: United States

PostPosted: Thu Apr 26, 2012 11:40 am    Post subject: Reply with quote

I clicked on format cells, looked at the Numbers tab, clicked on Number category and chose a format other than General, but it still didn't work. Is this what you meant?

Btw, I'm having this issue on a brand new sheet this time. This cell has never been accessed, so this problem has nothing to do with the copy-and-paste that I mentioned earlier.

edit: I've tried the IsFormula() function on the cell and the answer returned FALSE. Also, all the failures seem to involve the COUNTA() function. For now, the lists are short enough that I can just count them if necessary, but since I'm going to have to do this several times for much longer lists, I'd rather not.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Apr 26, 2012 11:50 am    Post subject: Reply with quote

No formatting attribute, no color, no font, no border and in particular no number format will ever modify your cell values.
Your text value remains a text value and when you format a number as text your number remains a number and your formula remains a formula. This is a granted feature in all data-aware software (all spreadsheets, all databases) even if 99% of all users expect something different.

You are the one to enter, paste or import your data. By mere intention, there must not be any on-the-fly conversion feature.
Apply some format which is not text and re-enter the formula or perform a dummy edit (append a space).
Replacing regular expression .+ with & effectively re-enters all conents semi-automatically.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
UmTheMuse
General User
General User


Joined: 05 Apr 2012
Posts: 33
Location: United States

PostPosted: Thu Apr 26, 2012 11:59 am    Post subject: Reply with quote

It turns out that what you told me worked, I just needed to click outside the cell for it to update properly or something. Thanks. I knew it had to be something dumb like that Embarassed
Back to top
View user's profile Send private message
UmTheMuse
General User
General User


Joined: 05 Apr 2012
Posts: 33
Location: United States

PostPosted: Fri May 04, 2012 12:47 pm    Post subject: Reply with quote

Sorry, I'm still struggling with this. This time, in the find and replace box I put in .* for a selection of text in the search and =TRIM(&) in the replace field.

These cells are supposed to be text, but I still want the formulas to evaluate. Any ideas of what I did wrong and what I can do to make it right?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat May 05, 2012 11:25 am    Post subject: Reply with quote

Why that complicated? =TRIM(A1) can do this. Copy the formula cell as needed. Then copy&paste-special the resulting text over the original data.

Alternatively you may use regexes
^[:space:]+ to match leading spaces
[:space:]+$ to match trailing spaces
and replace them with nothing
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
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