5 Replies Latest reply on Sep 29, 2011 1:19 PM by philmodjunk

# formula needed for debit and credit calculation

### Title

formula needed for debit and credit calculation

### Post

I have a invoice that uses a portal to display line items. At times some line items could be a credit while other line items could be debit. I wanted to have a selectable drop down list for 'd' or 'c' but am stumped on a formula. I was thinking a case function or if but need help. I need to subtotal all the credits and subtotal all the debits with the formula.

Your help will be greatly appreaciated.

Thanks

• ###### 1. Re: formula needed for debit and credit calculation

Presumably debits will add to a total and credits will be subtracted?

If so,

If ( DropDownField = "c" ; -1 * Qty ; Qty )

will convert Credits into negative values. You can subsitute this calculation field in place of Qty in summary field definitions or calculation fields that refer to this field.

• ###### 2. Re: formula needed for debit and credit calculation

I get that part. I have most of it done. Your example is more elegant as right now I have been using a '-' in the product pricing. The real difficulty is subtotaling the credit and subtotaling the debit. The subtotal needs to come from my extendedprice field. If I have a subtotalcredit calc and a subtotaldebit calc how do I seperate the numbers appropriately?

• ###### 3. Re: formula needed for debit and credit calculation

If ( DropDownfield = "c" ; Qty * Price )

If ( DropDownField = "d" ; Qty * price )

You can now take the sum of these two fields to compute debit and credit subtotals.

• ###### 4. Re: formula needed for debit and credit calculation

I am trying to get this, sorry for the difficulty.

So, it seems like I would need 2 fields (extendedtotal_c and extendedtotal_d) both with the same calculations. I guess I could overlay them so they appear as 1 in browse mode. Is this what you figured?

I was kind of hoping for a subtotal calculation that could decipher the debit and credit. Using the above example, I am hoping for one calc to display \$240 and another to display (\$2430.56)

It does seem that my interpretation of what you suggested should work

Thank you

• ###### 5. Re: formula needed for debit and credit calculation

I don't see why you would put these two fields on any layout. They can stay hidden and you use the fields you already have for displaying the extended total.

Then one calc that sums the first of these hidden calculation fields will compute the total credits and a second will do the same with the second field to compute the total debits.