Hi There,
I know that the CEILING function can provide the ability to round up in
multiples, so, for example to the nearest 5p, however, whenever I've tried to
do this for the nearest 9p, then it doesn't seem to work. The first value is
usually ok, but then successive answers usually end in 8, then 7, then 6, etc.

Is there a way to choose a value that you want a calculation to end in (for
example the nearest 9p, so you get values of 9, 19, 29, etc)?

Then what I'd like to be able to do is if the value is 1 - 5 the answer ends
in a 5, for 6(or 5.1) to 9 the answer is 9 & then 0 is 0 ('cos if I want 0 to
be a 9, I can always take 1 away - I know how to do that!!)

Cheers

Well, if you know how to take 1 away then you know how to do the first
part - round it to the next highest place value and subtract 1:

=ROUND(A1,1)-0.01

if you have pounds and pence in A1. If it is just pence, then:

=ROUND(A1,-1)-1

Hope this helps.

Pete

"Colin Foster" wrote in message
...
Hi There,
I know that the CEILING function can provide the ability to round up in
multiples, so, for example to the nearest 5p, however, whenever I've tried
to
do this for the nearest 9p, then it doesn't seem to work. The first value
is
usually ok, but then successive answers usually end in 8, then 7, then 6,
etc.

Is there a way to choose a value that you want a calculation to end in
(for
example the nearest 9p, so you get values of 9, 19, 29, etc)?

Then what I'd like to be able to do is if the value is 1 - 5 the answer
ends
in a 5, for 6(or 5.1) to 9 the answer is 9 & then 0 is 0 ('cos if I want 0
to
be a 9, I can always take 1 away - I know how to do that!!)

Cheers

Hi Pete,
Bugger!!!! (good technical word, that one!) I'd thought of the ROUND
function, but wrapped around the CEILING, not the obvious answer that you've
come up with!

Ok, so, if I then wanted to also round to 5p, then, I do the round &
subtract 0.05 :-)

Cheers for this... sometimes, you just need to ask the question for someone
else to take the blindfold away!


"Pete_UK" wrote:

Well, if you know how to take 1 away then you know how to do the first
part - round it to the next highest place value and subtract 1:

=ROUND(A1,1)-0.01

if you have pounds and pence in A1. If it is just pence, then:

=ROUND(A1,-1)-1

Hope this helps.

Pete

"Colin Foster" wrote in message
...
Hi There,
I know that the CEILING function can provide the ability to round up in
multiples, so, for example to the nearest 5p, however, whenever I've tried
to
do this for the nearest 9p, then it doesn't seem to work. The first value
is
usually ok, but then successive answers usually end in 8, then 7, then 6,
etc.

Is there a way to choose a value that you want a calculation to end in
(for
example the nearest 9p, so you get values of 9, 19, 29, etc)?

Then what I'd like to be able to do is if the value is 1 - 5 the answer
ends
in a 5, for 6(or 5.1) to 9 the answer is 9 & then 0 is 0 ('cos if I want 0
to
be a 9, I can always take 1 away - I know how to do that!!)

Cheers

Thanks for feeding back, Colin.

Pete

On Apr 18, 2:19*pm, Colin Foster
wrote:
Hi Pete,
Bugger!!!! (good technical word, that one!) I'd thought of the ROUND
function, but wrapped around the CEILING, not the obvious answer that you've
come up with!

Ok, so, if I then wanted to also round to 5p, then, I do the round &
subtract 0.05 :-)

Cheers for this... sometimes, you just need to ask the question for someone
else to take the blindfold away!