Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Angie33
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Does anyone know how to use the Round Function or the Ceiling function to
round up to a specific number? Example: I have the number 1288 in D8 and I
have a formula in E8 which says =D8/0.67, the result is 1922. But I would
like 1922 to round up to 1925. Had it been 1927, I would want it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling function to
round up to a specific number? Example: I have the number 1288 in D8 and

I
have a formula in E8 which says "=D8/0.67", the result is 1922. But I

would
like "1922" to round up to "1925". Had it been "1927", I would want it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33



  #4   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Hello,

=CEILING(A1-1,4)+1

HTH,
Bernd

  #5   Report Post  
Posted to microsoft.public.excel.misc
Angie33
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Bob,

I tried it and it worked, but to be honest with you I really don't
understand what or how it is working can you explain for me. This formula is
a bit advanced for me and I would really like to understand it so I can use
it on a large spreadsheet. Also is there anyway to combine the formula you
gave me with the one simple one that I had initially "=D8/0.67". Bob you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling function to
round up to a specific number? Example: I have the number 1288 in D8 and

I
have a formula in E8 which says "=D8/0.67", the result is 1922. But I

would
like "1922" to round up to "1925". Had it been "1927", I would want it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33






  #6   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Hello,

Is this really what you wanted:

Input Bob's Mine
1913 1915 1913
1914 1915 1917
1915 1915 1917
1916 1919 1917
1917 1919 1917
1918 1919 1921
1919 1919 1921
1920 1920 1921
1921 1925 1921
1922 1925 1925
1923 1925 1925
1924 1925 1925
1925 1925 1925
1926 1929 1929
1927 1929 1929
1928 1929 1929
1929 1929 1929
1930 1930 1933
1931 1935 1933
1932 1935 1933
1933 1935 1933
1934 1935 1937
1935 1935 1937

(Just want to make sure :-)

Regards,
Bernd

  #7   Report Post  
Posted to microsoft.public.excel.misc
Angie33
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Hi,

I am really looking for what Bob gave me. I need things to round up to
either a 5 or a 9. Thanks,


--
Angie33


" wrote:

Hello,

Is this really what you wanted:

Input Bob's Mine
1913 1915 1913
1914 1915 1917
1915 1915 1917
1916 1919 1917
1917 1919 1917
1918 1919 1921
1919 1919 1921
1920 1920 1921
1921 1925 1921
1922 1925 1925
1923 1925 1925
1924 1925 1925
1925 1925 1925
1926 1929 1929
1927 1929 1929
1928 1929 1929
1929 1929 1929
1930 1930 1933
1931 1935 1933
1932 1935 1933
1933 1935 1933
1934 1935 1937
1935 1935 1937

(Just want to make sure :-)

Regards,
Bernd


  #8   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to when the
result of that was exactly divisible by 10 (MOD(CEILING(A1,5),10)=0). Thus
any number thatn rounds up to a multiple of 10 passes this test, so by just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away. SO anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1 taken
away,. I included the test for the number being divisible by 10 without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628 round to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635. The AND is
just to combine both tests and return a single TRUE/FALSE result which the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all instances of A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really don't
understand what or how it is working can you explain for me. This formula

is
a bit advanced for me and I would really like to understand it so I can

use
it on a large spreadsheet. Also is there anyway to combine the formula

you
gave me with the one simple one that I had initially "=D8/0.67". Bob

you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling function

to
round up to a specific number? Example: I have the number 1288 in D8

and
I
have a formula in E8 which says "=D8/0.67", the result is 1922. But

I
would
like "1922" to round up to "1925". Had it been "1927", I would want

it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33






  #9   Report Post  
Posted to microsoft.public.excel.misc
Angie33
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Bob,

I did as you suggested, changed the A10 to D8/0.67. It worked now I will
test it out on larger spread sheet. Thanks so much for your explanation. It
makes sense I just have to absorb it all. Thanks much.
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to when the
result of that was exactly divisible by 10 (MOD(CEILING(A1,5),10)=0). Thus
any number thatn rounds up to a multiple of 10 passes this test, so by just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away. SO anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1 taken
away,. I included the test for the number being divisible by 10 without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628 round to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635. The AND is
just to combine both tests and return a single TRUE/FALSE result which the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all instances of A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really don't
understand what or how it is working can you explain for me. This formula

is
a bit advanced for me and I would really like to understand it so I can

use
it on a large spreadsheet. Also is there anyway to combine the formula

you
gave me with the one simple one that I had initially "=D8/0.67". Bob

you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling function

to
round up to a specific number? Example: I have the number 1288 in D8

and
I
have a formula in E8 which says "=D8/0.67", the result is 1922. But

I
would
like "1922" to round up to "1925". Had it been "1927", I would want

it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33






  #10   Report Post  
Posted to microsoft.public.excel.misc
Angie33
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Hi Bob,

Need your help one more time. I tried it out on a few numbers and it worked
but I need to tweek it. A number which ends with a "5" like "1555" rounds
up to 1559, I need it to stay the same, because I need numbers to end with 5
or 9, therefore no change would be required for 1555. Also, I noticed that
the numbers that ended with a "0" like "1630" actually rounded up to 1635.
How would I alter the formula so that anything ending with a "0" would round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to when the
result of that was exactly divisible by 10 (MOD(CEILING(A1,5),10)=0). Thus
any number thatn rounds up to a multiple of 10 passes this test, so by just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away. SO anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1 taken
away,. I included the test for the number being divisible by 10 without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628 round to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635. The AND is
just to combine both tests and return a single TRUE/FALSE result which the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all instances of A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really don't
understand what or how it is working can you explain for me. This formula

is
a bit advanced for me and I would really like to understand it so I can

use
it on a large spreadsheet. Also is there anyway to combine the formula

you
gave me with the one simple one that I had initially "=D8/0.67". Bob

you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling function

to
round up to a specific number? Example: I have the number 1288 in D8

and
I
have a formula in E8 which says "=D8/0.67", the result is 1922. But

I
would
like "1922" to round up to "1925". Had it been "1927", I would want

it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33








  #11   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

I don't get the same results as you are getting. 1625 is staying at 1625 and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in it which
makes a number that looks like 1625 actually be something like 16.000001 ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers and it

worked
but I need to tweek it. A number which ends with a "5" like "1555"

rounds
up to 1559, I need it to stay the same, because I need numbers to end with

5
or 9, therefore no change would be required for 1555. Also, I noticed

that
the numbers that ended with a "0" like "1630" actually rounded up to 1635.
How would I alter the formula so that anything ending with a "0" would

round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to when the
result of that was exactly divisible by 10 (MOD(CEILING(A1,5),10)=0).

Thus
any number thatn rounds up to a multiple of 10 passes this test, so by

just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away. SO

anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1 taken
away,. I included the test for the number being divisible by 10 without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that

started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628 round

to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635. The AND

is
just to combine both tests and return a single TRUE/FALSE result which

the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all instances of

A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really don't
understand what or how it is working can you explain for me. This

formula
is
a bit advanced for me and I would really like to understand it so I

can
use
it on a large spreadsheet. Also is there anyway to combine the

formula
you
gave me with the one simple one that I had initially "=D8/0.67".

Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling

function
to
round up to a specific number? Example: I have the number 1288

in D8
and
I
have a formula in E8 which says "=D8/0.67", the result is 1922.

But
I
would
like "1922" to round up to "1925". Had it been "1927", I would

want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33








  #12   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Bob Phillips wrote:

I think it is the D8/0.67, I think that has a small increment in it which
makes a number that looks like 1625 actually be something like 16.000001 ort
so, which will go up.


I am not following this discussion closely, but I wonder if D8*2/3
yields the desired results. Just a WAG, really.

  #13   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Errata ....

I wrote:
Bob Phillips wrote:
I think it is the D8/0.67, I think that has a small increment in it which
makes a number that looks like 1625 actually be something like 16.000001 ort
so, which will go up.


I am not following this discussion closely, but I wonder if D8*2/3
yields the desired results. Just a WAG, really.


First, that should be D8*3/2. Second, ignore my comment. It does not
seem relevant to the crux of the thread.

  #14   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

That thought occurred to me Joe, but I decided against messing with the OPs
data/original req <G

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Errata ....

I wrote:
Bob Phillips wrote:
I think it is the D8/0.67, I think that has a small increment in it

which
makes a number that looks like 1625 actually be something like

16.000001 ort
so, which will go up.


I am not following this discussion closely, but I wonder if D8*2/3
yields the desired results. Just a WAG, really.


First, that should be D8*3/2. Second, ignore my comment. It does not
seem relevant to the crux of the thread.



  #15   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Hi Bob,

I suggest to take
=IF(AND(A1-TRUNC(A1,-1)5,A1-TRUNC(A1,-1)<=9),CEILING(A1+1,5)-1,CEILING(A1+5,10)-5)

Instead of A1 we can take D8*3/2 again.

Regards,
Bernd



  #16   Report Post  
Posted to microsoft.public.excel.misc
Angie33
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Hi Bob,

I think I will be using your ugly version. It works very well. Its the
better of the two. It rounds down a number like 1990 to 1989 and rounds up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is staying at 1625 and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in it which
makes a number that looks like 1625 actually be something like 16.000001 ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers and it

worked
but I need to tweek it. A number which ends with a "5" like "1555"

rounds
up to 1559, I need it to stay the same, because I need numbers to end with

5
or 9, therefore no change would be required for 1555. Also, I noticed

that
the numbers that ended with a "0" like "1630" actually rounded up to 1635.
How would I alter the formula so that anything ending with a "0" would

round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to when the
result of that was exactly divisible by 10 (MOD(CEILING(A1,5),10)=0).

Thus
any number thatn rounds up to a multiple of 10 passes this test, so by

just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away. SO

anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1 taken
away,. I included the test for the number being divisible by 10 without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that

started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628 round

to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635. The AND

is
just to combine both tests and return a single TRUE/FALSE result which

the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all instances of

A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really don't
understand what or how it is working can you explain for me. This

formula
is
a bit advanced for me and I would really like to understand it so I

can
use
it on a large spreadsheet. Also is there anyway to combine the

formula
you
gave me with the one simple one that I had initially "=D8/0.67".

Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling

function
to
round up to a specific number? Example: I have the number 1288

in D8
and
I
have a formula in E8 which says "=D8/0.67", the result is 1922.

But
I
would
like "1922" to round up to "1925". Had it been "1927", I would

want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33









  #17   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

It was Ugly Angie, but necessary. Those numbers were bigger than 1930 etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

I think I will be using your ugly version. It works very well. Its the
better of the two. It rounds down a number like 1990 to 1989 and rounds

up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is staying at 1625

and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in it

which
makes a number that looks like 1625 actually be something like 16.000001

ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers and it

worked
but I need to tweek it. A number which ends with a "5" like "1555"

rounds
up to 1559, I need it to stay the same, because I need numbers to end

with
5
or 9, therefore no change would be required for 1555. Also, I noticed

that
the numbers that ended with a "0" like "1630" actually rounded up to

1635.
How would I alter the formula so that anything ending with a "0" would

round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to when

the
result of that was exactly divisible by 10

(MOD(CEILING(A1,5),10)=0).
Thus
any number thatn rounds up to a multiple of 10 passes this test, so

by
just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away. SO

anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1

taken
away,. I included the test for the number being divisible by 10

without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that

started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628

round
to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635. The

AND
is
just to combine both tests and return a single TRUE/FALSE result

which
the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all instances

of
A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really don't
understand what or how it is working can you explain for me. This

formula
is
a bit advanced for me and I would really like to understand it so

I
can
use
it on a large spreadsheet. Also is there anyway to combine the

formula
you
gave me with the one simple one that I had initially "=D8/0.67".

Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing

direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling

function
to
round up to a specific number? Example: I have the number

1288
in D8
and
I
have a formula in E8 which says "=D8/0.67", the result is

1922.
But
I
would
like "1922" to round up to "1925". Had it been "1927", I

would
want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33











  #18   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default Roundup or Ceiling Function to round to a specific number

Angie33 wrote:
Hi Bob,
I think I will be using your ugly version. It works very well.


Ugly or not, I think it fits the needed solution well. It took me a
long time to understand the poorly-stated and every-changing problem
specification. When I finally did, I independently came up with
exactly the same solution. Kudos to Bob for seeing through the
confusion early on.

It appears that your rounding rules a round up to a multiple of 5,
but when that results in a multiple of 10, round down from there (i.e.
subtract one) so that the number ends in 9 instead of 0. That is
exactly how Bob's expression reads. So frankly, I see nothing ugly
about solution. "The punishment fits the crime" ;-).

  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default Roundup or Ceiling Function to round to a specific number

Hello

I need a similar formula to Angie.

In Cell L92, I would like to combine L92*1.03
with a trunc formula that rounds to 49p if L92*1.03< or = 49p
or that rounds to 99p if L92*1.03 is 50p-99p

eg. £12.06 would round to £12.49
£12.49 would stay as £12.49
£12.58 would round to £12.99
£12.99 would stay as £12.99

I am using 2007.

Many thanks & best wishes.

Rachael

"Bob Phillips" wrote:

It was Ugly Angie, but necessary. Those numbers were bigger than 1930 etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

I think I will be using your ugly version. It works very well. Its the
better of the two. It rounds down a number like 1990 to 1989 and rounds

up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is staying at 1625

and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in it

which
makes a number that looks like 1625 actually be something like 16.000001

ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers and it
worked
but I need to tweek it. A number which ends with a "5" like "1555"
rounds
up to 1559, I need it to stay the same, because I need numbers to end

with
5
or 9, therefore no change would be required for 1555. Also, I noticed
that
the numbers that ended with a "0" like "1630" actually rounded up to

1635.
How would I alter the formula so that anything ending with a "0" would
round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to when

the
result of that was exactly divisible by 10

(MOD(CEILING(A1,5),10)=0).
Thus
any number thatn rounds up to a multiple of 10 passes this test, so

by
just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away. SO
anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1

taken
away,. I included the test for the number being divisible by 10

without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that
started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628

round
to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635. The

AND
is
just to combine both tests and return a single TRUE/FALSE result

which
the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all instances

of
A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really don't
understand what or how it is working can you explain for me. This
formula
is
a bit advanced for me and I would really like to understand it so

I
can
use
it on a large spreadsheet. Also is there anyway to combine the
formula
you
gave me with the one simple one that I had initially "=D8/0.67".
Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing

direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling
function
to
round up to a specific number? Example: I have the number

1288
in D8
and
I
have a formula in E8 which says "=D8/0.67", the result is

1922.
But
I
would
like "1922" to round up to "1925". Had it been "1927", I

would
want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33












  #20   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Roundup or Ceiling Function to round to a specific number

Try this:

=CEILING(L92*1.03+0.01,0.5)-0.01

--
Biff
Microsoft Excel MVP


"Rachael F" wrote in message
...
Hello

I need a similar formula to Angie.

In Cell L92, I would like to combine L92*1.03
with a trunc formula that rounds to 49p if L92*1.03< or = 49p
or that rounds to 99p if L92*1.03 is 50p-99p

eg. 12.06 would round to 12.49
12.49 would stay as 12.49
12.58 would round to 12.99
12.99 would stay as 12.99

I am using 2007.

Many thanks & best wishes.

Rachael

"Bob Phillips" wrote:

It was Ugly Angie, but necessary. Those numbers were bigger than 1930
etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

I think I will be using your ugly version. It works very well. Its
the
better of the two. It rounds down a number like 1990 to 1989 and
rounds

up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is staying at
1625

and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in it

which
makes a number that looks like 1625 actually be something like
16.000001

ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers and
it
worked
but I need to tweek it. A number which ends with a "5" like
"1555"
rounds
up to 1559, I need it to stay the same, because I need numbers to
end

with
5
or 9, therefore no change would be required for 1555. Also, I
noticed
that
the numbers that ended with a "0" like "1630" actually rounded up
to

1635.
How would I alter the formula so that anything ending with a "0"
would
round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to
when

the
result of that was exactly divisible by 10

(MOD(CEILING(A1,5),10)=0).
Thus
any number thatn rounds up to a multiple of 10 passes this test,
so

by
just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away.
SO
anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1

taken
away,. I included the test for the number being divisible by 10

without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that
started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628

round
to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635.
The

AND
is
just to combine both tests and return a single TRUE/FALSE result

which
the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all
instances

of
A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really
don't
understand what or how it is working can you explain for me.
This
formula
is
a bit advanced for me and I would really like to understand it
so

I
can
use
it on a large spreadsheet. Also is there anyway to combine the
formula
you
gave me with the one simple one that I had initially
"=D8/0.67".
Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing

direct)

"Angie33" wrote in
message
...
Does anyone know how to use the Round Function or the
Ceiling
function
to
round up to a specific number? Example: I have the number

1288
in D8
and
I
have a formula in E8 which says "=D8/0.67", the result is

1922.
But
I
would
like "1922" to round up to "1925". Had it been "1927", I

would
want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33
















  #21   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default Roundup or Ceiling Function to round to a specific number

Thanks Biff. The formula worked a treat.

Best wishes.

Rachael

"T. Valko" wrote:

Try this:

=CEILING(L92*1.03+0.01,0.5)-0.01

--
Biff
Microsoft Excel MVP


"Rachael F" wrote in message
...
Hello

I need a similar formula to Angie.

In Cell L92, I would like to combine L92*1.03
with a trunc formula that rounds to 49p if L92*1.03< or = 49p
or that rounds to 99p if L92*1.03 is 50p-99p

eg. £12.06 would round to £12.49
£12.49 would stay as £12.49
£12.58 would round to £12.99
£12.99 would stay as £12.99

I am using 2007.

Many thanks & best wishes.

Rachael

"Bob Phillips" wrote:

It was Ugly Angie, but necessary. Those numbers were bigger than 1930
etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

I think I will be using your ugly version. It works very well. Its
the
better of the two. It rounds down a number like 1990 to 1989 and
rounds
up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is staying at
1625
and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in it
which
makes a number that looks like 1625 actually be something like
16.000001
ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers and
it
worked
but I need to tweek it. A number which ends with a "5" like
"1555"
rounds
up to 1559, I need it to stay the same, because I need numbers to
end
with
5
or 9, therefore no change would be required for 1555. Also, I
noticed
that
the numbers that ended with a "0" like "1630" actually rounded up
to
1635.
How would I alter the formula so that anything ending with a "0"
would
round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to
when
the
result of that was exactly divisible by 10
(MOD(CEILING(A1,5),10)=0).
Thus
any number thatn rounds up to a multiple of 10 passes this test,
so
by
just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away.
SO
anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1
taken
away,. I included the test for the number being divisible by 10
without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that
started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628
round
to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635.
The
AND
is
just to combine both tests and return a single TRUE/FALSE result
which
the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all
instances
of
A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really
don't
understand what or how it is working can you explain for me.
This
formula
is
a bit advanced for me and I would really like to understand it
so
I
can
use
it on a large spreadsheet. Also is there anyway to combine the
formula
you
gave me with the one simple one that I had initially
"=D8/0.67".
Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in
message
...
Does anyone know how to use the Round Function or the
Ceiling
function
to
round up to a specific number? Example: I have the number
1288
in D8
and
I
have a formula in E8 which says "=D8/0.67", the result is
1922.
But
I
would
like "1922" to round up to "1925". Had it been "1927", I
would
want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33















  #22   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default Roundup or Ceiling Function to round to a specific number

Hello

I would also like a formula to rounddown to the nearest 49p or 99p. I can
only manage to rounddown to 99p.

eg. £12.06 would rounddown to £11.99
£12.49 would stay as £12.49
£12.58 would rounddown to £12.49
£12.99 would stay as £12.99

Many thanks.

Rachael

"Rachael F" wrote:

Thanks Biff. The formula worked a treat.

Best wishes.

Rachael

"T. Valko" wrote:

Try this:

=CEILING(L92*1.03+0.01,0.5)-0.01

--
Biff
Microsoft Excel MVP


"Rachael F" wrote in message
...
Hello

I need a similar formula to Angie.

In Cell L92, I would like to combine L92*1.03
with a trunc formula that rounds to 49p if L92*1.03< or = 49p
or that rounds to 99p if L92*1.03 is 50p-99p

eg. £12.06 would round to £12.49
£12.49 would stay as £12.49
£12.58 would round to £12.99
£12.99 would stay as £12.99

I am using 2007.

Many thanks & best wishes.

Rachael

"Bob Phillips" wrote:

It was Ugly Angie, but necessary. Those numbers were bigger than 1930
etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

I think I will be using your ugly version. It works very well. Its
the
better of the two. It rounds down a number like 1990 to 1989 and
rounds
up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is staying at
1625
and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in it
which
makes a number that looks like 1625 actually be something like
16.000001
ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers and
it
worked
but I need to tweek it. A number which ends with a "5" like
"1555"
rounds
up to 1559, I need it to stay the same, because I need numbers to
end
with
5
or 9, therefore no change would be required for 1555. Also, I
noticed
that
the numbers that ended with a "0" like "1630" actually rounded up
to
1635.
How would I alter the formula so that anything ending with a "0"
would
round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy, just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as to
when
the
result of that was exactly divisible by 10
(MOD(CEILING(A1,5),10)=0).
Thus
any number thatn rounds up to a multiple of 10 passes this test,
so
by
just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one away.
SO
anything
that is a multiple of 10 when rounded up to the nearest 5 gets 1
taken
away,. I included the test for the number being divisible by 10
without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers that
started as
multiples of 10, 1630 etc., didn't get changed to 1629. So 1628
round
to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to 1635.
The
AND
is
just to combine both tests and return a single TRUE/FALSE result
which
the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all
instances
of
A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really
don't
understand what or how it is working can you explain for me.
This
formula
is
a bit advanced for me and I would really like to understand it
so
I
can
use
it on a large spreadsheet. Also is there anyway to combine the
formula
you
gave me with the one simple one that I had initially
"=D8/0.67".
Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in
message
...
Does anyone know how to use the Round Function or the
Ceiling
function
to
round up to a specific number? Example: I have the number
1288
in D8
and
I
have a formula in E8 which says "=D8/0.67", the result is
1922.
But
I
would
like "1922" to round up to "1925". Had it been "1927", I
would
want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33















  #23   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Roundup or Ceiling Function to round to a specific number

=FLOOR(A1+0.01,0.5)-0.01
--
David Biddulph

"Rachael F" wrote in message
...
Hello

I would also like a formula to rounddown to the nearest 49p or 99p. I can
only manage to rounddown to 99p.

eg. 12.06 would rounddown to 11.99
12.49 would stay as 12.49
12.58 would rounddown to 12.49
12.99 would stay as 12.99

Many thanks.

Rachael

"Rachael F" wrote:

Thanks Biff. The formula worked a treat.

Best wishes.

Rachael

"T. Valko" wrote:

Try this:

=CEILING(L92*1.03+0.01,0.5)-0.01

--
Biff
Microsoft Excel MVP


"Rachael F" wrote in message
...
Hello

I need a similar formula to Angie.

In Cell L92, I would like to combine L92*1.03
with a trunc formula that rounds to 49p if L92*1.03< or = 49p
or that rounds to 99p if L92*1.03 is 50p-99p

eg. 12.06 would round to 12.49
12.49 would stay as 12.49
12.58 would round to 12.99
12.99 would stay as 12.99

I am using 2007.

Many thanks & best wishes.

Rachael

"Bob Phillips" wrote:

It was Ugly Angie, but necessary. Those numbers were bigger than
1930
etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

I think I will be using your ugly version. It works very well.
Its
the
better of the two. It rounds down a number like 1990 to 1989 and
rounds
up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is staying
at
1625
and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in
it
which
makes a number that looks like 1625 actually be something like
16.000001
ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers
and
it
worked
but I need to tweek it. A number which ends with a "5" like
"1555"
rounds
up to 1559, I need it to stay the same, because I need numbers
to
end
with
5
or 9, therefore no change would be required for 1555. Also, I
noticed
that
the numbers that ended with a "0" like "1630" actually rounded
up
to
1635.
How would I alter the formula so that anything ending with a
"0"
would
round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy,
just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as
to
when
the
result of that was exactly divisible by 10
(MOD(CEILING(A1,5),10)=0).
Thus
any number thatn rounds up to a multiple of 10 passes this
test,
so
by
just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one
away.
SO
anything
that is a multiple of 10 when rounded up to the nearest 5
gets 1
taken
away,. I included the test for the number being divisible by
10
without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers
that
started as
multiples of 10, 1630 etc., didn't get changed to 1629. So
1628
round
to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to
1635.
The
AND
is
just to combine both tests and return a single TRUE/FALSE
result
which
the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all
instances
of
A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in
message
...
Bob,

I tried it and it worked, but to be honest with you I
really
don't
understand what or how it is working can you explain for
me.
This
formula
is
a bit advanced for me and I would really like to
understand it
so
I
can
use
it on a large spreadsheet. Also is there anyway to
combine the
formula
you
gave me with the one simple one that I had initially
"=D8/0.67".
Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if
mailing
direct)

"Angie33" wrote in
message
...
Does anyone know how to use the Round Function or the
Ceiling
function
to
round up to a specific number? Example: I have the
number
1288
in D8
and
I
have a formula in E8 which says "=D8/0.67", the
result is
1922.
But
I
would
like "1922" to round up to "1925". Had it been
"1927", I
would
want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33

















  #24   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default Roundup or Ceiling Function to round to a specific number

Thanks very much David.

"David Biddulph" wrote:

=FLOOR(A1+0.01,0.5)-0.01
--
David Biddulph

"Rachael F" wrote in message
...
Hello

I would also like a formula to rounddown to the nearest 49p or 99p. I can
only manage to rounddown to 99p.

eg. £12.06 would rounddown to £11.99
£12.49 would stay as £12.49
£12.58 would rounddown to £12.49
£12.99 would stay as £12.99

Many thanks.

Rachael

"Rachael F" wrote:

Thanks Biff. The formula worked a treat.

Best wishes.

Rachael

"T. Valko" wrote:

Try this:

=CEILING(L92*1.03+0.01,0.5)-0.01

--
Biff
Microsoft Excel MVP


"Rachael F" wrote in message
...
Hello

I need a similar formula to Angie.

In Cell L92, I would like to combine L92*1.03
with a trunc formula that rounds to 49p if L92*1.03< or = 49p
or that rounds to 99p if L92*1.03 is 50p-99p

eg. £12.06 would round to £12.49
£12.49 would stay as £12.49
£12.58 would round to £12.99
£12.99 would stay as £12.99

I am using 2007.

Many thanks & best wishes.

Rachael

"Bob Phillips" wrote:

It was Ugly Angie, but necessary. Those numbers were bigger than
1930
etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

I think I will be using your ugly version. It works very well.
Its
the
better of the two. It rounds down a number like 1990 to 1989 and
rounds
up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is staying
at
1625
and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in
it
which
makes a number that looks like 1625 actually be something like
16.000001
ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers
and
it
worked
but I need to tweek it. A number which ends with a "5" like
"1555"
rounds
up to 1559, I need it to stay the same, because I need numbers
to
end
with
5
or 9, therefore no change would be required for 1555. Also, I
noticed
that
the numbers that ended with a "0" like "1630" actually rounded
up
to
1635.
How would I alter the formula so that anything ending with a
"0"
would
round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy,
just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as
to
when
the
result of that was exactly divisible by 10
(MOD(CEILING(A1,5),10)=0).
Thus
any number thatn rounds up to a multiple of 10 passes this
test,
so
by
just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one
away.
SO
anything
that is a multiple of 10 when rounded up to the nearest 5
gets 1
taken
away,. I included the test for the number being divisible by
10
without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers
that
started as
multiples of 10, 1630 etc., didn't get changed to 1629. So
1628
round
to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to
1635.
The
AND
is
just to combine both tests and return a single TRUE/FALSE
result
which
the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all
instances
of
A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in
message
...
Bob,

I tried it and it worked, but to be honest with you I
really
don't
understand what or how it is working can you explain for
me.
This
formula
is
a bit advanced for me and I would really like to
understand it
so
I
can
use
it on a large spreadsheet. Also is there anyway to
combine the
formula
you
gave me with the one simple one that I had initially
"=D8/0.67".
Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if
mailing
direct)

"Angie33" wrote in
message
...
Does anyone know how to use the Round Function or the
Ceiling
function
to
round up to a specific number? Example: I have the
number
1288
in D8
and
I
have a formula in E8 which says "=D8/0.67", the
result is
1922.
But
I
would
like "1922" to round up to "1925". Had it been
"1927", I
would
want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33

















  #25   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default Roundup or Ceiling Function to round to a specific number

You're welcome. Glad it helped.
--
David Biddulph

"Rachael F" wrote in message
...
Thanks very much David.

"David Biddulph" wrote:

=FLOOR(A1+0.01,0.5)-0.01
--
David Biddulph

"Rachael F" wrote in message
...
Hello

I would also like a formula to rounddown to the nearest 49p or 99p. I
can
only manage to rounddown to 99p.

eg. 12.06 would rounddown to 11.99
12.49 would stay as 12.49
12.58 would rounddown to 12.49
12.99 would stay as 12.99

Many thanks.

Rachael

"Rachael F" wrote:

Thanks Biff. The formula worked a treat.

Best wishes.

Rachael

"T. Valko" wrote:

Try this:

=CEILING(L92*1.03+0.01,0.5)-0.01

--
Biff
Microsoft Excel MVP


"Rachael F" wrote in message
...
Hello

I need a similar formula to Angie.

In Cell L92, I would like to combine L92*1.03
with a trunc formula that rounds to 49p if L92*1.03< or = 49p
or that rounds to 99p if L92*1.03 is 50p-99p

eg. 12.06 would round to 12.49
12.49 would stay as 12.49
12.58 would round to 12.99
12.99 would stay as 12.99

I am using 2007.

Many thanks & best wishes.

Rachael

"Bob Phillips" wrote:

It was Ugly Angie, but necessary. Those numbers were bigger than
1930
etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

I think I will be using your ugly version. It works very well.
Its
the
better of the two. It rounds down a number like 1990 to 1989
and
rounds
up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is
staying
at
1625
and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment
in
it
which
makes a number that looks like 1625 actually be something
like
16.000001
ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in
message
...
Hi Bob,

Need your help one more time. I tried it out on a few
numbers
and
it
worked
but I need to tweek it. A number which ends with a "5"
like
"1555"
rounds
up to 1559, I need it to stay the same, because I need
numbers
to
end
with
5
or 9, therefore no change would be required for 1555.
Also, I
noticed
that
the numbers that ended with a "0" like "1630" actually
rounded
up
to
1635.
How would I alter the formula so that anything ending with
a
"0"
would
round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been
easy,
just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested
as
to
when
the
result of that was exactly divisible by 10
(MOD(CEILING(A1,5),10)=0).
Thus
any number thatn rounds up to a multiple of 10 passes
this
test,
so
by
just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes
one
away.
SO
anything
that is a multiple of 10 when rounded up to the nearest 5
gets 1
taken
away,. I included the test for the number being divisible
by
10
without
rounding up to the nearest 5 (MOD(A1,10)<0) so that
numbers
that
started as
multiples of 10, 1630 etc., didn't get changed to 1629.
So
1628
round
to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds
to
1635.
The
AND
is
just to combine both tests and return a single TRUE/FALSE
result
which
the -
will negate (-1 or -0).

As for the second bit, I think you just need to change
all
instances
of
A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in
message
...
Bob,

I tried it and it worked, but to be honest with you I
really
don't
understand what or how it is working can you explain
for
me.
This
formula
is
a bit advanced for me and I would really like to
understand it
so
I
can
use
it on a large spreadsheet. Also is there anyway to
combine the
formula
you
gave me with the one simple one that I had initially
"=D8/0.67".
Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if
mailing
direct)

"Angie33" wrote
in
message
...
Does anyone know how to use the Round Function or
the
Ceiling
function
to
round up to a specific number? Example: I have
the
number
1288
in D8
and
I
have a formula in E8 which says "=D8/0.67", the
result is
1922.
But
I
would
like "1922" to round up to "1925". Had it been
"1927", I
would
want
it to
round up to 1929. Can anyone tell me how to do
this?
--
Angie33





















  #26   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Roundup or Ceiling Function to round to a specific number

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rachael F" wrote in message
...
Thanks Biff. The formula worked a treat.

Best wishes.

Rachael

"T. Valko" wrote:

Try this:

=CEILING(L92*1.03+0.01,0.5)-0.01

--
Biff
Microsoft Excel MVP


"Rachael F" wrote in message
...
Hello

I need a similar formula to Angie.

In Cell L92, I would like to combine L92*1.03
with a trunc formula that rounds to 49p if L92*1.03< or = 49p
or that rounds to 99p if L92*1.03 is 50p-99p

eg. 12.06 would round to 12.49
12.49 would stay as 12.49
12.58 would round to 12.99
12.99 would stay as 12.99

I am using 2007.

Many thanks & best wishes.

Rachael

"Bob Phillips" wrote:

It was Ugly Angie, but necessary. Those numbers were bigger than 1930
etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

I think I will be using your ugly version. It works very well. Its
the
better of the two. It rounds down a number like 1990 to 1989 and
rounds
up
where it needs to. Thanks so much for your help.
--
Angie33


"Bob Phillips" wrote:

I don't get the same results as you are getting. 1625 is staying
at
1625
and
1630 is staying at 1630, neither is rounding up.

I think it is the D8/0.67, I think that has a small increment in
it
which
makes a number that looks like 1625 actually be something like
16.000001
ort
so, which will go up.

Try this (getting ugly) version

=CEILING(ROUND(D8/0.67,0),5)-(MOD(CEILING(ROUND(D8/0.67,0),5),10)=0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Hi Bob,

Need your help one more time. I tried it out on a few numbers
and
it
worked
but I need to tweek it. A number which ends with a "5" like
"1555"
rounds
up to 1559, I need it to stay the same, because I need numbers
to
end
with
5
or 9, therefore no change would be required for 1555. Also, I
noticed
that
the numbers that ended with a "0" like "1630" actually rounded
up
to
1635.
How would I alter the formula so that anything ending with a "0"
would
round
down to "29" instead of up to "35"?
--
Angie33


"Bob Phillips" wrote:

I'll give it a shot at explaining it.

if 1627 were to round up to 1630, it would have been easy,
just

=CEILING(A1,5)

I used this as the basis of the solution, but then tested as
to
when
the
result of that was exactly divisible by 10
(MOD(CEILING(A1,5),10)=0).
Thus
any number thatn rounds up to a multiple of 10 passes this
test,
so
by
just
subtracting it -(MOD(CEILING(A1,5),10)=0), which takes one
away.
SO
anything
that is a multiple of 10 when rounded up to the nearest 5 gets
1
taken
away,. I included the test for the number being divisible by
10
without
rounding up to the nearest 5 (MOD(A1,10)<0) so that numbers
that
started as
multiples of 10, 1630 etc., didn't get changed to 1629. So
1628
round
to
1629, as does 1629, but 1630 stays at 1630, 1631 rounds to
1635.
The
AND
is
just to combine both tests and return a single TRUE/FALSE
result
which
the -
will negate (-1 or -0).

As for the second bit, I think you just need to change all
instances
of
A10
in my formula to D8/0.67.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in message
...
Bob,

I tried it and it worked, but to be honest with you I really
don't
understand what or how it is working can you explain for me.
This
formula
is
a bit advanced for me and I would really like to understand
it
so
I
can
use
it on a large spreadsheet. Also is there anyway to combine
the
formula
you
gave me with the one simple one that I had initially
"=D8/0.67".
Bob
you
are great!!
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Angie33" wrote in
message
...
Does anyone know how to use the Round Function or the
Ceiling
function
to
round up to a specific number? Example: I have the
number
1288
in D8
and
I
have a formula in E8 which says "=D8/0.67", the result
is
1922.
But
I
would
like "1922" to round up to "1925". Had it been "1927",
I
would
want
it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33

















  #27   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Roundup or Ceiling Function to round to a specific number

Bob, if you are out there I need your assistance once again. you created
this formula for me to have my numbers end in either a 5 or a 9 and it works
great, can this formula be altered to end in either 49 or 99, depending on
where the numbers fall. Example 419 rounded down to 399, 329 rounded down to
299, 339 rounded up to 349, 359 rounded down to 349 or 369 rounded up to 399.
Of couse the numbers will be larger. Any assistance I can get with this
would be great. Thank you.
--
Angie33


"Bob Phillips" wrote:

=CEILING(A10,5)-(AND(MOD(A10,10)<0,MOD(CEILING(A10,5),10)=0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Angie33" wrote in message
...
Does anyone know how to use the Round Function or the Ceiling function to
round up to a specific number? Example: I have the number 1288 in D8 and

I
have a formula in E8 which says "=D8/0.67", the result is 1922. But I

would
like "1922" to round up to "1925". Had it been "1927", I would want it to
round up to 1929. Can anyone tell me how to do this?
--
Angie33




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Round up to next half number rmb4253 New Users to Excel 7 March 23rd 06 10:51 AM
Force function to show positive or negative number? smoore Excel Worksheet Functions 3 March 3rd 06 09:34 PM
Function to Count Number of Consecutive Rows with a Specific Criteria? Templee1 Excel Worksheet Functions 2 July 10th 05 10:22 PM
Round a number in nested function kim Excel Worksheet Functions 1 July 6th 05 11:45 AM
Change a number to round up without a function teebee0831 Excel Discussion (Misc queries) 7 June 30th 05 05:55 PM


All times are GMT +1. The time now is 05:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"