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

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

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

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.

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

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

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

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?

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?