Attention Master Excel Slayers:

Platform: MS Excel 2000 I don't even know if this is possible, but I know if it is, y'all will be the ones to make it happen. :) I need a formula created for column J Other Comm: 1. If B ...

Formulas & Functions

Need Help

SergeiBaklan

Sep 27, 2017

When better to start from the end (for cell J2)

=IF(
   AND(LEN(
   <previous rules>
   )>1,
      ISNUMBER(SEARCH("INV",$G2))),
   "INV",
   <previous rules>
)

where <pervious rules> are

=IF($B2=112,
   "",
   IF(
      OR(
         LEFT($B2,3)="305",
         LEFT($B2,3)="308",
         LEFT($B2,3)="311",
         LEFT($B2,3)="312",
         LEFT($B2,3)="313",
         LEFT($B2,3)="350"
      ), $H2*10%,
      IF(
         OR(
            LEFT($N2,2)="30",
            LEFT($N2,2)="40"
         ),"",
         IF(
            OR($F2="CM-3000",$F2="CM-4000"),
            "",
            $H2*5%
         )
      )
   )
)

and finally

=IF(
   AND(LEN(
   IF($B2=112,
   "",
   IF(
      OR(
         LEFT($B2,3)="305",
         LEFT($B2,3)="308",
         LEFT($B2,3)="311",
         LEFT($B2,3)="312",
         LEFT($B2,3)="313",
         LEFT($B2,3)="350"
      ), $H2*10%,
      IF(
         OR(
            LEFT($N2,2)="30",
            LEFT($N2,2)="40"
         ),"",
         IF(
            OR($F2="CM-3000",$F2="CM-4000"),
            "",
            $H2*5%
         )
      )
   )
)

   )>1,
      ISNUMBER(SEARCH("INV",$G2))),
   "INV",
   IF($B2=112,
   "",
   IF(
      OR(
         LEFT($B2,3)="305",
         LEFT($B2,3)="308",
         LEFT($B2,3)="311",
         LEFT($B2,3)="312",
         LEFT($B2,3)="313",
         LEFT($B2,3)="350"
      ), $H2*10%,
      IF(
         OR(
            LEFT($N2,2)="30",
            LEFT($N2,2)="40"
         ),"",
         IF(
            OR($F2="CM-3000",$F2="CM-4000"),
            "",
            $H2*5%
         )
      )
   )
)
)

- to check if all functuions are in 2000;

- text/number fields to be used correctly

Above is only from editor, didn't check with some values in Excel

Forum Discussion

Attention Master Excel Slayers:

Resources