# Need help in shortening formula.

#### BigMoe

##### New Member
*** 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),>

Last edited:

#### elmer007

##### Active Member
What is the goal for this formula?

#### excelmane

##### New Member
Exactly! what is the problem statement?

#### BigMoe

##### New Member
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

##### Active Member
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...

1,081,528
Messages
5,359,305
Members
400,524
Latest member
Excelbat

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...