Help on @ROUND

[Tilak]

Hi All (noob here - sorry for basic question!!!)

I have a cell (say C10) that correctly displays a rounded value using ROUND

Now - from cell C11 - I need to reference the UNrounded value in cell C10 apply a multiplier to it.

Could someone explain how I can get that UNrounded value

TIA - take care and stay safe

Tilak

[keme]

The ROUND() function will alter the calculated value, effectively stripping away the “surplus” decimals. They are lost. So, you can go about this in two ways:

• Repeat the calculation from T10 in T11, just omit the ROUND() step.
  This is sort of “going backwards”, but in some cases, such as if you have a lot of formulas already (intentionally) referencing the rounded value, it may be the best way.
• Don’t round the result in T10, but use cell format to display a rounded result.
  This is how it is usually done.

If those two options don’t cater for your requirement, give more detail. Why do you need rounded/unrounded? What are your formulas? Is the rounding in any way dynamic (depending on magnitude, user choice, or other condition)?

Edit, according to comment: Rounding thousands by format.

In that rough mockup, actual zero and “rounded to zero” appear differently, which may be desirable, or not. Other behavior can be had…

[Tilak]

Hi Keme

Thanks very much for your prompt and very clear advice.

I understand what you say - the issue was that when I say rounding - in this case we are actually rounding to 1,000’s but want the next calculation to be done on the unrounded figure. There are many rows in the actual table.

For this case I think the easiest is to have two areas - one doing the calculations and then the next displaying the rounded answers.

Thanks again

regards

Tilak

[keme]

… in this case we are actually rounding to 1,000’s …

That is also possible by way of formatting codes. Just add a trailing thousands separator after the format code. Adding sample to answer above.

[Tilak]

Hi Keme

Thanks very much for that - didn’t know - shows how noob I am - could you please give me an example of that formatting code to display say 123,456 as 123,000 — just to be clear I would also need a number like 123,567 to be displayed as 124,000

TIA

Tilak

[keme]

Trailing thousands separators in the format code will (at least for the locales I have used) reduce the rendered value by a factor of 1000. You can team the trailing separators to display millions, billions, etc.

If you still want the zeroes displayed, they can be added as a literal to the format, after the rendered number.

Also, thhe format code optionally supports up to 3 number formats for different intervals. By default they are used for positive values, negative values, and zero, respectively. The ranges can be adjusted with a condition in square brackets.

Using comma as thousands separator, you could then use this:

[>=500]# ##0,",000";[<=-500]-# ##0,",000";0, Will display “0” for all values rounded to zero

…or…

# ##0,",000";-# ##0,",000";0 Displays “rounded to zero” as “,000”, only exactly zero values as “0”

[Tilak]

wow Keme - worked perfectly - thank you so much for taking the trouble to explain so clearly - much appreciated. No doubt I’ll be back