Too Many IF Statements

[JJL]

We try to bring equipment on when scheduled. A scoring system is used to rate how close we meet this schedule:
<TABLE style="WIDTH: 237pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=316 border=0 x:str><COLGROUP><COL style="WIDTH: 189pt; mso-width-source: userset; mso-width-alt: 9216" width=252><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 22.5pt" height=30><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 189pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=252 height=30>Scoring</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Points assigned</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>up to 2 hours late and 4 hours early</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2-3 hours late or 4-5 hours early</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" x:num>3.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3-4 hours late or 5-6 hours early</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>4-5 hours late or 6-7 hours early</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" x:num>2.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>5-6 hours late or 7-8 hours early</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>more than 6 hours late or more than 8 hours early</TD><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" x:num>1</TD></TR></TBODY></TABLE>

The problem is, there are too many conditions for your typical IF statement. I am using Excel 2003 and it keeps stopping me at 4 'IF(AND(OR' statements. Here is what I have so far
=IF(AND(H15<"2:00",H15<"-4:00"),"4",IF(AND(OR(H15>"2:00",H15<="3:00"),H15<="-5:00"),"3.5",IF(AND(OR(H15>"3:00",H15<="4:00"),H15<="-6:00"),"3",IF(AND(H15>="4:00",H15>="-8:00"),"1","y"))))

Can someone please lead me in the right direction?

 

[Andrew Poulsom]

Use a lookup table:

http://www.contextures.com/xlFunctions02.html

 

[konew1]

My recollection was that you could have 7 nested if statements in excel 2003. Why do you have inverted commas (quote marks) around the values being checked and the values to be returned? If the data is numeric then they aren't needed.