Ask Your Question

Calc will not use 'rounded up/down' numbers in formulas

asked 2017-03-06 23:18:43 +0200

MDC gravatar image

The formula I am needing is based on the rounded 'up/down' number. Calc. will display the rounded number, however, it uses the original number (from before it was rounded) in the calculation of the formula being used in the cell. This ruins the calculation. Is there a way to change this?

edit retag flag offensive close merge delete


You surely did not round, but format the display to a reduced number of decimal places.
Rounding is done by functions like ROUND(Value; NumOfDecimalPlaces), ROUNDUP, ROUNDDOWN, TRUNC, INT, FLOOR, CEILING. Number formatting is done by format codes in the tab 'Numbers' when formatting cells.

Lupp gravatar imageLupp ( 2017-03-07 00:03:49 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-03-06 23:28:50 +0200

karolus gravatar image


Enable →Tools→Options→LO-Calc→calculate→[x]precision as shown

edit flag offensive delete link more



This is a dangerous option. I would not advise to use it. To reliably conrtol the working of spreadsheets use explicit rounding and keep values an formatting apart. 'Precision as shown' is always global to the document and against the grain of spreadsheets.
Using =ROUND(expression; 2) where working with currencies, but having full precision otherwise should be preferrable.

Lupp gravatar imageLupp ( 2017-03-07 00:05:27 +0200 )edit

Don't aggree … working on Spreadsheets is always "dangerous" if $User doesn't know how to work with. ( The answer of @MDC seems a pretty good example …)

Work with 2digit precision-formatting on Currencies and use approiate precision otherwise, without tons of additional ROUND(…) surrounding every calculation.

karolus gravatar imagekarolus ( 2017-03-07 10:36:53 +0200 )edit

@karolus: Our differences are probably not substantial here. Maybe there are experienced users like you who, in specific cases, prefer to use that option over rounding. I simply cannot think of a spreadsheet containg "nothing else at all". and where I have many different calculations, and many different formats (date-time, percentage, scientific, fractions probably, ...), I would necessarily need to research about all the implications of that option including intermediary reults ...

Lupp gravatar imageLupp ( 2017-03-07 14:23:56 +0200 )edit

Thanks for the comments. Looks like I "accidentally" answered instead of 'commented'. Meanwhile, could you guys please tell me how to accomplish the task I am attempting?

MDC gravatar imageMDC ( 2017-03-08 05:48:40 +0200 )edit

There is an answer in my comment to your answer!!

karolus gravatar imagekarolus ( 2017-03-08 11:01:27 +0200 )edit

@MDC: Did you guy even "accidentally" read my comment on your original question, and subsequently the help texts concerning the mentioned functions?
Making spreadsheets work as expected is not exclusively a task of contributors to a forum.

Lupp gravatar imageLupp ( 2017-03-08 11:15:51 +0200 )edit

If you are referring to this: →Tools→Options→LO-Calc→calculate→[x]precision as shown , thank you. However, I am not finding Options under tools. Could I be looking in the wrong place? I believe this is your answer...Work with 2digit precision-formatting on Currencies and use appropriate precision otherwise... but I don't know how to use it. Forgive my ignorance, I appreciate your attempt to help me. Lupp.... your sarcasm is not at all helpful.

MDC gravatar imageMDC ( 2017-03-08 23:34:55 +0200 )edit

The 'Options' under discussion here ('Preferences on the Mac) are under the main menu item 'Tools' in the English UI.
"Work with 2-digit precision-formatting on Currencies and use appropriate precision otherwise... "
There isn't precisely such an option currently. The option 'Precision as shown' also afflicts fraction formats an (!) even Boolean "subtype". There are implications and stability over versions is not guaranteed, as there is no explicit specification of the feature.

Lupp gravatar imageLupp ( 2017-03-09 00:16:46 +0200 )edit

Thank You Lupp for your assistance. I am using a Mac, and I am still not finding the 'Options' (or Preferences) under tools. I am not sure what 'UI' is, so maybe that is where I am missing it.

on another note, I "just now" saw the comment from you Karolus, =INT( G4/2000 ), I tried it and it worked perfectly. Thank you! Please tell me if you can though, what is the INT, and how could I have known this or found it, for future reference or problems? Thanks again

MDC gravatar imageMDC ( 2017-03-10 02:34:46 +0200 )edit

(Please do not write loudly.)
I haven't a 'Mac' at hand, but there surely exists a main menu item containing the 'Preferences'. Just look them through. If there was a way on the Mac to remove a main menui item or to rename it, I do not know remedy.

Lupp gravatar imageLupp ( 2017-03-10 12:23:29 +0200 )edit

answered 2017-03-07 07:17:50 +0200

MDC gravatar image

Thanks so much for the quick response. You are correct, I was 'reducing decimal places'. So, please forgive my ignorance in how to get around this software, where everything is and what it is called. I am a novice at it, I only know what I want it to do.
The issue is this: I need to cell E5 to = G4/ every 2000 that is in cell G4. So, if G4 is 3500, then cell E5 still needs to be 1, not anything more, since 2000 only goes into 3500 once. Once cell Gx is over 4000 then cell Ex can go to 2, etc.

Does that help at all?
Can you tell me how to accomplish this? Thanks in advance

edit flag offensive delete link more


Formula in E4
=INT( G4/2000 )
and copy down.

karolus gravatar imagekarolus ( 2017-03-07 11:32:55 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-03-06 23:18:43 +0200

Seen: 480 times

Last updated: Mar 07 '17