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 wrote in message
...
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 wrote in message
...
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" wrote in message
...
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 wrote in message
...
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" wrote in message
...
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 wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 wrote in message
...
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
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