Need help with increasing number of Excel

Copper Contributor

Hi everyone. I need help with this formula:

=ROUND(IF(Q10>=555,(Q10-P10)*$P$28,IF(OR(AND(Q10<555,J10=0,H10=0),F10="vnl",F10="snl",F10="2sut",F10="pens",F10="iki 24m",F10="vaiko a"),(Q10-P10)*$P$28),IF(AND(Q10<555,P10>10),(Q10-P10)*$P$28+((555-Q10)-(555/$E$3*H10))*$P$28),IF(OR(AND(Q10<555),I10="",I10=0),(Q10-P10)*$P$28+((555- Q10)-(555/$E$3*H10))*$P$28,IF(AND(Q10<555,G10<555),(Q10-P10)*$P$28+(555-Q10)*$P$28,IF(OR(AND(Q10<555,J10=0,J10=""),I10="",I10=0,J10>0,H10>0),(Q10- P10)*$P$28+(555-Q10)*$P$28,0),2)

 

My Excel program do not let me to use 6 arguments. He just accepts 3 arguments. What should I do? Is it a chance to change something?

2 Replies

@LaimaA 

In your formula

=ROUND(
   IF(
      Q10>=555,
      (Q10-P10)*$P$28,
      IF(
         OR(
            AND(Q10<555,J10=0,H10=0),
            F10="vnl",
            F10="snl",
            F10="2sut",
            F10="pens",
            F10="iki 24m",
            F10="vaiko a"),
         (Q10-P10)*$P$28),
         IF(
            AND(Q10<555,P10>10),
            (Q10-P10)*$P$28+((555-Q10)-(555/$E$3*H10))*$P$28),
            IF(
               OR(
                  AND(Q10<555),
                  I10="",I10=0),
               (Q10-P10)*$P$28+((555- Q10)-(555/$E$3*H10))*$P$28,
               IF(
                  AND(Q10<555,G10<555),
                  (Q10-P10)*$P$28+(555-Q10)*$P$28,
                  IF(
                     OR(
                        AND(Q10<555,J10=0,J10=""),
                        I10="",
                        I10=0,
                        J10>0,
                        H10>0),
                     (Q10- P10)*$P$28+(555-Q10)*$P$28,0),2)

you close internal IF too early. If place brackets more correctly it works:

=ROUND(
   IF(
      Q10>=555,
      (Q10-P10)*$P$28,
      IF(
         OR(
            AND(Q10<555,J10=0,H10=0),
            F10="vnl",
            F10="snl",
            F10="2sut",
            F10="pens",
            F10="iki 24m",
            F10="vaiko a"),
         (Q10-P10)*$P$28,
         IF(
            AND(Q10<555,P10>10),
            (Q10-P10)*$P$28+((555-Q10)-(555/$E$3*H10))*$P$28,
            IF(
               OR(
                  AND(Q10<555),
                  I10="",I10=0),
               (Q10-P10)*$P$28+((555- Q10)-(555/$E$3*H10))*$P$28,
               IF(
                  AND(Q10<555,G10<555),
                  (Q10-P10)*$P$28+(555-Q10)*$P$28,
                  IF(
                     OR(
                        AND(Q10<555,J10=0,J10=""),
                        I10="",
                        I10=0,
                        J10>0,
                        H10>0),
                     (Q10- P10)*$P$28+(555-Q10)*$P$28,0
                  )
               )
            )
         )
      )
   ),
2)

@LaimaA 

Perhaps, you can shorten your formula to this: 

=ROUND(IFERROR($P$28*(Q10-P10)+IF(

OR(Q10>=555,AND(Q10<555,J10=0,H10=0),

F10={"vnl","snl","2sut","pens","iki 24m","vaiko a"}),

0,IF(

OR(AND(Q10<555,P10>10),

Q10<555,OR(I10={"",0})),

((555-Q10)-(555/$E$3*H10))*$P$28,IF(

OR(AND(Q10<555,G10<555),

AND(Q10<555,OR(J10={0,""})),

OR(I10={"",0}),J10>0,H10>0),

(555-Q10)*$P$28,0))),0),2)

Note that I modified AND(Q10<555,J10=0,J10="") in your original formula to AND(Q10<555,OR(J10={0,""})) because J10 can never be equal to 0 AND "empty text" at the same time.