Pricing on a 'sliding scale' possible?

Hi, I have just joined this forum & realising the potential of Excel!

We have a vast range of products that I need to price on a 'sliding scale'.

EG. The lower cost items are mulitplied by say 2.3, then reducing down to around 1.8 for higher cost prices.

Then I want to be able to round up to '£x.99p' eg - £4.99 / £5.99 etc.

example
lower price item cost = £2.57 x 2.3 = £5.91 (need it to say £5.99)
Higher price item cost = £20.30 x 1.8 = £36.54 (need it say £36.99)

Can this be done?
Any help would be fantastic

Cheers
Mark

Originally Posted by Guitarmarky

Hi, I have just joined this forum & realising the potential of Excel!

We have a vast range of products that I need to price on a 'sliding scale'.

EG. The lower cost items are mulitplied by say 2.3, then reducing down to around 1.8 for higher cost prices.

Then I want to be able to round up to '£x.99p' eg - £4.99 / £5.99 etc.

example
lower price item cost = £2.57 x 2.3 = £5.91 (need it to say £5.99)
Higher price item cost = £20.30 x 1.8 = £36.54 (need it say £36.99)

Can this be done?
Any help would be fantastic

Cheers
Mark

If I understood you well, you might apply the following formula:

Please Login or Register  to view this content.

So, if you
* put prices in the first column
* put the other value (2.3, 1.8 or whatever) in the second column
* put that formula in the third column (put it in cell C1 and copy and paste it where it is needed)

you should get what you need.

Let me know if it worked.

ciao

Hmmm. Thanks for your replies, but not sure about those 2 examples.

I have tried the following code

where
A1 = cost price
B1 = Ratio
C1 = Result

Then the following code in D1 rounds it up:
=INT(C1)+0.99

eg, £2.57 x 2.3 = £5.91 then box D1 rounds it to £5.99. Great.

But, I would like it to be able to say for example:

if A1 is between
£2.14 - £2.53 use ratio 2.5
£2.54 - £2.94 use ratio 2.49
£2.95 - £3.35 use ratio 2.48

etc etc .........right the way through to things costing £30.

Am I asking too much??

Thanks again for your help!

Thank-you all for your help!

Still haven't quite sorted it, but will have another look next week now.

Will let you know!!

Cheers
Mark