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

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

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

Sort by » oldest newest most voted

Hallo

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

more

2

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.

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

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

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

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

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

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

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

( 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

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

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.

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

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

more

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

( 2017-03-07 11:32:55 +0200 )edit