wasnt this what the did in office space? On Thu, May 30, 2019 at 8:39 PM Ken Hohhof <[email protected]> wrote:
> 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]> *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] > > *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] 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] > > *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]> 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] > http://af.afmug.com/mailman/listinfo/af_af.afmug.com > > > > > > -- > > Lewis Bergman > > 325-439-0533 Cell > > > > ------------------------------ > > -- > AF mailing list > [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 > ------------------------------ > > -- > AF mailing list > [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 >
-- AF mailing list [email protected] http://af.afmug.com/mailman/listinfo/af_af.afmug.com
