4 Replies Latest reply on Jul 13, 2018 5:12 PM by Tony Fahd

    if function with and?

    Tony Fahd

      so i am attaching a workbook where i want to classify my clients into 4 categories:

       

      first category:

      if the type is car and id is either 273 or 274 then PP

      if the type is car and client id is between 93650 and 93657 then PP

      if the type is care and client id between 93600 and 93644 then PP

       

      second category:

      if the type is car and id is between 215 and 272 then SP

      if the type is car and client id is between 33010 and 37799 then SP

       

      third category:

       

      if the type is car and id is between 280 and 316 then M

      if the type is car and client id is not in between 10021 and 69990 then M

       

      fourth category:

      Other

       

      the issue i have is that have lot of rows where id and client id are 0 so for example customer rt5a has an id of 273 and should be classified as PP but since he has lot of rows where id and client id are 0 he 's counted in both category PP  and other which i don't want this to happen.

       

      any help is greatly appreciated.

       

       

       

       

        • 1. Re: if function with and?
          Shinichiro Murakami

          HI Tony,

           

          not exactly sure,

          but if you need to assign each customer to one category, you need to add one more calc, for example,

           

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: if function with and?
            Tony Fahd

            Shinichiro, this is exactly what I needed. Amazing!

            • 3. Re: if function with and?
              Shinichiro Murakami

              Hi Tony

               

              You are welcome.

               

              Shin

              • 4. Re: if function with and?
                Tony Fahd

                Hi Shinichiro, i have a question, i changed the logic for my categories and I can't write the correct two functions you wrote for this post. my new categories are below:

                 

                 

                first category: didn't change

                if the type is car and id is either 273 or 274 then PP

                if the type is car and client id is between 93650 and 93657 then PP

                if the type is care and client id between 93600 and 93644 then PP

                 

                second category: it changed

                if the type is car and id is 215  then SP

                if the type is car and id is between 222 and 227 then SP

                if the type is car and id is between 230 and 272 then SP

                if the type is car and client id is between 33010 and 33249 then SP

                if the type is car and client id is between 33267 and 36598 then SP

                if the type is car and client id is between 36800 and 37799 then SP

                 

                 

                third category: didn't change

                if the type is car and id is between 280 and 316 then M

                if the type is car and client id is not in between 10021 and 69990 then M

                 

                Fourth category: New

                if the type is car and id is between 216 and 221 then V

                 

                Fifth category : New

                if the type is car and id is either 228 or 229  then Ma

                if the type is car and client id is between 33250 and 33266 then Ma

                 

                 

                Sixth category:didn't change

                Other

                 

                your help is greatly appreciated