require:Need formula help

My email address is byumlee@hotmail.com

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!

=if(a1>X,120,0)

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

Hope this is what you want!

Carl

Originally Posted by SeattleBumm

My email address is byumlee@hotmail.com

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!

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


On Tue, 25 Jul 2006 09:04:02 -0700, SeattleBumm
<SeattleBumm@discussions.microsoft.com> wrote:

>My email address is byumlee@hotmail.com
>
>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!

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

"SeattleBumm" <SeattleBumm@discussions.microsoft.com> wrote in message
news:16FF3DBF-DAF3-4B33-8AEF-AA4B1DDD5791@microsoft.com...
> My email address is byumlee@hotmail.com
>
> 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!
>

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!

"SeattleBumm" wrote:

> My email address is byumlee@hotmail.com
>
> 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!
>

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



"stormy" wrote:

> 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":
<snip>
> 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!
>
> "SeattleBumm" wrote:
>
> > My email address is byumlee@hotmail.com
> >
> > 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!
> >