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

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!!!
- 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?
katie
Brass Contributor
Sep 27, 2017
Wow Sergei! That is incredible!

I copied and pasted, then deleted all of the spaces, and entered it into the J2 field. Can you help me pinpoint the error?