Rounding of amounts

[Gerd]

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

 

[Ragdyer]

Try this:

=CEILING(A1+0.01,0.1)-0.01

 

[Sandy Mann]

Gerd,

Assuming that as you are selling you want to round up to the next x.x9,
does:

=ROUND([your price change formula]*10,0)/10+0.09

do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Gerd]

Thank you very much, RD, this works great.

Try this:

=CEILING(A1+0.01,0.1)-0.01

 

[Gerd]

Thank you very much, Sandy. Your formula works for most of my columns but
for 1. It must have something to do with the price formula that I use to
calculate my new price.

Gerd,

Assuming that as you are selling you want to round up to the next x.x9,
does:

=ROUND([your price change formula]*10,0)/10+0.09

do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

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

 

[RagDyeR]

Thanks for the feed-back.

 

[Sandy Mann]

Thank you very much, Sandy. Your formula works for most of my columns but
for 1. It must have something to do with the price formula that I use to
calculate my new price.

I think that it is more likely the dumb formula I suggested - RD's formula
is the way to go.

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Gerd]

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]

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

 

[Gerd]

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]

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!
=====================================================


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

 

[Gerd]

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]

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