Need formula help

S

SeattleBumm

My email address is [email protected]

I want to have a cell such that if the number in the cell is greater than X,
the formula puts 120 in the cell.

Thanks for your help! Please send me an email if you know how to do this!
 
M

mr_teacher

=if(a1>X,120,0)

This assumes that your number is in cell a1 though. You can change th
number after the second comma to whatever you want.

Hope this is what you want!

Carl
 
G

Gord Dibben

Don't know what X represents, but assume it is 45

=IF(B1>45,120)

You posted here, so I'll answer here.


Gord Dibben MS Excel MVP
 
B

Bill Ridgeway

I don't think this can be done - in one cell - as you have asked.

You would need to use three cells.
a) to insert the value of X (and preferably defined by a name) A1
b) the user input cell A2
c) the output cell A3 with a formula along the lines of =IF(a2>a1,120,)

Regards.

Bill Ridgeway
Computer Solutions
 
S

stormy

You could write some VB to do this, however cumbersome it may be. Assuming
that the cell you want to become 120 is "A1" and the 'X' is in cell "B1":

' This event is called with the new selection; work with the previous
selection
Dim lastRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' lastRange is not set first time through
On Error Resume Next

' Is the the cell? (A1)
If (lastRange.Row = 1 And lastRange.Column = 1) Then
' ... if the number in the cell (A1) is greater than x (B1)
If (Range("A1") > Range("B1")) Then
' put 120 in the cell
Range("A1") = 120
End If
End If
' Restore error handling
On Error GoTo 0

' Keep whatever the current selection is
Set lastRange = Target
End Sub

Its up to you to detect the cell you want to change, to find the value for
X, to properly initialize lastRange, and to determine if this is more hassle
(and overhead) than its worth!
 
S

stormy

Slightly more refined

Dim lastRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If (lastRange.Address = "$A$1") Then
If (lastRange.Value > Range("B1")) Then
lastRange.Value = 120
End If
End If
On Error GoTo 0
Set lastRange = Target
End Sub
 
Top