modified CEILING function??

[Colin Foster]

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

 

[Pete_UK]

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]

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]

Thanks for feeding back, Colin.

Pete