Roundup to the nearest number that is divisible by 3

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone please tell me how I can roundup a number in a cell to the next number that is evenly divisble by 3?

For ex., cell A1 = 5081 which is NOT evenly divisble by 3, so I would want cell A1 to be 5082 (or 1694*3)

The part that is puzzling me is how I can refer to the number that already exists in the cell without causing an error or circular reference. I am sure I am overlooking the obvious at this point but frustration has taken hold and I am running out of time playing with this!

Your help is most appreciated!
BTW, the new site looks great!
 
How about if you divide your number by three first, then round it up to the next whole number, then multiply the result by three. I think that would work?
 
Oh by the way, the self reference wis unnecessary, just put your formula in another column and hide the original
 
Yes, but how can I write a formula in that same cell to do this?
You see, I am sent this s/s regularly by salesmen who write the order as customer dictates. But, for the purpose of production, I must change the units to accomodate how it will be shipped. So, if hte customer ordered 5081 units but it is shipped in packs of 3, I need to change the original order form to 5082.

Can I do this as a formula? Do I need a "blind column"?
 
I don't think you can simply format the number to the next multiple of three, so I think you do need to keep the original data in one place and the result in another, whether you do that by inserting another column, or overlaying with another sheet that cross references the original. The alternative would be to write some code to perform the transformation and insert it into the cell.
 
thanks, gentlemen!
I guess it only became clear to me as I was tryping the issue that I would have to create a hidden column.
But, oh so grateful for the formulas and the push in the right direction!!
 
Hi
not possible with formulas directly in the same cell. Either use a
helper column or you have to use VBA (using an event procedure for
this)
 
Can someone please tell me how I can roundup a number in a cell to the next number that is evenly divisble by 3?

For ex., cell A1 = 5081 which is NOT evenly divisble by 3, so I would want cell A1 to be 5082 (or 1694*3)

The part that is puzzling me is how I can refer to the number that already exists in the cell without causing an error or circular reference. I am sure I am overlooking the obvious at this point but frustration has taken hold and I am running out of time playing with this!
Click to expand...

Right click on the worksheet tab and select View Code.

Paste the following into the window that opens:

=====================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim c As Range

Set rng = [A:A] 'Change this to reflect the cells that you want to alter

If Intersect(Target, rng) Is Nothing Then Exit Sub

Application.enableevents = False

For Each c In Target

If c.Value > 0 Then c.Value = Application.WorksheetFunction.Ceiling(c.Value, 3)
If c.Value < 0 Then c.Value = Application.WorksheetFunction.Floor(c.Value, -3)

Next c

Application.enableevents = True
End Sub
=======================


Any number entered in column A (and you can edit that) will be rounded up to
the closest value divisible by three.

I was not sure what you wanted to do with negative numbers, so they are rounded
towards 0. If you want negative numbers to be rounded away from zero, change
the FLOOR to CEILING in that line.


--ron
 
Ron,
You are a dear, sweet man! What a help this was!!
I hope everyone has a wonderful weekend as I can do so now!!
--
Marcy


Ron Rosenfeld said:
Can someone please tell me how I can roundup a number in a cell to the next number that is evenly divisble by 3?

For ex., cell A1 = 5081 which is NOT evenly divisble by 3, so I would want cell A1 to be 5082 (or 1694*3)

The part that is puzzling me is how I can refer to the number that already exists in the cell without causing an error or circular reference. I am sure I am overlooking the obvious at this point but frustration has taken hold and I am running out of time playing with this!
Click to expand...

Right click on the worksheet tab and select View Code.

Paste the following into the window that opens:

=====================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim c As Range

Set rng = [A:A] 'Change this to reflect the cells that you want to alter

If Intersect(Target, rng) Is Nothing Then Exit Sub

Application.enableevents = False

For Each c In Target

If c.Value > 0 Then c.Value = Application.WorksheetFunction.Ceiling(c.Value, 3)
If c.Value < 0 Then c.Value = Application.WorksheetFunction.Floor(c.Value, -3)

Next c

Application.enableevents = True
End Sub
=======================


Any number entered in column A (and you can edit that) will be rounded up to
the closest value divisible by three.

I was not sure what you wanted to do with negative numbers, so they are rounded
towards 0. If you want negative numbers to be rounded away from zero, change
the FLOOR to CEILING in that line.


--ron
Click to expand...
 
Hi Marcy,

This is Raju. Here is the solution and procedure.

if you have value at cell A1 that is 5081
write this formulea at B1 which is =ROUND(A1/3,0)*3 which give you 5082 in
the B1 cell.

Hope i am clear to the point what Vaughan tries to explain

Thanks
Raju
 
Raju,

This is a 1 year old thread.

Mangesh


Hi Marcy,

This is Raju. Here is the solution and procedure.

if you have value at cell A1 that is 5081
write this formulea at B1 which is =ROUND(A1/3,0)*3 which give you 5082
in
the B1 cell.

Hope i am clear to the point what Vaughan tries to explain

Thanks
Raju

formula in another column and hide the original
up to the next whole number, then multiply the result by three. I think
that would work?
that already exists in the cell without causing an error or circular
reference. I am sure I am overlooking the obvious at this point but
frustration has taken hold and I am running out of time playing with
this!
Click to expand...
 
Back
Top