Forum Discussion

Brass Contributor

Sep 27, 2017

Solved

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

SergeiBaklan

Diamond Contributor

Sep 27, 2017

Hi Katie,

Will it be correct to reword rule #5

---

5. If there is an amount in the J column, and there is “INV” in column G MAStat, it puts INV in J instead of amount; If no amount, leave blank

5. If one of prevous rules return any non-blank value for J and there is “INV” in column G MAStat, we put "INV" in J, otherwise the value returned by previous rules (some number or blank).

katie

Brass Contributor

Sep 27, 2017

Yes, that would be correct

SergeiBaklan

Diamond Contributor

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

katie
Brass Contributor
Sep 27, 2017
Disregard the error comment. It works PERFECTLY!! I cannot even begin to thank you enough! That is truly amazing! I am amazed!! THANK YOU SERGEI!!!