-1

I'm sure that this has an incredibly simple answer but I've looked over this formula for hours and can't figure out the error. Essentially what I want the formula to do is if F3 = "Freshman" then check if U3 is blank, go through N3 and enter a value, if U3 is not blank go through U3. Then if F3 != "Freshman" and V3 is blank, go through S3, if V3 is not blank, use V3. Maybe there is a less complicated way of going about this but I'm not sure.

=IF(ISBLANK($U3)=TRUE,IF($F3="Freshman",IF(N3>=Lists!$H$7,3,IF(N3>=Lists!$H$6,2.5,IF(N3>=Lists!$H$5,2,IF(N3>=Lists!$H$4,1.5,IF(N3>=Lists!$H$3,1,IF(N3>=Lists!$H$2,0.5,IF(N3>Lists!$H$8,0,""))))))),IF(U3>=Lists!$I$7,3,IF(U3>=Lists!$I$6,2.5,IF(U3>=Lists!$I$5,2,IF(U3>=Lists!$I$4,1.5,IF(U3>=Lists!$I$3,1,IF(U3>=Lists!$I$2,0.5,IF(U3>=Lists!$I$8,"")))))))),IF(ISBLANK($V3)=TRUE,IF(S3>=Lists!$H$7,3,IF((S3>=Lists!$H$6,2.5,IF(S3>=Lists!$H$4,1.5,IF(S3>=Lists!$H$3,1,IF(S3>=Lists!$H$2,0.5,IF(S3>Lists!$H$8,0,"")))))),IF(V3>=Lists!$I$7,3,IF(V3>=Lists!$I$6,2.5,IF(V3>=Lists!$I$5,2,IF(V3>=Lists!$I$4,1.5,IF(V3>=Lists!$I$3,1,IF(V3>=Lists!$I$2,0.5,IF(V3>=Lists!$I$8,0,"")))))))))

I know that there are a lot of nested if's which is probably what's tripping me up. If anyone could help it would be much appreciated.

4
  • How is it not working? Is it returning the incorrect value? An error? – Raystafarian May 22 '15 at 17:36
  • Excel is opening a pop up saying that they've found a problem with my formula. – user3295539 May 22 '15 at 17:38
  • If you would post a sample data and describe a bit the logic behind e may could help you to write a note simple formula. – Máté Juhász May 22 '15 at 19:00
  • The logic behind the formula is that there are 4 columns, one that has user inputted High School GPA, one with user inputted College GPA, one with university confirmed High School GPA, and one with university confirmed College GPA. We don't receive the university confirmed GPAs for a few weeks and so when those are received, I need the formula to update the values. (above a 3.7 = 3, anything below a 2.0 = 0). There is one column that says "Freshman" "Sophomore" etc. If it's "Freshman" I need the high school GPA. Does that make sense? – user3295539 May 22 '15 at 22:45
0

I have put your formula in notepad. Then, for every condition I started a new line and used the tab to sort out the different IF functions. This makes your problem easier to debug. I do not know how to upload the file unfortunately. I have found that the last ")" is one too many and that there are some inconsistencies in the formula.

For example in the second listing, last statement ends with S3>=lists$I$8,"") whereas in other listings it ends with S3>=lists$I$8,0,"").

In the third listing IF(U3>=Lists!$H$4,2 is missing.

If you want I can send you the file to see for yourself

0
0

Here it is in coded notation

=IF(ISBLANK($U2)=TRUE,          'if condition #1
 IF($F3="Freshman",             '#1 true condition
  IF(N3>=Lists!$H$7,3,
   IF(N3>=Lists!$H$6,2.5,
    IF(N3>=Lists!$H$5,2,
     IF(N3>=Lists!$H$4,1.5,
      IF(N3>=Lists!$H$3,1,
       IF(N3>=Lists!$H$2,0.5,
        IF(N3>Lists!$H$8,0,"")
       )
      )
     )
    )
   )
  ),
   IF(S3>=Lists!$I$7,3,            '#1 false condition
    IF(S3>=Lists!$I$6,2.5,
     IF(S3>=Lists!$I$5,2,
      IF(S3>=Lists!$I$4,1.5,
       IF(S3>=Lists!$I$3,1,
        IF(S3>=Lists!$I$2,0.5,
         IF(S3>=Lists!$I$8,"")
        )
       )
      )
     )
    )
   )
  ),                              'everything before the comma is one function
   IF(ISBLANK($V2)=TRUE,          'new #2 if condition
    IF(U3>=Lists!$H$7,3,          '#2 true condition
     IF((U3>=Lists!$H$6,2.5,
      IF(U3>=Lists!$H$4,1.5,
       IF(U3>=Lists!$H$3,1,
        IF(U3>=Lists!$H$2,0.5,
         IF(Lists!$H$8,0,"")      'here is an if without a test condition
        )
       )
      )
     )
    ),
     IF(V3>=Lists!$I$7,3,         '#2 false condition
      IF(V3>=Lists!$I$6,2.5,
       IF(V3>=Lists!$I$5,2,
        IF(V3>=Lists!$I$4,1.5,
         IF(V3>=Lists!$I$3,1,
          IF(V3>=Lists!$I$2,0.5,
           IF(V3>=Lists!$I$8,0,"")
          )
         )
        )
       )
      )
     )
    )
   )
  )
4
  • In this format, you're missing the IF(U3>=Lists!$H$5 condition – Engineer Toast May 22 '15 at 18:57
  • Also, did you mean IF(Lists!$H$8,0,"") or IF(U3>=Lists!$H$8,0,"") ? – Engineer Toast May 22 '15 at 18:58
  • Also, did you mean IF(N3>Lists!$H$8,0,"") or IF(N3>=Lists!$H$8,0,"")? – Engineer Toast May 22 '15 at 18:58
  • It should be IF(U3>Lists!$H$8,0,"") and IF(N3>Lists!$H$8,0,""). – user3295539 May 22 '15 at 22:44
0

Here's an alternative: Reduce the nesting.

I would recommend helper columns but it's not strictly necessary. With helper columns, the formulas would look like this:

[N]: {=IFERROR(CHOOSE(LARGE(ROW($H$2:$H$7)*(N3>=$H$2:$H$7),1)-1,0.5,1,1.5,2,2.5,3),IF(N3>=$H$8,0,""))}
[S]: {=IFERROR(CHOOSE(LARGE(ROW($I$2:$I$7)*(S3>=$I$2:$I$7),1)-1,0.5,1,1.5,2,2.5,3),IF(S3>=$I$8,0,""))}
[U]: {=IFERROR(CHOOSE(LARGE(ROW($H$2:$H$7)*(U3>=$H$2:$H$7),1)-1,0.5,1,1.5,2,2.5,3),IF(U3>=$H$8,0,""))}
[V]: {=IFERROR(CHOOSE(LARGE(ROW($I$2:$I$7)*(V3>=$I$2:$I$7),1)-1,0.5,1,1.5,2,2.5,3),IF(V3>=$I$8,0,""))}

=IF(ISBLANK($U3),IF($F3="Freshman",[N],[U],IF(ISBLANK($V3),[S],[V])))

Alternatively, if you have the space in your data, I would recommend adding the values 0.5 to 3 step 0.5 in the range J2:J7. It might make it easier to maintain as you could change the values - if there's ever a need - without touching the formulas. If you did, the formulas could be:

[N]: {=IFERROR(INDEX($J$2:$J$8,LARGE(ROW($H$2:$H$7)*(N3>=$H$2:$H$7),1)-1),IF(N3>=$H$8,0,""))}
[S]: {=IFERROR(INDEX($J$2:$J$8,LARGE(ROW($I$2:$I$7)*(S3>=$I$2:$I$7),1)-1),IF(S3>=$I$8,0,""))}
[U]: {=IFERROR(INDEX($J$2:$J$8,LARGE(ROW($H$2:$H$7)*(U3>=$H$2:$H$7),1)-1),IF(U3>=$H$8,0,""))}
[V]: {=IFERROR(INDEX($J$2:$J$8,LARGE(ROW($I$2:$I$7)*(V3>=$I$2:$I$7),1)-1),IF(V3>=$I$8,0,""))}

=IF(ISBLANK($U3),IF($F3="Freshman",[N],[U],IF(ISBLANK($V3),[S],[V])))

In all likelihood, these could be improved further. I hope I've at least given you an idea, though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.