IF statements referencing an absolute value or a range of values
 

Does anyone know how to create a conditional statement for the following
situation?

If the value in a particular cell is between -5 and 5, then return a message
that says "ok", otherwise return a message that says "validation error".

Answer: IF statements referencing an absolute value or a range of values
 

Yes, you can use an IF statement in Excel to achieve this. Here are the steps:

1. Select the cell where you want to display the message.
2. Type the following formula:
   
   
   Formula:

   ---

   =IF(AND(A1=-5,A1<=5),"ok","validation error") 


   ---

   Note: Replace "A1" with the cell reference that contains the value you want to check.
   
3. Press Enter.
   
   The formula checks if the value in the cell is between -5 and 5 using the AND function. If the value meets the condition, it returns "ok". If not, it returns "validation error".
   
   You can also use absolute cell references in the formula if you want to apply it to multiple cells. For example, if you want to check the values in cells A1 to A10, you can use the following formula:
   
   
   Formula:

   ---

   =IF(AND(A1=-$5,A1<=5),"ok","validation error") 


   ---

   Note: The dollar sign ($) before the column and row references makes them absolute, so they won't change when you copy the formula to other cells.
   

IF statements referencing an absolute value or a range of values
 

If it is cell A1

=IF(A1=-5,IF(A1<=5,"Ok","Validation Error"),"Validation Error")

to make it a static reference add $'s

=IF($A$1=-5,IF($A$1<=5,"Ok","Validation Error"),"Validation Error")
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"skaminski" wrote:

Does anyone know how to create a conditional statement for the following
situation?

If the value in a particular cell is between -5 and 5, then return a message
that says "ok", otherwise return a message that says "validation error".

IF statements referencing an absolute value or a range of values
 

Look in the help index for AND and IF

--
Don Guillett
SalesAid Software

"skaminski" wrote in message
...
Does anyone know how to create a conditional statement for the following
situation?

If the value in a particular cell is between -5 and 5, then return a
message
that says "ok", otherwise return a message that says "validation error".

IF statements referencing an absolute value or a range of values
 

Try this:

If A1 contains the number to test

Then...
B1: =IF(ABS(A1)<=5,"OK","Validation Error!")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"skaminski" wrote:

Does anyone know how to create a conditional statement for the following
situation?

If the value in a particular cell is between -5 and 5, then return a message
that says "ok", otherwise return a message that says "validation error".

IF statements referencing an absolute value or a range of values
 

Public Sub validate()
Dim validation As Integer
Dim row As Integer
Dim col As Integer

row = 5
col = 1
validation = ActiveSheet.Cells(row, col).Value

If validation -5 And validation < 5 Then
MsgBox ("OK")
Else
MsgBox ("Validation Error")
End If

End Sub


skaminski wrote:
Does anyone know how to create a conditional statement for the following
situation?

If the value in a particular cell is between -5 and 5, then return a message
that says "ok", otherwise return a message that says "validation error".

IF statements referencing an absolute value or a range of valu
 

Thanks to both John and Ron for your quick responses! Both solutions worked
for me. Your help was much appreciated.

"John Bundy" wrote:

If it is cell A1

=IF(A1=-5,IF(A1<=5,"Ok","Validation Error"),"Validation Error")

to make it a static reference add $'s

=IF($A$1=-5,IF($A$1<=5,"Ok","Validation Error"),"Validation Error")
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"skaminski" wrote:

Does anyone know how to create a conditional statement for the following
situation?

If the value in a particular cell is between -5 and 5, then return a message
that says "ok", otherwise return a message that says "validation error".