Rounding to price points

I want to round a series of numbers to the nearest price point of 5 or 9
pence (up or down). E.g. £12.74 would become £12.75, £12.78 would become
£12.79, £12.91 would become £12.89

Any ideas? I'm happy to use a series of calculations if there is no single
function that will do the trick.

TIA,

Chris.

HI Chris,

Not nice but the best I could do

=IF(MOD(A1,0.1)*100<5,CEILING(A1,0.05),A1-MOD(A1,0.1)+0.09)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris Wetz" <Chris [email protected]> wrote in message
news:[email protected]...
> I want to round a series of numbers to the nearest price point of 5 or 9
> pence (up or down). E.g. £12.74 would become £12.75, £12.78 would become
> £12.79, £12.91 would become £12.89
>
> Any ideas? I'm happy to use a series of calculations if there is no
single
> function that will do the trick.
>
> TIA,
>
> Chris.

Hi Bob.

Don't care whether it's nice or not!

This certainly brings the prices to the price points required, but by
rounding up. Is there any way of getting the initial number to the 'nearest'
price point?

Many thanks for your help so far!

Chris.

"Bob Phillips" wrote:

> HI Chris,
>
> Not nice but the best I could do
>
> =IF(MOD(A1,0.1)*100<5,CEILING(A1,0.05),A1-MOD(A1,0.1)+0.09)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Chris Wetz" <Chris [email protected]> wrote in message
> news:[email protected]...
> > I want to round a series of numbers to the nearest price point of 5 or 9
> > pence (up or down). E.g. £12.74 would become £12.75, £12.78 would become
> > £12.79, £12.91 would become £12.89
> >
> > Any ideas? I'm happy to use a series of calculations if there is no
> single
> > function that will do the trick.
> >
> > TIA,
> >
> > Chris.
>
>
>

In that scenario, where would 12.92 go, 12.95 or 12.89?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris Wetz" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob.
>
> Don't care whether it's nice or not!
>
> This certainly brings the prices to the price points required, but by
> rounding up. Is there any way of getting the initial number to the
'nearest'
> price point?
>
> Many thanks for your help so far!
>
> Chris.
>
> "Bob Phillips" wrote:
>
> > HI Chris,
> >
> > Not nice but the best I could do
> >
> > =IF(MOD(A1,0.1)*100<5,CEILING(A1,0.05),A1-MOD(A1,0.1)+0.09)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Chris Wetz" <Chris [email protected]> wrote in message
> > news:[email protected]...
> > > I want to round a series of numbers to the nearest price point of 5 or
9
> > > pence (up or down). E.g. £12.74 would become £12.75, £12.78 would
become
> > > £12.79, £12.91 would become £12.89
> > >
> > > Any ideas? I'm happy to use a series of calculations if there is no
> > single
> > > function that will do the trick.
> > >
> > > TIA,
> > >
> > > Chris.
> >
> >
> >

Excellent question - my Buyer says that it would be difficult to set those
types of conventions! I will therefore leave it to him to resolve.

Thanks for your help, Bob.

Chris.

"Bob Phillips" wrote:

> In that scenario, where would 12.92 go, 12.95 or 12.89?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Chris Wetz" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob.
> >
> > Don't care whether it's nice or not!
> >
> > This certainly brings the prices to the price points required, but by
> > rounding up. Is there any way of getting the initial number to the
> 'nearest'
> > price point?
> >
> > Many thanks for your help so far!
> >
> > Chris.
> >
> > "Bob Phillips" wrote:
> >
> > > HI Chris,
> > >
> > > Not nice but the best I could do
> > >
> > > =IF(MOD(A1,0.1)*100<5,CEILING(A1,0.05),A1-MOD(A1,0.1)+0.09)
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Chris Wetz" <Chris [email protected]> wrote in message
> > > news:[email protected]...
> > > > I want to round a series of numbers to the nearest price point of 5 or
> 9
> > > > pence (up or down). E.g. £12.74 would become £12.75, £12.78 would
> become
> > > > £12.79, £12.91 would become £12.89
> > > >
> > > > Any ideas? I'm happy to use a series of calculations if there is no
> > > single
> > > > function that will do the trick.
> > > >
> > > > TIA,
> > > >
> > > > Chris.
> > >
> > >
> > >
>
>
>

It gets worse. The same problem applies to ending with 7 as to ending with
2. so if it ends with either of those, I don't change it. Otherwise

=IF(OR(RIGHT(A1,1)="2",RIGHT(A1,1)="7"),A1,IF(MOD(A1,0.1)*100<2,INT(A1*10)/1
0-0.01,IF(MOD(A1,0.1)*100<7,ROUND(A1*20,0)/20,A1-MOD(A1,0.1)+0.09)))

Enjoy :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris Wetz" <[email protected]> wrote in message
news:[email protected]...
> Excellent question - my Buyer says that it would be difficult to set those
> types of conventions! I will therefore leave it to him to resolve.
>
> Thanks for your help, Bob.
>
> Chris.
>
> "Bob Phillips" wrote:
>
> > In that scenario, where would 12.92 go, 12.95 or 12.89?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Chris Wetz" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob.
> > >
> > > Don't care whether it's nice or not!
> > >
> > > This certainly brings the prices to the price points required, but by
> > > rounding up. Is there any way of getting the initial number to the
> > 'nearest'
> > > price point?
> > >
> > > Many thanks for your help so far!
> > >
> > > Chris.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > HI Chris,
> > > >
> > > > Not nice but the best I could do
> > > >
> > > > =IF(MOD(A1,0.1)*100<5,CEILING(A1,0.05),A1-MOD(A1,0.1)+0.09)
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Chris Wetz" <Chris [email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I want to round a series of numbers to the nearest price point of
5 or
> > 9
> > > > > pence (up or down). E.g. £12.74 would become £12.75, £12.78 would
> > become
> > > > > £12.79, £12.91 would become £12.89
> > > > >
> > > > > Any ideas? I'm happy to use a series of calculations if there is
no
> > > > single
> > > > > function that will do the trick.
> > > > >
> > > > > TIA,
> > > > >
> > > > > Chris.
> > > >
> > > >
> > > >
> >
> >
> >

On Tue, 1 Mar 2005 01:11:03 -0800, "Chris Wetz" <Chris
[email protected]> wrote:

>I want to round a series of numbers to the nearest price point of 5 or 9
>pence (up or down). E.g. £12.74 would become £12.75, £12.78 would become
>£12.79, £12.91 would become £12.89
>
>Any ideas? I'm happy to use a series of calculations if there is no single
>function that will do the trick.
>
>TIA,
>
>Chris.

Perhaps:

=ROUND(A1/0.05,0)*0.05-(MOD(ROUND(A1/0.05,0)*0.5,1)=0)/100

But note that:

12.77 --> 12.75
12.92 --> 12.89

Not sure if this is what you want.
--ron