Modify Rounding Rules

[NickDangr]

[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 Kiosterud]

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.

 

[Bernie Deitrick]

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

 

[JulieD]

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

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

[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

 

[Bernie Deitrick]

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

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

[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

 

[Bernie Deitrick]

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

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

[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

 

[Sandy Mann]

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

 

[NickDangr]

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.]

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

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

[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

 

[Bernie Deitrick]

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

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.]

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

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

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

[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

 

[NickDangr]

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.

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

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.]

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

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

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

[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

 

[NickDangr]

I'll give this a shot - thanks!

 

[NickDangr]

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

 

[NickDangr]

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