You use round when you want to round. But it sounds like you want to round. Not format.
From: AF <[email protected]> On Behalf Of [email protected] Sent: Thursday, May 30, 2019 7:51 PM To: 'AnimalFarm Microwave Users Group' <[email protected]> Subject: Re: [AFMUG] OT Excel oddness I use round when I want to round. But when I cut a dollar amount in half, the result should be rounded to the nearest penny. (Which it does just fine). However if you use that result in an equation, you don’t get the number you see you get the full floating point number behind what you see so you can get some strange results. $10 * 20 = $200.03 $100,015 for a lot of product. Say it is waveguide. This represents 10kf of eliptical 18 GHz waveguide.. $100015/10000= $10.0015/foot But excel will say it is $10.00/foot if you do this formula and the format is accounting or currency. So you have a price per foot cell showing $10.00 And then you have a project where you need 20 feet. Perhaps you are selling it or showing it in a budget. Your spread sheet will show price per foot of $10 x quantity 20 = $200.03 If you show that to a customer you will get a funny look. Like everybody can see that ain’t right. So what is the price? Do you pay or get paid the hidden floating point 3 cents or not? Do you force it to just say $200? Do you round the result? From: Ken Hohhof Sent: Thursday, May 30, 2019 6:22 PM To: 'AnimalFarm Microwave Users Group' Subject: Re: [AFMUG] OT Excel oddness I think you want ROUND(). Cell formatting is just how it is displayed, it doesn’t affect the actual number. From: AF <[email protected] <mailto:[email protected]> > On Behalf Of [email protected] <mailto:[email protected]> Sent: Thursday, May 30, 2019 3:26 PM To: [email protected] <mailto:[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] <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
