formula help
 

what formula is used when you enter a number into cell and it is round up to
a multiple of 3. So if 20 is entered in a cell that it is changes it to 21.
--
glen

formula help
 

Try

=CEILING(A1,3)

Mike

"Silverline" wrote:

what formula is used when you enter a number into cell and it is round up to
a multiple of 3. So if 20 is entered in a cell that it is changes it to 21.
--
glen

formula help
 

how do I protect the formula, when I put in the number 20 it erases the
formula.
--
glen


"Mike H" wrote:

Try

=CEILING(A1,3)

Mike

"Silverline" wrote:

what formula is used when you enter a number into cell and it is round up to
a multiple of 3. So if 20 is entered in a cell that it is changes it to 21.
--
glen

formula help
 

Hi,

You don't. The formula goes in a cell (say) b1 and your entered value goes
in a1.

If you want the actual cell you enter the value in to change then you need
VB code. Post back if that's what you want. Indicating which cells this
should apply to.

Mike

"Silverline" wrote:

how do I protect the formula, when I put in the number 20 it erases the
formula.
--
glen


"Mike H" wrote:

Try

=CEILING(A1,3)

Mike

"Silverline" wrote:

what formula is used when you enter a number into cell and it is round up to
a multiple of 3. So if 20 is entered in a cell that it is changes it to 21.
--
glen

formula help
 

On Tue, 31 Mar 2009 13:34:04 -0700, Silverline
wrote:

what formula is used when you enter a number into cell and it is round up to
a multiple of 3. So if 20 is entered in a cell that it is changes it to 21.

You can't do what you describe using a formula.

You would have to use an event triggered macro.

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.

Be sure to set AOI to the range where you want this to occur.

======================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Set AOI = Range("A:A")
If Not Intersect(Target, AOI) Is Nothing Then
Application.EnableEvents = False
For Each c In Intersect(Target, AOI)
If IsNumeric(c.Value) And Len(c.Value) 0 Then
c.Value = Application.WorksheetFunction.Ceiling _
(c.Value, 3)
End If
Next c
Application.EnableEvents = True
End If
End Sub
============================
--ron

formula help
 

thank you Ron I have it working on a range of cells example B12-B37 how do
now add N12:N37
--
glen


"Ron Rosenfeld" wrote:

On Tue, 31 Mar 2009 13:34:04 -0700, Silverline
wrote:

what formula is used when you enter a number into cell and it is round up to
a multiple of 3. So if 20 is entered in a cell that it is changes it to 21.

You can't do what you describe using a formula.

You would have to use an event triggered macro.

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.

Be sure to set AOI to the range where you want this to occur.

======================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Set AOI = Range("A:A")
If Not Intersect(Target, AOI) Is Nothing Then
Application.EnableEvents = False
For Each c In Intersect(Target, AOI)
If IsNumeric(c.Value) And Len(c.Value) 0 Then
c.Value = Application.WorksheetFunction.Ceiling _
(c.Value, 3)
End If
Next c
Application.EnableEvents = True
End If
End Sub
============================
--ron

formula help
 

On Wed, 1 Apr 2009 13:48:09 -0700, Silverline
wrote:

thank you Ron I have it working on a range of cells example B12-B37 how do
now add N12:N37
--
glen


Set AOI = Range("b12:b37,n12:n37")

--ron

formula help
 

Thankyou for your help.
--
glen


"Ron Rosenfeld" wrote:

On Wed, 1 Apr 2009 13:48:09 -0700, Silverline
wrote:

thank you Ron I have it working on a range of cells example B12-B37 how do
now add N12:N37
--
glen


Set AOI = Range("b12:b37,n12:n37")

--ron

formula help
 

On Thu, 2 Apr 2009 06:19:02 -0700, Silverline
wrote:

Thankyou for your help.
--
glen

Glad to help.

Thanks for the feedback.
--ron

formula help
 

I have run into problem Ron in the N column I have a formula in each cell and
the macro does not round the number up to multiple of 3. Is it possible to
do the formula in the cell and then round up to multiple of 3.
--
glen


"Ron Rosenfeld" wrote:

On Thu, 2 Apr 2009 06:19:02 -0700, Silverline
wrote:

Thankyou for your help.
--
glen

Glad to help.

Thanks for the feedback.
--ron

formula help
 

On Thu, 2 Apr 2009 08:31:02 -0700, Silverline
wrote:

I have run into problem Ron in the N column I have a formula in each cell and
the macro does not round the number up to multiple of 3. Is it possible to
do the formula in the cell and then round up to multiple of 3.
--
glen

I need more data.

If the *only* change you made in the macro was the range to which AOI is set,
(i.e. the set AOI = range("...") line, then the macro should be replacing the
formula with the value rounded up to the multiple of 3 (unless those cells are
not included in the AOI (Area of Interest).

So what exactly have you done, and what is happening?

--ron

formula help
 

What I have done Ron is entered the range of cells that it is required to
have the number round up to multiple of 3 witch is "B12:B37,N12:N37". The
formula required in cells N12:N37 is =sum (b12*N9)+B12. N9 is a percentage
addition but I want it to round up to muliple of 3.
--
glen


"Ron Rosenfeld" wrote:

On Thu, 2 Apr 2009 08:31:02 -0700, Silverline
wrote:

I have run into problem Ron in the N column I have a formula in each cell and
the macro does not round the number up to multiple of 3. Is it possible to
do the formula in the cell and then round up to multiple of 3.
--
glen

I need more data.

If the *only* change you made in the macro was the range to which AOI is set,
(i.e. the set AOI = range("...") line, then the macro should be replacing the
formula with the value rounded up to the multiple of 3 (unless those cells are
not included in the AOI (Area of Interest).

So what exactly have you done, and what is happening?

--ron

formula help
 

On Thu, 2 Apr 2009 12:58:01 -0700, Silverline
wrote:

What I have done Ron is entered the range of cells that it is required to
have the number round up to multiple of 3 witch is "B12:B37,N12:N37". The
formula required in cells N12:N37 is =sum (b12*N9)+B12. N9 is a percentage
addition but I want it to round up to muliple of 3.
--
glen

Why not just remove N12:N37 from the AOI range, and, in N12:N37, instead use
the formula:

=ceiling(b12*$N$9)+b12,3)

???
--ron

formula help
 

Excel tells me that $n$9 has to few of arguments. ???
--
glen


"Ron Rosenfeld" wrote:

On Thu, 2 Apr 2009 12:58:01 -0700, Silverline
wrote:

What I have done Ron is entered the range of cells that it is required to
have the number round up to multiple of 3 witch is "B12:B37,N12:N37". The
formula required in cells N12:N37 is =sum (b12*N9)+B12. N9 is a percentage
addition but I want it to round up to muliple of 3.
--
glen

Why not just remove N12:N37 from the AOI range, and, in N12:N37, instead use
the formula:

=ceiling(b12*$N$9)+b12,3)

???
--ron

formula help
 

I haven't followed your thread, but maybe:

=ceiling((b12*$N$9)+b12,3)


Silverline wrote:

Excel tells me that $n$9 has to few of arguments. ???
--
glen

"Ron Rosenfeld" wrote:

On Thu, 2 Apr 2009 12:58:01 -0700, Silverline
wrote:

What I have done Ron is entered the range of cells that it is required to
have the number round up to multiple of 3 witch is "B12:B37,N12:N37". The
formula required in cells N12:N37 is =sum (b12*N9)+B12. N9 is a percentage
addition but I want it to round up to muliple of 3.
--
glen

Why not just remove N12:N37 from the AOI range, and, in N12:N37, instead use
the formula:

=ceiling(b12*$N$9)+b12,3)

???
--ron


--

Dave Peterson

formula help
 

On Thu, 2 Apr 2009 14:14:10 -0700, Silverline
wrote:

Excel tells me that $n$9 has to few of arguments. ???
--
glen

You are misreading something.

$N$9 is a cell reference, and cell references cannot have "to few of
arguments".

A function could have too few arguments, but you should be able to read HELP
for that function to figure it out.

If not, please be more complete and accurate in your descriptions of what you
are doing and what is happening.
--ron

formula help
 

Your suggestion had mismatched ()'s.
=ceiling(b12*$N$9)+b12,3)

(I'm not sure how that message got generated, though <vbg.)



Ron Rosenfeld wrote:

On Thu, 2 Apr 2009 14:14:10 -0700, Silverline
wrote:

Excel tells me that $n$9 has to few of arguments. ???
--
glen

You are misreading something.

$N$9 is a cell reference, and cell references cannot have "to few of
arguments".

A function could have too few arguments, but you should be able to read HELP
for that function to figure it out.

If not, please be more complete and accurate in your descriptions of what you
are doing and what is happening.
--ron

--

Dave Peterson

formula help
 

On Thu, 02 Apr 2009 17:55:19 -0500, Dave Peterson
wrote:

Your suggestion had mismatched ()'s.
=ceiling(b12*$N$9)+b12,3)

Yes, I missed a parenthesis. But, as you wrote, that still shouldn't result in
a message of "$n$9 has to few of arguments."
--ron

formula help
 

-- Thank you Dave for your help it works great.
glen


"Dave Peterson" wrote:

I haven't followed your thread, but maybe:

=ceiling((b12*$N$9)+b12,3)


Silverline wrote:

Excel tells me that $n$9 has to few of arguments. ???
--
glen

"Ron Rosenfeld" wrote:

On Thu, 2 Apr 2009 12:58:01 -0700, Silverline
wrote:

What I have done Ron is entered the range of cells that it is required to
have the number round up to multiple of 3 witch is "B12:B37,N12:N37". The
formula required in cells N12:N37 is =sum (b12*N9)+B12. N9 is a percentage
addition but I want it to round up to muliple of 3.
--
glen

Why not just remove N12:N37 from the AOI range, and, in N12:N37, instead use
the formula:

=ceiling(b12*$N$9)+b12,3)

???
--ron


--

Dave Peterson