formula help

[Silverline]

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.

 

[Mike H]

Try

=CEILING(A1,3)

Mike

 

[Silverline]

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

 

[Mike H]

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

 

[Ron Rosenfeld]

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

 

[Silverline]

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

 

[Ron Rosenfeld]

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

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

--ron

 

[Silverline]

Thankyou for your help.
--
glen

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

--ron

 

[Ron Rosenfeld]

Thankyou for your help.

Glad to help.

Thanks for the feedback.
--ron

 

[Silverline]

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.

 

[Ron Rosenfeld]

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.

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

 

[Silverline]

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.

 

[Ron Rosenfeld]

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.

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

 

[Silverline]

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

 

[Dave Peterson]

I haven't followed your thread, but maybe:

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

 

[Ron Rosenfeld]

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

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]

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

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

 

[Ron Rosenfeld]

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

 

[Silverline]

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