Need help in shortening formula.

[BigMoe]

*** I'm using Excel 2003 ***

This is more of a general question because I don't have the whole formula 100% figured-out, but I would appreciate some direction on how to either make the formula below shorter (I've run into the Excel limit) or go about it in a more efficient way (without VBA please). I'm a bit of a self-taught Excel weekend warrior, so I'm open to using a completely different formula if need-be.

=IF(SHEETOFFSET(-1,$D$169)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0), if(and(vlookup(sheetoffset(-1,$h$164),tables!$m$21:$n$36,2,0)<4,sheetoffset(-1,$d$170)="">0,SHEETOFFSET(-1,$H$175)>=1, SHEETOFFSET(-1,$D$168)>=SHEETOFFSET(-1,$H$168)),VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$A$1:$B$16,2,0), IF(AND(VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=4,SHEETOFFSET(-1,$H$165)="A",SHEETOFFSET(-1,$D$165)>0, SHEETOFFSET(-1,$H$175)>=1,SHEETOFFSET(-1,$D$168)>=SHEETOFFSET(-1,$H$168),SHEETOFFSET(-1,$D$170)>0), VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$A$1:$B$16,2,0),IF(AND(OR(VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=5, VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=6,VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$M$21:$N$36,2,0)=7), SHEETOFFSET(-1,$H$165)="A",SHEETOFFSET(-1,$D$165)>0,SHEETOFFSET(-1,$D$168)>=SHEETOFFSET(-1,$H$168),SHEETOFFSET(-1,$D$170)>0), VLOOKUP(SHEETOFFSET(-1,$H$164),Tables!$P$21:$Q$36,2,0),SHEETOFFSET(-1,$H$164)))))

I've read-up on "Named Formulas", but having some trouble putting it in practice. For example, I tried naming the following parts of the formula, but end up getting errors:
Trial #1 (SHEETOFFSET(-1,$D$169)...<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)

Trial #2 SHEETOFFSET(-1,$D$169)...<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)

Trial #3 IF(SHEETOFFSET(-1,$D$169)...<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)

Any tips, advice, or examples using the formula above would be of great help, thanks. </sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
</sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
</sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)<sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0)
</sheetoffset(-1,$h$173),vlookup(sheetoffset(-1,$h$164),tables!$d$1:$e$16,2,0),>

 

[elmer007]

What is the goal for this formula?

 

[excelmane]

Exactly! what is the problem statement?

 

[BigMoe]

This formula compares the current level of a student and goes through a set of complex variables to see if they will be promoted or demoted to another level.

I realize that the help I'm going to receive is on the general side, but that's fine. I just don't know how else to word long formulas like this since I'm not an advanced user. I'm reading-up on Named Formulas at the moment, but not having much success. Is there a way of using helper columns or additional tables to simplify things? Any pointing in a possible direction would be helpful.

 

[elmer007]

What are the criteria for promotion or demotion? For instance, Student Timmy should be promoted if column B is greater than 2 and column C is equal to Red, but not if Column D is equal to "F", etc.

What value should the formula return? Promote, Demote, No Action?

It may be complicated to describe, but it's even more complicated to solve without a description I'm afraid...