Rounding to price points

[Chris Wetz]

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.

 

[Bob Phillips]

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]

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]

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]

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]

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)

 

[Ron Rosenfeld]

On Tue, 1 Mar 2005 01:11:03 -0800, "Chris Wetz" <Chris

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