Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aging by netting negatives in other age buckets for the customer
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 ... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aging by netting negatives in other age buckets for the customer
What is your data field? Is it set to count, sum, average...?
"Maniv" wrote: 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 ... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aging by netting negatives in other age buckets for the custom
Hi Sean
The pivot formula / function is SUM.. The value field is AMOUNT.. "Sean Timmons" wrote: What is your data field? Is it set to count, sum, average...? "Maniv" wrote: 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 ... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aging by netting negatives in other age buckets for the custom
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? "Maniv" wrote: Hi Sean The pivot formula / function is SUM.. The value field is AMOUNT.. "Sean Timmons" wrote: What is your data field? Is it set to count, sum, average...? "Maniv" wrote: 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 ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to put data into different buckets. - PLEASE HELP | Excel Discussion (Misc queries) | |||
Netting off | Excel Discussion (Misc queries) | |||
Allocating usage across aging buckets | Excel Worksheet Functions | |||
CONVER CSV CUSTOMER DATA TO A CUSTOM INDIVI CUSTOMER PRICE SHEET | Excel Discussion (Misc queries) | |||
Netting off to zero | Excel Worksheet Functions |