Aging by netting negatives in other age buckets for the customer

[Maniv]

Have a customer line items database. Have put an IF formula to generate a TAG
column for AGING BUCKET using if(date(2009,3,31)-A2<=30,"Current"....

When I run a pivot on this AGING BUCKET as the column object with customer
no as the row object, i get an aging report which has unbalanced NEGATIVES
in the current bucket (signifying the COLLECTION recd against an invoice)
with the INVOICES getting stacked up as huge POSITIVES in the older age
buckets.

Require a formula or VBA Code to get excel to netoff credits with debits for
a customer and give me a correct aging ...

 

[Sean Timmons]

What is your data field? Is it set to count, sum, average...?

 

[Maniv]

Hi Sean

The pivot formula / function is SUM.. The value field is AMOUNT..

 

[Sean Timmons]

So the issue is, the invoices are dated more than 30 days ago, but the
collections can be within 30 days, so how do you combine them to get a net by
account #, broken out by aging. The issue is the invoices are not tied to a
"Current" bucket based on their date. Is it possible to separate invoices
from collections as two different tables?

If so, you can use an advanced filter on the acct #, aging, and amount of
collection to get the total collection amt per acct by aging. You could then
add a column to the right to show invoice amount per acct #, use a SUMIF to
get your total invoice from the new invoices table. Then, antoehr column to
merely sum the invoice minus collection amount to get total.

Is that feasible?