Price Pointing/Complex Rounding

[Neil Mcknight]

I have a client who is trying to price his shop stock using Excel.

The problem is that he has taken his cost price and multiplied it by
his markup, which is an easy formula.

He is now left with his selling price, but what he wants to do is
round the selling price up to either the nearest £5 or £10 and
subtract 5p.
To explain this further, if his exact selling price is £123.45 he
wants to round this up to £124.95, but if his exact selling price is
£125.67 he wants to round this up to £130.95.

Does anyone know how to do this formula in Excel, in words it will
basically say if below pounds ending in the multiples of 5, round up
and subtract 5p. But if above pounds ending in 5 round up to the
nearest 10 and subtract 5p.

I hope this makes sense, but I just can't find a formula that works

Thanks in advance

Neil.

 

[Norman Harker]

Hi Neil!

Try:
=CEILING(A1,5)-0.05

But you need to satisfy yourself on what happens to prices of (eg)
124.95 and 125

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob Phillips]

Neil,

I can't figure out your example.£123.45 rounds up £5 to £125, that's fine.
But how does £125 rounding up £10 work out as £131? If it should be £135,
this should work

=ROUNDUP(A1/5,0)*5+((MOD(INT(A1),5)=0)*5)-0.05

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Neil Mcknight]

No sorry, rounding to the nearest £10 should make £125 round up to £130.

Basically if it is under the nearest £5 round to it, but if it is over £5
then round to the nearest £10.

Eg 123.23 becomes 125
125.50 becomes £130

I hope this explains better.
Thanks

Neil.

 

[JE McGimpsey]

If 125 rounds up, sounds like:

=CEILING(A1+0.0001,5)

will work for you.

 

[Bob Phillips]

Hi Neil,

SO how do you get ... £125.67 he wants to round this up to £130.95 ... in
your original message. If this should be £129.95, then just use

=ROUNDUP(A1/5,0)*5-0.05

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Neil Mcknight]

Thank very muc, this seems to do exactly what I am loking for.

Sorry about the original confusion with my silly examples, but you seem to
have got to the answer I wanted.

Thanks again

Neil.

 

[Bob Phillips]

Good, I'm glad we got there.

Bob

Thank very muc, this seems to do exactly what I am loking for.

Sorry about the original confusion with my silly examples, but you seem to
have got to the answer I wanted.

Thanks again

Neil.