I think you want ROUND().
Cell formatting is just how it is displayed, it doesn’t affect the actual number. From: AF <[email protected]> On Behalf Of [email protected] Sent: Thursday, May 30, 2019 3:26 PM To: [email protected] Subject: Re: [AFMUG] OT Excel oddness But I don’t want that for anything other than money. And it is a pain to change. Too many clicks and you might forget how you had it set. I think if you select accounting or currency or really any format that allows you to set the number of decimal places, it ought to use fixed point math. From: Bill Prince Sent: Thursday, May 30, 2019 1:40 PM To: [email protected] <mailto:[email protected]> Subject: Re: [AFMUG] OT Excel oddness That's not the correct option. You need to set "Precision as Displayed" so that it will trim the rounded portion from the next calculation. bp <part15sbs{at}gmail{dot}com> On 5/30/2019 12:36 PM, [email protected] <mailto:[email protected]> wrote: I only have two types of rounding: None (everything but money) 2 decimal places (for money) When you select format, it asks you how many digits you want. I presumed that set the rounding precision. It does not. From: Bill Prince Sent: Thursday, May 30, 2019 1:20 PM To: [email protected] <mailto:[email protected]> Subject: Re: [AFMUG] OT Excel oddness You need to set the rounding precision. Read about it here <https://support.office.com/en-us/article/Set-rounding-precision-E5D707E3-07A8-4DF2-810C-218C531EB06A> . bp <part15sbs{at}gmail{dot}com> On 5/30/2019 12:17 PM, Lewis Bergman wrote: Yeah. I run into this when I do tower leases that run out to a lot of years and have annual increases. Normally it flies through fine but every once in a while someone will check and mention that every so often it is off by the rounding (dispalyed rounding). I just agree to whatever pennies they want and go on but it is annoying. On Thu, May 30, 2019 at 11:58 AM <[email protected] <mailto:[email protected]> > wrote: If you divide a number like $123.45 by 2 you get $61.73 if the format of the first cell is set to two decimal points of precision. Fine, obeys rounding rules. Now if you take the cell containing $61.73 and multiply it by 3 you get $185.18 If you take your calculator out and do that same calculation you get $185.19 Even if you set precision format to 2 decimal places, it continues to carry full floating point precision in the cell, irrespective of how it is displayed. There is an option under options>advanced for setting precision as displayed. But that will shut off full floating point calculations. Seems like they would have a third option for financial formats only. -- AF mailing list [email protected] <mailto:[email protected]> http://af.afmug.com/mailman/listinfo/af_af.afmug.com -- Lewis Bergman 325-439-0533 Cell _____ -- AF mailing list [email protected] <mailto:[email protected]> http://af.afmug.com/mailman/listinfo/af_af.afmug.com _____ -- AF mailing list [email protected] <mailto:[email protected]> http://af.afmug.com/mailman/listinfo/af_af.afmug.com
-- AF mailing list [email protected] http://af.afmug.com/mailman/listinfo/af_af.afmug.com
