MrExcel Publishing
Your One Stop for Excel Tips & Solutions

If Statements with 4 tests (2 tests include a range of numbers)

Posted by Neil on January 02, 2002 2:02 PM

I am having problems trying to figure out a nested If statement. Here is what I need to be returned in a cell.

>5:54 return a 4 in the cell
6:55 to 7:00 return a 3 in the cell
7:01 to 7:10 return a 2 in the cell
<7:11 return a 1 in the cell

Posted by Jacob on January 02, 2002 3:00 PM


Forget about nested ifs. They get tooooo messy. Cases do the same thing and are easier to visualise.

Sub MyMacro()
Dim MyValue
MyValue = the number you want or a range to where the number is.

'Your Code Here

Select Case MyValue

Case is <5:54
Case 6:55 to 7:00
case 7:01 to 7:10
case is > 7:11
case else
msgbox("I dont know what to do."),vbcritical,"Error"

'Your Code Here
End Sub


Posted by IML on January 02, 2002 3:35 PM

I assumed these times were minutes and seconds. I further assumed anything 5:54 and below should be n/a, and antything above 7:11 to get a 1.

assuming your time A1,

good luck