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

# Is the If function the one for this problem?

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?

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