im currently working on a spread sheet and was hoping someone would know a
formula so that when a number is less than 35, it will be automatically
rounded up to 35.
parts of the row have auto sum to total up this number ( being upto, or
actually 35) but i want it to change, for example, 32.4 into 35.
thanks guys

A formula cannot change the value of a number in the same cell as the
original number. You can use another cell to show 35 whenever the original
cell has a number less than 35. Otherwise, you have to use VBA
(automation). Paste the following macro into the sheet module of your
sheet. As written, this macro will do what you want with any number entered
into Column A. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Value < 35 Then _
Target.Value = 35
End If
End Sub
"Bro40dz" wrote in message
...
im currently working on a spread sheet and was hoping someone would know a
formula so that when a number is less than 35, it will be automatically
rounded up to 35.
parts of the row have auto sum to total up this number ( being upto, or
actually 35) but i want it to change, for example, 32.4 into 35.
thanks guys

Hi,

=if(A1<35,35,A1)

HTH

Simon

Bro40dz wrote:
im currently working on a spread sheet and was hoping someone would know a
formula so that when a number is less than 35, it will be automatically
rounded up to 35.
parts of the row have auto sum to total up this number ( being upto, or
actually 35) but i want it to change, for example, 32.4 into 35.
thanks guys

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200610/1

=MAX(A1,35)
--
David Biddulph

"Bro40dz" wrote in message
...
im currently working on a spread sheet and was hoping someone would know a
formula so that when a number is less than 35, it will be automatically
rounded up to 35.
parts of the row have auto sum to total up this number ( being upto, or
actually 35) but i want it to change, for example, 32.4 into 35.
thanks guys

In , Otto Moehrbach
spake thusly:

A formula cannot change the value of a number in the same
cell as the original number. You can use another cell to
show 35 whenever the original cell has a number less than
35. Otherwise, you have to use VBA (automation).

True enough. However, the OP might have a formula that
produces some number, and he could emend the formula to
produce the calculated number or 35, whichever is higher.

=IF(100/335,100/3,35)

(Substitute the actual formula for my example "100/3".)

I don't really know what the OP is asking, but I thought
I'd cover that possibility.

-dman-

----------------------------------------------------------
Paste the following macro into the sheet module of your sheet. As
written, this macro will do what you want with any number entered
into Column A. HTH Otto

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Value < 35 Then _
Target.Value = 35
End If
End Sub
"Bro40dz" wrote in message
...
im currently working on a spread sheet and was hoping someone would know a
formula so that when a number is less than 35, it will be automatically
rounded up to 35.
parts of the row have auto sum to total up this number ( being upto, or
actually 35) but i want it to change, for example, 32.4 into 35.
thanks guys

Point well taken. Thanks. Otto
"Dallman Ross" <dman@localhost. wrote in message
...
In , Otto Moehrbach
spake thusly:

A formula cannot change the value of a number in the same
cell as the original number. You can use another cell to
show 35 whenever the original cell has a number less than
35. Otherwise, you have to use VBA (automation).

True enough. However, the OP might have a formula that
produces some number, and he could emend the formula to
produce the calculated number or 35, whichever is higher.

=IF(100/335,100/3,35)

(Substitute the actual formula for my example "100/3".)

I don't really know what the OP is asking, but I thought
I'd cover that possibility.

-dman-

----------------------------------------------------------
Paste the following macro into the sheet module of your sheet. As
written, this macro will do what you want with any number entered
into Column A. HTH Otto

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Count 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Value < 35 Then _
Target.Value = 35
End If
End Sub
"Bro40dz" wrote in message
...
im currently working on a spread sheet and was hoping someone would
know a
formula so that when a number is less than 35, it will be automatically
rounded up to 35.
parts of the row have auto sum to total up this number ( being upto, or
actually 35) but i want it to change, for example, 32.4 into 35.
thanks guys