1 Reply Latest reply: May 15, 2014 10:03 AM by Data Kruncher RSS

    Is the If function the one for this problem?

    dbillingslea _

      I'm trying to create a calculation that looks at Debits/Credits 1-3 and adds the Disburse and Misc amounts to DR Output or CR Output when it finds accout no. 211001 in corresponding fields.  I tried using the "If" function to no avail.                                              

      Disburse     Misc     Debit1     Credit1     Debit2     Credit2 DR Output CR Output

      100     500     211001     101002     490101     490201        600               0

      350     750     610001     211001     490101     490201       1100            0

      275     450     610001     101002     211001     490201         725               0

      525     575     211001     101002     480101     211001              0       1100

      600     400     141001     101002     480101     610001              0              0

      200     300     141002     101002     211001     490201           500              0

        • Is the If function the one for this problem?
          Data Kruncher

          Capture your DR and CR accounts as Character fields. Build a new calculated Numeric field named DisbMisc, and use this expression:

           

          If("211001" .In. (DR1,DR2,CR1,CR2),Disb+Misc,0)[/code]Now create two new Numeric calculated fields for each of the final DR and CR amounts using:

           

          If(DROutput=0,0,DROutput+DisbMisc)[/code]and

           

          If(CROutput=0,0,CROutput+DisbMisc)[/code]If your actual report has three sets of DR and CR account numbers, just revise the first expression to:

           

          If("211001" .In. (DR1,DR2,DR3,CR1,CR2,CR3),Disb+Misc,0)[/code]

           

          I find that breaking up the logic like this makes it easier to manage.

           

          HTH,

          Kruncher