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.

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"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.

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.

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.

On 3/4/04 11:51, in article , "Bob
Phillips" wrote:

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

If 125 rounds up, sounds like:

=CEILING(A1+0.0001,5)

will work for you.

In article ,
Neil Mcknight wrote:

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

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

On 3/4/04 11:51, in article , "Bob
Phillips" wrote:

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

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.

On 3/4/04 15:18, in article , "Bob
Phillips" wrote:

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

Good, I'm glad we got there.

Bob

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

On 3/4/04 15:18, in article , "Bob
Phillips" wrote:

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