That's a valid solution, but I think my complaint would be that if I
have to add a Round() function to all of my financial formulas, then it
should have been a setting you can adjust for the whole worksheet.
On 5/30/2019 9:38 PM, Ken Hohhof 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] <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
