Re: Rounding of amounts

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You're quite welcome, and thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gerd" <gerd@xxxxxxxxxxx> wrote in message news:eU4vMM%234GHA.4256@xxxxxxxxxxxxxxxxxxxxxxx
You are absolutely correct. I only displayed 2 decimals for my cost, thus, "assuming" the amount was what I saw.

And thanks for the additional formula. I guess I now have to make a decission about how I want to calculate and display my cost.

Thanks again for your excellent help.
Gerd

"RagDyeR" <ragdyer@xxxxxxxxxxxxx> wrote in message news:OD%23xRp94GHA.4504@xxxxxxxxxxxxxxxxxxxxxxx
Using your formula of:

=((($G7/16)*(1+T$4))*T$6)

AND, the numbers in your example:

G7 (cost in lbs) = $9.96, T4 (markup) = 210%, T6 (packaging size in ounces)
= 1.5

Your formula returns 2.894625
Where the total is *not* 2.89

2.894625 becomes 2.90,
Which rounds to 2.99

It's your decision what to charge in such cases as this.

What would you want to do with an amount of 2.899999
As opposed to an amount of 2.8900000

If you are willing to accept (use) *only* the first two decimal places as
the basis for your calculations, you might try something like this:

=CEILING(TRUNC(($G7/16*(1+T$4)*T$6),2)+0.01,0.1)-0.01

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"Gerd" <gerd@xxxxxxxxxxx> wrote in message
news:e4gpx574GHA.2464@xxxxxxxxxxxxxxxxxxxxxxx
Hi RD,

In one of my columns I use formula =((($G7/16)*(1+N$4))*N$6) to calculate
the selling price in ounces, my 2nd column uses formula
=CEILING(((($G7/16)*(1+T$4))*T$6)+0.01,0.1)-0.01
which calculates the rounded selling price in ounces.

If it happens that the price per ounce ends with a 9 the rounded price adds
$ .10 cents to the amount.

Here is an example:
G7 (cost in lbs) = $9.96, T4 (markup) = 210%, T6 (packaging size in ounces)
= 1.5


"RagDyer" <RagDyer@xxxxxxxxxxxxx> wrote in message
news:uVPkz624GHA.4196@xxxxxxxxxxxxxxxxxxxxxxx
No it doesn't!

Check it out again.

*THIS* is what I posted:

=CEILING(A1+0.01,0.1)-0.01

To get what you describe, you probably used this:

=CEILING(A1+0.1,0.1)-0.01

--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Gerd" <gerd@xxxxxxxxxxx> wrote in message
news:u5nx2k24GHA.3964@xxxxxxxxxxxxxxxxxxxxxxx
I got one more question for you. If my base price is already (lets say
$2.79) your calculation rule adds another $ .10 to the price making the
result $2.89 instead.

I
"RagDyeR" <ragdyer@xxxxxxxxxxxxx> wrote in message
news:%23EYHzBL4GHA.4972@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for the feed-back.
--

Regards,

RD
------------------------------------------------------------------------
----
-------------------
Please keep all correspondence within the Group, so all may benefit !
------------------------------------------------------------------------
----
-------------------

"Gerd" <gerd@xxxxxxxxxxx> wrote in message
news:OqiFgqK4GHA.508@xxxxxxxxxxxxxxxxxxxxxxx
Thank you very much, RD, this works great.

"Ragdyer" <RagDyer@xxxxxxxxxxxxx> wrote in message
news:umJoiCC4GHA.2596@xxxxxxxxxxxxxxxxxxxxxxx
Try this:

=CEILING(A1+0.01,0.1)-0.01


--
HTH,

RD

-----------------------------------------------------------------------
---
-
Please keep all correspondence within the NewsGroup, so all may benefit
!
-----------------------------------------------------------------------
---
-

"Gerd" <gerd@xxxxxxxxxxx> wrote in message
news:eaabRtB4GHA.1256@xxxxxxxxxxxxxxxxxxxxxxx
I have a small retail store and am currently working on price changes.
I
have an excel with all the calculated prices however I would like to
round
all amounts to xx.x9. The 2nd decimal should always be 9.
Is there a way to do this in Excel? I checked the help file for
anything
related to rounding but did not find any examples that would let me do
the
rounding to 9.
Thanks for any help.
Gerd















.

Relevant Pages

  • Re: Rounding of amounts
    ... I only displayed 2 decimals for my cost, thus, ... What would you want to do with an amount of 2.899999 ... the selling price in ounces, ... which calculates the rounded selling price in ounces. ...
    (microsoft.public.excel)
  • Re: Rounding of amounts
    ... Which rounds to 2.99 ... What would you want to do with an amount of 2.899999 ... which calculates the rounded selling price in ounces. ...
    (microsoft.public.excel)
  • Re: Formula to differentiate between product lines
    ... same row as the full named product in PL1. ... When someones enters WMGD3030 I would like the cell to look at PL2 determine ... Please keep all correspondence within the NewsGroup, ... C1 then looks at A1 and Index-Matches to find the price in appropriate ...
    (microsoft.public.excel.misc)
  • Re: Shoppers beware: Products shrink but prices stay the same
    ... Many major ice cream makers, hit by higher dairy costs, have shrunk ... "Downsizing is nothing but a sneaky price increase," says Edgar Dworsky, ... A jar of Hellmann's mayonnaise that was 32 ounces is now ...
    (soc.senior.issues)
  • Re: Best Foods - Hellmans Mayo
    ... The new quart jar is 30 ounces. ... I just checked my recent mayo purchase ... annoyed when a package gets smaller. ... to hide a price increase. ...
    (rec.food.cooking)