Round Up by a factor not by a digit [MROUNDUP(number,multiple)]
 

3 questions really, first most relevant and important:

1) I want the functionailty of mround() i.e. rounding by a factor, but
I want it to be rounded up as in roundup, but roundup rounds up by a
specified number of digits not a factor.
Can this be achieved without code?
2) If I was to write the code for my own funtions using vba for:
a)mround,b)roundup,c)& my new function, mroundup, what would the code
be? Also where would be the best palce to save this code so it is
available in all workbooks I open.
3) Can the mroundup function be adjusted to round up by factor z if
the valuex and by factor y if the value<x.

Appreciate any help
Thanks
Matty

Round Up by a factor not by a digit [MROUNDUP(number,multiple)]
 

1) =ROUND(A1/3)*3

as an example

2) Why would you bother?

3) a simple IF test

=IF(B1=1,MROUND(A8,3),MROUND(A8,4))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
oups.com...
3 questions really, first most relevant and important:

1) I want the functionailty of mround() i.e. rounding by a factor, but
I want it to be rounded up as in roundup, but roundup rounds up by a
specified number of digits not a factor.
Can this be achieved without code?
2) If I was to write the code for my own funtions using vba for:
a)mround,b)roundup,c)& my new function, mroundup, what would the code
be? Also where would be the best palce to save this code so it is
available in all workbooks I open.
3) Can the mroundup function be adjusted to round up by factor z if
the valuex and by factor y if the value<x.

Appreciate any help
Thanks
Matty

Round Up by a factor not by a digit [MROUNDUP(number,multiple)]
 

=CELING(number,multiple)
--
David Biddulph

wrote in message
oups.com...
3 questions really, first most relevant and important:

1) I want the functionailty of mround() i.e. rounding by a factor, but
I want it to be rounded up as in roundup, but roundup rounds up by a
specified number of digits not a factor.
Can this be achieved without code?
....

Round Up by a factor not by a digit [MROUNDUP(number,multiple)]
 

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]

as an example

2) Why would you bother? [So I can learn the coding technique and then adapt it to a more powerful and specific function]

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]

--
HTH

Bob

Round Up by a factor not by a digit [MROUNDUP(number,multiple)]
 

On May 24, 6:41 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=CELING(number,multiple)
--
David Biddulph

wrote in message

oups.com...3 questions really, first most relevant and important:

1) I want the functionailty of mround() i.e. rounding by a factor, but
I want it to be rounded up as in roundup, but roundup rounds up by a
specified number of digits not a factor.
Can this be achieved without code?

...
Thanks for that David - that does what I'm after.
I'm keen to learn how to write my own functions and would like to use
some microsoft functions as a starting point.
So using =ceiling(number,multiple/digit) as an example can anyone
supply the code that would be required to make this function if it
didn't exist already in excel?

Round Up by a factor not by a digit [MROUNDUP(number,multiple)]
 

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.

Round Up by a factor not by a digit [MROUNDUP(number,multiple)]
 

Public Function MyCeiling(rng As Range, by)
If rng.Count 1 Then
MyCeiling = CVErr(xlErrRef)
Exit Function
End If

MyCeiling = ((rng.Value + by - 1) \ by) * by
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
ps.com...
On May 24, 6:41 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
=CELING(number,multiple)
--
David Biddulph

wrote in message

oups.com...3 questions
really, first most relevant and important:

1) I want the functionailty of mround() i.e. rounding by a factor, but
I want it to be rounded up as in roundup, but roundup rounds up by a
specified number of digits not a factor.
Can this be achieved without code?

...
Thanks for that David - that does what I'm after.
I'm keen to learn how to write my own functions and would like to use
some microsoft functions as a starting point.
So using =ceiling(number,multiple/digit) as an example can anyone
supply the code that would be required to make this function if it
didn't exist already in excel?

Round Up by a factor not by a digit [MROUNDUP(number,multiple)]
 

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.....

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.....