Need help in shortening formula.

BigMoe

New Member
Joined
Nov 1, 2014
Messages
18
*** 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:

BigMoe

New Member
Joined
Nov 1, 2014
Messages
18
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
Joined
Aug 29, 2014
Messages
299
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...
 

Forum statistics

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

Some videos you may like

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...
Top