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