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 28, 2017

Hi Katia,

Thank you. With so long formulas better to keep it formatted, it'll be much easire to modify if needed. You may resize formula bar by mouse as large as necessary to see the formula

and when you don't work with it collapse formula bar (and expand back) with arrow at right side of it or by Ctrl+Shift+U

Formula bar is not the best place to edit such formulas, better somewhere outside, even in Notepad (better Notepad++), and in formula bar only to check if there are any errors.

And sorry for misprint - LEN(...) better to compare with zero if we exclude empty cells, not with 1. Other words

LEN(...) > 1
change on
LEN(...) > 0

katie

Brass Contributor

Sep 28, 2017

SergeiBaklan That information is very helpful! :)

I'm running through my procedure again today, and came across a little bump. When I entered the formula, it is giving Dealer 112 a commission, even though 112 should always come back blank regardless of the model or hardware link. Is this an easy fix?

SergeiBaklan

Diamond Contributor

Sep 28, 2017

Hi Katie,

Will check the logic tomorrow, finish for today in couple of minutes.

katie

Brass Contributor

Sep 28, 2017

Okay, thank you kindly. :)

SergeiBaklan

Diamond Contributor

Sep 29, 2017

Hi Katie,

It looks like your column B keeps texts and i compare with numbers - corrected. Also bit compacted the formula, hope works in Excel 2000

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

Attached is the file where i tested couple of rules (not all of them)

Rules.xls35 KB