formula to round up any number less than 35 upto 35.

[Guest]

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

 

[Otto Moehrbach]

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

 

[smw226 via OfficeKB.com]

Hi,

=if(A1<35,35,A1)

HTH

Simon

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.aspx/ms-excel/200610/1

 

[David Biddulph]

=MAX(A1,35)

 

[Dallman Ross]

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/3>35,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-

----------------------------------------------------------

 

[Otto Moehrbach]

Point well taken. Thanks. Otto

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/3>35,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-