11 Replies Latest reply on Jun 7, 2018 12:18 AM by Rajesh NV

    Calculation Field

    Rajesh NV

      Hi,

       

      I have data in the below format:

       

      Number          Domain          TableName          TableCount1          TableCount2          Count          CountStatus

      1                         A                    master                    10                         10                          Pass               Pass

       

      2                         B                    Dept                        20                         20                         Pass               Fail

                                 C                    Dept                        30                         30                         Fail

                                 D                    Dept                        40                         40                         Cond. Pass      

       

      3                         E                    Amaze                         40                        Fail                    Fail

       

      4                         G                    Sales                        50                         40                       Fail                  Fail

                                 G                    Sales                        50                         50                       Pass   

       

      5                         F                    Category                    60                        60                       Pass                Pass

                                 F                    Category                    70                        70                       Pass              

       

      Can anyone please help on how to achieve the count status.

       

      If count = pass, then countstatus = pass ;

      if count = pass, Fail, Cond.Pass then countstatus = Fail ;

      if count = Fail then countstatus = Fail ;

      if count = Fail, Pass  then countstatus = Fail

       

      Appreciate if anyone help on this!

       

      Regards,

      Rajesh

        • 1. Re: Calculation Field
          lovepreet kaur

          Hey Rajesh,

           

          can you please attach a sample workbook.

           

          Thanks ,

          Lovepreet

          1 of 1 people found this helpful
          • 2. Re: Calculation Field
            Deepak Rai

            Hi Rajesh,

            Please attach the above data as excel file.

            Thanks

            Deepak

            • 3. Re: Calculation Field
              Anisha Jhamb

              You can try

               

              IF ({FIXED [Number]: COUNTD([Count])} = 3) THEN 'Fail'

              ELSEIF ({FIXED [Number]: COUNTD([Count])} = 1)  AND ({FIXED [Number]: MAX([Count])} = 'Pass') THEN 'Pass'

              ELSEIF ({FIXED [Number]: COUNTD([Count])} = 1)  AND ({FIXED [Number]: MAX([Count])} = 'Fail') THEN 'Fail'

              ELSEIF ({FIXED [Number]: COUNTD([Count])} = 2)  AND ({FIXED [Number]: MIN([Count])} = 'Fail') THEN 'Fail'

              END

               

              If there are more scenarios, then you need more if conditions.

               

              Regards,

              Anisha Jhamb

              • 6. Re: Calculation Field
                Rajesh NV

                Sorry Anisha . Number field will be same for all.

                 

                Number          Domain          TableName          TableCount1          TableCount2          Count          CountStatus

                1                         A                    master                    10                         10                          Pass               Pass

                 

                1                         B                    Dept                        20                         20                         Pass               Fail

                                           C                    Dept                        30                         30                         Fail

                                           D                    Dept                        40                         40                         Cond. Pass     

                 

                1                         E                    Amaze                         40                        Fail                    Fail

                 

                1                         G                    Sales                        50                         40                       Fail                  Fail

                                           G                    Sales                        50                         50                       Pass  

                 

                1                         F                    Category                    60                        60                       Pass                Pass

                                           F                    Category                    70                        70                       Pass

                • 7. Re: Calculation Field
                  Deepak Rai

                  Here it is

                  • 8. Re: Calculation Field
                    Deepak Rai

                    I used the above Calc

                    If it Helps, Pl mark it Helpful and CORRECT to Close Thread

                    Thanks

                    Deepak

                    • 9. Re: Calculation Field
                      Rajesh NV

                      Sorry Deepak. Please find the attached updated sheet

                      • 10. Re: Calculation Field
                        Deepak Rai

                        Pl check sheet 7 in attached

                        1 of 1 people found this helpful
                        • 11. Re: Calculation Field
                          Rajesh NV

                          Great Deepak. Working perfect as expected. Great job!

                           

                          Regards,

                          Rajesh