Hello

I need a similar formula to Angie.

In Cell L92, I would like to combine L92*1.03
with a trunc formula that rounds to 49p if L92*1.03< or = 49p
or that rounds to 99p if L92*1.03 is 50p-99p

eg. £12.06 would round to £12.49
£12.49 would stay as £12.49
£12.58 would round to £12.99
£12.99 would stay as £12.99

I am using 2007.

Many thanks & best wishes.

Rachael

"Bob Phillips" wrote:

It was Ugly Angie, but necessary. Those numbers were bigger than 1930 etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

I think I will be using your ugly version. It works very well. Its the
better of the two. It rounds down a number like 1990 to 1989 and rounds
up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is staying at 1625
and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in it
which
makes a number that looks like 1625 actually be something like 16.000001
ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers and it
worked
but I need to tweek it. A number which ends with a "5" like "1555"
rounds
up to 1559, I need it to stay the same, because I need numbers to end
with
5
or 9, therefore no change would be required for 1555. Also, I noticed
that
the numbers that ended with a "0" like "1630" actually rounded up to
1635.
How would I alter the formula so that anything ending with a "0" would
round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to when
the
result of that was exactly divisible by 10
(MOD(CEILING(A1,5),10)=0).
Thus
any number thatn rounds up to a multiple of 10 passes this test, so
by
just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away. SO
anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1
taken
away,. I included the test for the number being divisible by 10
without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that
started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628
round
to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635. The
AND
is
just to combine both tests and return a single TRUE/FALSE result
which
the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all instances
of
A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really don't
understand what or how it is working can you explain for me. This
formula
is
a bit advanced for me and I would really like to understand it so
I
can
use
it on a large spreadsheet. Also is there anyway to combine the
formula
you
gave me with the one simple one that I had initially "=D8/0.67".
Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling
function
to
round up to a specific number? Example: I have the number
1288
in D8
and
I
have a formula in E8 which says "=D8/0.67", the result is
1922.
But
I
would
like "1922" to round up to "1925". Had it been "1927", I
would
want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33