Modify Rounding Rules

[This followup was posted to microsoft.public.excel and
a copy was sent to the cited author.]

I wanted to know if there was a means to modify the
rounding rules in excel (or in a column) to reflect
the following:

..n0, .n1, .n2, .n3, and .n4 all round to .n5

..n6, .n7, .n8 all round to .n9

where n is a number between 0 and 9

The purpose is to generate retail prices from a calculated
column that usually contains a value like n.nnn

Any assistance would be greatly appreciated.

ND/Ben

Nick,

You can use a separate column to calculate the value you need.

=ROUNDUP(A2*2,1)/2

Or maybe it just kills weeds. Don't remember.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"NickDangr" <[email protected]> wrote in message
news:[email protected]...
> [This followup was posted to microsoft.public.excel and
> a copy was sent to the cited author.]
>
> I wanted to know if there was a means to modify the
> rounding rules in excel (or in a column) to reflect
> the following:
>
> .n0, .n1, .n2, .n3, and .n4 all round to .n5
>
> .n6, .n7, .n8 all round to .n9
>
> where n is a number between 0 and 9
>
> The purpose is to generate retail prices from a calculated
> column that usually contains a value like n.nnn
>
> Any assistance would be greatly appreciated.
>
> ND/Ben

Nick,

For a number in cell A1:

=ROUNDUP((A1+0.01)*20,0)/20-IF(MOD(ROUND(A1,2),0.05)<>
MOD(ROUND(A1,2),0.1),IF(ROUND(MOD(ROUND(A1,2),0.05),2)=0,0.05,0.01),0)

All on one line, so remove any line breaks. The formula can be copied down
to match your price data.

HTH,
Bernie
MS Excel MVP

"NickDangr" <[email protected]> wrote in message
news:[email protected]...
> [This followup was posted to microsoft.public.excel and
> a copy was sent to the cited author.]
>
> I wanted to know if there was a means to modify the
> rounding rules in excel (or in a column) to reflect
> the following:
>
> .n0, .n1, .n2, .n3, and .n4 all round to .n5
>
> .n6, .n7, .n8 all round to .n9
>
> where n is a number between 0 and 9
>
> The purpose is to generate retail prices from a calculated
> column that usually contains a value like n.nnn
>
> Any assistance would be greatly appreciated.
>
> ND/Ben

obscure <vbg>

oh, and it doesn't seem to do what the OP wanted either

1.n0 rounds to 1.n0 not 1.n5
and
1.n6 etc round to 1.1 not 1.n9

..... btw i'm stuck for ideas to

Cheers
JulieD


"Earl Kiosterud" <[email protected]> wrote in message
news:%[email protected]...
> Nick,
>
> You can use a separate column to calculate the value you need.
>
> =ROUNDUP(A2*2,1)/2
>
> Or maybe it just kills weeds. Don't remember.
>
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "NickDangr" <[email protected]> wrote in message
> news:[email protected]...
>> [This followup was posted to microsoft.public.excel and
>> a copy was sent to the cited author.]
>>
>> I wanted to know if there was a means to modify the
>> rounding rules in excel (or in a column) to reflect
>> the following:
>>
>> .n0, .n1, .n2, .n3, and .n4 all round to .n5
>>
>> .n6, .n7, .n8 all round to .n9
>>
>> where n is a number between 0 and 9
>>
>> The purpose is to generate retail prices from a calculated
>> column that usually contains a value like n.nnn
>>
>> Any assistance would be greatly appreciated.
>>
>> ND/Ben
>
>

Earl,

That doesn't do what the OP wanted. Fails on numbers that round to 0, 6, 7,
8, or 9 in the hundredths place.

Bernie
MS Excel MVP

"Earl Kiosterud" <[email protected]> wrote in message
news:%[email protected]...
> Nick,
>
> You can use a separate column to calculate the value you need.
>
> =ROUNDUP(A2*2,1)/2
>
> Or maybe it just kills weeds. Don't remember.
>
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "NickDangr" <[email protected]> wrote in message
> news:[email protected]...
> > [This followup was posted to microsoft.public.excel and
> > a copy was sent to the cited author.]
> >
> > I wanted to know if there was a means to modify the
> > rounding rules in excel (or in a column) to reflect
> > the following:
> >
> > .n0, .n1, .n2, .n3, and .n4 all round to .n5
> >
> > .n6, .n7, .n8 all round to .n9
> >
> > where n is a number between 0 and 9
> >
> > The purpose is to generate retail prices from a calculated
> > column that usually contains a value like n.nnn
> >
> > Any assistance would be greatly appreciated.
> >
> > ND/Ben
>
>

Nick,

I missed the bit about how your prices have 3 digits after the decimal. To
account for that, you could change the formula to

=ROUNDUP((ROUND(A1,2)+0.01)*20,0)/20-IF(MOD(ROUND(A1,2),0.05)<>MOD(ROUND(A1,
2),0.1),IF(ROUND(MOD(ROUND(A1,2),0.05),2)=0,0.05,0.01),0)

to account for that, but you really need to decide what your rounding rules
are: for example, what should 1.193 round to? 1.19, or 1.25?

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Nick,
>
> For a number in cell A1:
>
> =ROUNDUP((A1+0.01)*20,0)/20-IF(MOD(ROUND(A1,2),0.05)<>
> MOD(ROUND(A1,2),0.1),IF(ROUND(MOD(ROUND(A1,2),0.05),2)=0,0.05,0.01),0)
>
> All on one line, so remove any line breaks. The formula can be copied down
> to match your price data.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "NickDangr" <[email protected]> wrote in message
> news:[email protected]...
> > [This followup was posted to microsoft.public.excel and
> > a copy was sent to the cited author.]
> >
> > I wanted to know if there was a means to modify the
> > rounding rules in excel (or in a column) to reflect
> > the following:
> >
> > .n0, .n1, .n2, .n3, and .n4 all round to .n5
> >
> > .n6, .n7, .n8 all round to .n9
> >
> > where n is a number between 0 and 9
> >
> > The purpose is to generate retail prices from a calculated
> > column that usually contains a value like n.nnn
> >
> > Any assistance would be greatly appreciated.
> >
> > ND/Ben
>
>

Nick,

=TRUNC(F1,1)+IF(MOD(F1*10,1)>0.5,0.09,0.05)

Seems to work with the limited testing that I have done.

HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"NickDangr" <[email protected]> wrote in message
news:[email protected]...
> [This followup was posted to microsoft.public.excel and
> a copy was sent to the cited author.]
>
> I wanted to know if there was a means to modify the
> rounding rules in excel (or in a column) to reflect
> the following:
>
> .n0, .n1, .n2, .n3, and .n4 all round to .n5
>
> .n6, .n7, .n8 all round to .n9
>
> where n is a number between 0 and 9
>
> The purpose is to generate retail prices from a calculated
> column that usually contains a value like n.nnn
>
> Any assistance would be greatly appreciated.
>
> ND/Ben

The change in rounding is only to affect hundredths.

So...
3.527 rounds to 3.55, 3.569 rounds to 3.59...



[This followup was posted to microsoft.public.excel and a copy was sent
to the cited author.]

In article <#[email protected]>, "Bernie Deitrick"
<deitbe @ consumer dot org> says...
> Nick,
>
> I missed the bit about how your prices have 3 digits after the decimal. To
> account for that, you could change the formula to
>
> =ROUNDUP((ROUND(A1,2)+0.01)*20,0)/20-IF(MOD(ROUND(A1,2),0.05)<>MOD(ROUND(A1,
> 2),0.1),IF(ROUND(MOD(ROUND(A1,2),0.05),2)=0,0.05,0.01),0)
>
> to account for that, but you really need to decide what your rounding rules
> are: for example, what should 1.193 round to? 1.19, or 1.25?
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
> > Nick,
> >
> > For a number in cell A1:
> >
> > =ROUNDUP((A1+0.01)*20,0)/20-IF(MOD(ROUND(A1,2),0.05)<>
> > MOD(ROUND(A1,2),0.1),IF(ROUND(MOD(ROUND(A1,2),0.05),2)=0,0.05,0.01),0)
> >
> > All on one line, so remove any line breaks. The formula can be copied down
> > to match your price data.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "NickDangr" <[email protected]> wrote in message
> > news:[email protected]...
> > > [This followup was posted to microsoft.public.excel and
> > > a copy was sent to the cited author.]
> > >
> > > I wanted to know if there was a means to modify the
> > > rounding rules in excel (or in a column) to reflect
> > > the following:
> > >
> > > .n0, .n1, .n2, .n3, and .n4 all round to .n5
> > >
> > > .n6, .n7, .n8 all round to .n9
> > >
> > > where n is a number between 0 and 9
> > >
> > > The purpose is to generate retail prices from a calculated
> > > column that usually contains a value like n.nnn
> > >
> > > Any assistance would be greatly appreciated.
> > >
> > > ND/Ben
> >
> >
>
>
>

Nick,

Yes, but does 1.193 round to 1.19, or does it roundup to 1.20 and then to
1.25? And does 1.196 round to 1.20 and then to 1.25, or does it round down
to 1.19?

HTH,
Bernie
MS Excel MVP

"NickDangr" <[email protected]> wrote in message
news:[email protected]...
> The change in rounding is only to affect hundredths.
>
> So...
> 3.527 rounds to 3.55, 3.569 rounds to 3.59...
>
>
>
> [This followup was posted to microsoft.public.excel and a copy was sent
> to the cited author.]
>
> In article <#[email protected]>, "Bernie Deitrick"
> <deitbe @ consumer dot org> says...
> > Nick,
> >
> > I missed the bit about how your prices have 3 digits after the decimal.
To
> > account for that, you could change the formula to
> >
> >
=ROUNDUP((ROUND(A1,2)+0.01)*20,0)/20-IF(MOD(ROUND(A1,2),0.05)<>MOD(ROUND(A1,
> > 2),0.1),IF(ROUND(MOD(ROUND(A1,2),0.05),2)=0,0.05,0.01),0)
> >
> > to account for that, but you really need to decide what your rounding
rules
> > are: for example, what should 1.193 round to? 1.19, or 1.25?
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:[email protected]...
> > > Nick,
> > >
> > > For a number in cell A1:
> > >
> > > =ROUNDUP((A1+0.01)*20,0)/20-IF(MOD(ROUND(A1,2),0.05)<>
> > > MOD(ROUND(A1,2),0.1),IF(ROUND(MOD(ROUND(A1,2),0.05),2)=0,0.05,0.01),0)
> > >
> > > All on one line, so remove any line breaks. The formula can be copied
down
> > > to match your price data.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > > "NickDangr" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > [This followup was posted to microsoft.public.excel and
> > > > a copy was sent to the cited author.]
> > > >
> > > > I wanted to know if there was a means to modify the
> > > > rounding rules in excel (or in a column) to reflect
> > > > the following:
> > > >
> > > > .n0, .n1, .n2, .n3, and .n4 all round to .n5
> > > >
> > > > .n6, .n7, .n8 all round to .n9
> > > >
> > > > where n is a number between 0 and 9
> > > >
> > > > The purpose is to generate retail prices from a calculated
> > > > column that usually contains a value like n.nnn
> > > >
> > > > Any assistance would be greatly appreciated.
> > > >
> > > > ND/Ben
> > >
> > >
> >
> >
> >

I see what you're asking. 1.193 would round to 1.19 - which is already
a 9 in the 100ths position - no rounding necessary. 1.196 would round
up to a 1.20 which would become 1.25 if the rounding rules stated
initially were followed.



In article <[email protected]>, "Bernie Deitrick"
<deitbe @ consumer dot org> says...
> Nick,
>
> Yes, but does 1.193 round to 1.19, or does it roundup to 1.20 and then to
> 1.25? And does 1.196 round to 1.20 and then to 1.25, or does it round down
> to 1.19?
>
> HTH,
> Bernie
> MS Excel MVP
>
> "NickDangr" <[email protected]> wrote in message
> news:[email protected]...
> > The change in rounding is only to affect hundredths.
> >
> > So...
> > 3.527 rounds to 3.55, 3.569 rounds to 3.59...
> >
> >
> >
> > [This followup was posted to microsoft.public.excel and a copy was sent
> > to the cited author.]
> >
> > In article <#[email protected]>, "Bernie Deitrick"
> > <deitbe @ consumer dot org> says...
> > > Nick,
> > >
> > > I missed the bit about how your prices have 3 digits after the decimal.
> To
> > > account for that, you could change the formula to
> > >
> > >
> =ROUNDUP((ROUND(A1,2)+0.01)*20,0)/20-IF(MOD(ROUND(A1,2),0.05)<>MOD(ROUND(A1,
> > > 2),0.1),IF(ROUND(MOD(ROUND(A1,2),0.05),2)=0,0.05,0.01),0)
> > >
> > > to account for that, but you really need to decide what your rounding
> rules
> > > are: for example, what should 1.193 round to? 1.19, or 1.25?
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > > news:[email protected]...
> > > > Nick,
> > > >
> > > > For a number in cell A1:
> > > >
> > > > =ROUNDUP((A1+0.01)*20,0)/20-IF(MOD(ROUND(A1,2),0.05)<>
> > > > MOD(ROUND(A1,2),0.1),IF(ROUND(MOD(ROUND(A1,2),0.05),2)=0,0.05,0.01),0)
> > > >
> > > > All on one line, so remove any line breaks. The formula can be copied
> down
> > > > to match your price data.
> > > >
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
> > > >
> > > > "NickDangr" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > [This followup was posted to microsoft.public.excel and
> > > > > a copy was sent to the cited author.]
> > > > >
> > > > > I wanted to know if there was a means to modify the
> > > > > rounding rules in excel (or in a column) to reflect
> > > > > the following:
> > > > >
> > > > > .n0, .n1, .n2, .n3, and .n4 all round to .n5
> > > > >
> > > > > .n6, .n7, .n8 all round to .n9
> > > > >
> > > > > where n is a number between 0 and 9
> > > > >
> > > > > The purpose is to generate retail prices from a calculated
> > > > > column that usually contains a value like n.nnn
> > > > >
> > > > > Any assistance would be greatly appreciated.
> > > > >
> > > > > ND/Ben
> > > >
> > > >
> > >
> > >
> > >
>
>
>

I'll give this a shot - thanks!

In article <[email protected]>,
[email protected] says...
> Nick,
>
> =TRUNC(F1,1)+IF(MOD(F1*10,1)>0.5,0.09,0.05)
>
> Seems to work with the limited testing that I have done.
>
> HTH
>
> Sandy
>
>

Sandy,

I tried this out, and it works in most cases, except the one
Bernie D. stated in a previous reply... 1.196 becomes 1.19, 1.193
becomes 1.19... if we followed the rule originally set up by the
owners, 1.196 would eventually become 1.25... perhaps I can change their
rules. :-)

Your insights and help are much appreciated, just the same.

Regards,

ND/Ben

In article <[email protected]>,
[email protected] says...
> Nick,
>
> =TRUNC(F1,1)+IF(MOD(F1*10,1)>0.5,0.09,0.05)
>
> Seems to work with the limited testing that I have done.
>
> HTH
>
> Sandy
>
>

I got a lot of good responses here.. experimented around a bit and
here's one unelegant solution that seems to accomplish that for which I
was striving.

Sandy Mann suggested I use a formula like this :

=TRUNC(B1,1)+IF(MOD(B1*10,1)>0.5,0.09,0.05)

It worked in most cases, except for one stated earlier - Bernie Deitrick
brought up the fact that 1.196 would normally be rounded to 1.2(0) which
would become, if the below rules were applied, 1.25.

So I brought a 2nd column into it.

=ROUND(C1,2)

So in Cell A1 I have the formula Sandy suggested, with slight
modification as to the cell to which it referred. In cell A2, I have
the round formula, referencing C1.

In C1, if I place 1.196, cell A1 reflects 1.25 - which seems to agree
with the rules mentioned below.

I tested this over a range of 1.011 through 2.000 incrementing by .001 -
works just right.

Thanks all!

ND/Ben

In article <[email protected]>,
[email protected] says...
> [This followup was posted to microsoft.public.excel and
> a copy was sent to the cited author.]
>
> I wanted to know if there was a means to modify the
> rounding rules in excel (or in a column) to reflect
> the following:
>
> .n0, .n1, .n2, .n3, and .n4 all round to .n5
>
> .n6, .n7, .n8 all round to .n9
>
> where n is a number between 0 and 9
>
> The purpose is to generate retail prices from a calculated
> column that usually contains a value like n.nnn
>
> Any assistance would be greatly appreciated.
>
> ND/Ben
>