1

Please see test file at this link. I have the following formula on a cell:

=IFERROR(IFS(AND(L5>=60%,L5<=79.99%),J5*132,L5>=80%,J5*150,$L$9>=80%,+2500),0)

For example, since L5 is 57% (no amt applied) but L9 is at 80%, it should add 2500.

Likewise, cell L6 is at 71% (multiplies amt in J6 by 132 = 660) but L9 is at 80%; it should be a total of 3160 and not 660 by itself.

The formula works perfectly but when it reaches the +2500 it doesn't add that amount if the cell L9 is over 80%. What could be wrong?


=IFERROR(IFS(AND(L5>=60%,L5<=79.99%),J5*132,L5>=80%,J5*150,$L$9>79.99%,M5+2500),0)

This is giving me a #REF!

Pic

1

Put the "if $L$9>79.99% add 2500" as an outer IF:

=IFERROR(
  IF(
    $L$9>79.99%,
    IFS(AND(L5>=60%,L5<=79.99%),J5*132,L5>=80%,J5*150)+2500,
    0),
  0
)

NOTE: The formula on E5 of the linked spreadsheet returns 0 because there isn't a match on IFS as 57.14% is not included on the IFS conditions. You have to decide if you add a condition to handle L5<60% or to replace the second argument of IFERROR.

7
  • That actually worked! It did add the 2500 to the existing amount, but only on the ones that had $660. It should also add it to the ones that even have $0.
    – A. C.
    Aug 3 '18 at 14:02
  • @A.C. Create a demo spreadsheet including a column with the correct result for each case, share the spreadsheet with anyone with the link to view only and add the link to the question.
    – Rubén
    Aug 3 '18 at 14:06
  • Created and added to the question. Thanks Ruben.
    – A. C.
    Aug 3 '18 at 14:23
  • @A.C. added a note to my answer.
    – Rubén
    Aug 3 '18 at 15:06
  • That's awesome Ruben! It works! I was able to handle the L5<60%. Formula ended as =IFERROR(IF($D$8>=80%,IFS(D4<60%,0,AND(D4>=60%,D4<=80%),B4*132,D4>=80%,B4*150)+2500,0),0) I appreciate it.
    – A. C.
    Aug 3 '18 at 15:41

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.