[SOLVED] misbehaving formula

UmTheMuse · Posted: Wed Apr 25, 2012 2:24 pm Post subject: [SOLVED] misbehaving formula

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?

ozzie · Posted: Wed Apr 25, 2012 3:33 pm Post subject:

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

UmTheMuse · Posted: Thu Apr 26, 2012 9:17 am Post subject:

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.

Villeroy · Posted: Thu Apr 26, 2012 10:01 am Post subject:

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 http://forum.openoffice.org

UmTheMuse · Posted: Thu Apr 26, 2012 11:40 am Post subject:

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.

Villeroy · Posted: Thu Apr 26, 2012 11:50 am Post subject:

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 http://forum.openoffice.org

UmTheMuse · Posted: Thu Apr 26, 2012 11:59 am Post subject:

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

UmTheMuse · Posted: Fri May 04, 2012 12:47 pm Post subject:

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?

Villeroy · Posted: Sat May 05, 2012 11:25 am Post subject:

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 http://forum.openoffice.org