|
5/13/2008 5:34:31 AM
Excel: Aged Accounts Receivable
Q: Can you tell me how/if it is possible to do the following? I've created a table which shows an invoice number, amount and date. I put in a formula which is "today's date" minus the invoice date to show how many days the invoice is past due, i.e. 1-15, 16-30, 31+. Is there a way to have the number move to a different column depending on what the number is, i.e. if the number is 35, move the number to the 16-30 column? Thanks!
A: Just put an IF function in your different columns to display the value if it falls in your date range. For example, in your "Less Than 30 Days" column, assuming DAYS LATE is in cell D1 and AMOUNT DUE is in C1, you could say:
=IF(D1 < 30, C1, 0)
Then you can make another cell to the right of that to show orders that are between 30 and 60 days late:
=IF(D1 >= 30 AND D1 < 60, C1, 0)
And so on.
I cover the IF function in detail in my Excel 201 class:
http://www.599cd.com?GOXL201
Permanent Link
Keywords: excel tips accounts receivable if function
|
