Round Up by a factor not by a digit [MROUNDUP(number,multiple)]
Just change the formula to =ROUNDUP(A1/5,0)*5
Taking 21 as an example, dividing by 5 gives 4.2. Round this up to 0 places
gives 5, multiply back by 5 gives 25.
Similarly for 24.
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
wrote in message
oups.com...
On May 25, 7:23 pm, "Bob Phillips" wrote:
wrote in message
oups.com...
Hi
thanks for your answers, I've added some new comments....
On May 24, 6:03 pm, "Bob Phillips" wrote:
1) =ROUND(A1/3)*3 [This rounds up and down and not only up as I
require,
plus it uses digits rather than factors]
So use ROUNDUP.
Ceiling also uses digits but you seem happy with that. Give an example of
where mine gives you a differentr example to what you want
2) Why would you bother? [So I can learn the coding technique and then
adapt it to a more powerful and specific function]
Judging from your responses, your time would seem to be better served
understanding how formulae work so that you can adapt them.
3) a simple IF test
=IF(B1=1,MROUND(A8,3),MROUND(A8,4)) [This also rounds up and down, not
soley up as I require]
So just substitute my formula above with ROUNDUP in place of MROUND, the
principle is the same.
Thanks again, especially for insight into writing a function.
Regarding the equations, so how does your method result in values 21
to 24, for example, being rounded to 25 as per ceiling(x,5).
Judging from your responses.....
|