Excel Magic Trick 1128: Aging Accounts Receivable Reports On Multiple Sheets With Array Formula
ฝัง
- เผยแพร่เมื่อ 21 ต.ค. 2024
- Download Excel Start File: people.highlin...
people.highlin...
people.highlin...
people.highlin...
Download Excel File: people.highline...
Playlist for Array Formula Basics Videos: • Ctrl+Shift+Enter: Mast...
See how to create an automated Accounting Aging Accounts Receivable Reports On Multiple Sheets With Array Formula, Excel Table Feature and Formula Table Nomenclature (Structured References):
1. (00:16 min) Overview of end result: Automatic Aging Reports
2. (00:53 min) Convert Source Data Table (in separate workbook file) to an Excel Table and name the Table.
3. (01:55 min) Import Source Data into Report File
4. (03:05 min) Add Helper Column to Destination Excel Table in the Report Workbook file. First look at Table Formula Nomenclature (Structured References). The correct formula for Days Late is: =TODAY()-[@[Due Date]].
5. (03:45 min) COUNTIFS formula to count records that are between 1 and 30 days past due. Including syntax for greater than or equal to criteria when the comparative operator and a cell must be joined.
6. (06:04 min) Array Formula to extract the "Number of Days Late" information for each customer, given two criteria. See how to use LARGE function and IF function and the array formula key stroke: Ctrl + Shift + Enter. GOOD FOR EXCEL 2007 or EARLIER. Learn more about Table Formula Nomenclature (Structured References).
7. (09:58 min) See how to use IF function rather than IFERROR function to create efficient Data Extraction Array Formulas.
8. (11:13 min) Array Formula to extract the "Number of Days Late" information for each customer, given two criteria. See how to use the AGGREGATE function rather than the LARGE function to gain the benefits: 1) No need for special keystroke, 2) avoid errors from implicit intersection. GOOD FOR EXCEL 2010 or LATER.
9. (14:53 min) Array Formula to extract Customer Name and Invoice Amount using INDEX, AGGREGATE, IF, ROW, COUNTIFS and ROWS functions.
10. (21:01 min) Learn how to make Table Formula Nomenclature (Structured References) Absolute.
11. (23:30 min) Copy Array Formula Reprt to Multiple Sheets.
12. (24:35 min ) Test to see if data changed in the Source and Destination workbooks will be reflected in the Aging Reports automatically.
What is an Aging Report:
Aging means how many days past the due date the invoice is. Accounts Receivable Customers must pay their bill by a certain date, if they pay late, they would be listed in one of the aging reports. If they are 1-30 days late, they are listed in the first report. 31-60, the second. And so on. Further, once the customer is so late, like 300 days, the company stops wasting its time trying to collect and writes the Accounts Receivables amount as "Bad Debt" and runs that expense through the Income Statement.
![CAMPปลิ้น | EP.83[1/2] เปิดแคมป์หลอนต้อนรับ 2 ตัวพ่อแห่งวงการผี!](http://i.ytimg.com/vi/2uPuHYHgwiI/mqdefault.jpg)
Excel Magic Trick 1128: Aging Accounts Receivable Reports On Multiple Sheets With Array Formula
Download Excel File: people.highline.edu/mgirvin/ExcelIsFun.htm
Playlist for Array Formula Basics Videos: th-cam.com/play/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci.html
See how to create an automated Accounting Aging Accounts Receivable Reports On Multiple Sheets With Array Formula, Excel Table Feature and Formula Table Nomenclature (Structured References):
1. (00:16 min) Overview of end result: Automatic Aging Reports
2. (00:53 min) Convert Source Data Table (in separate workbook file) to an Excel Table and name the Table.
3. (01:55 min) Import Source Data into Report File
4. (03:05 min) Add Helper Column to Destination Excel Table in the Report Workbook file. First look at Table Formula Nomenclature (Structured References).
5. (03:45 min) COUNTIFS formula to count records that are between 1 and 30 days past due. Including syntax for greater than or equal to criteria when the comparative operator and a cell must be joined.
6. (06:04 min) Array Formula to extract the “Number of Days Late” information for each customer, given two criteria. See how to use LARGE function and IF function and the array formula key stroke: Ctrl + Shift + Enter. GOOD FOR EXCEL 2007 or EARLIER. Learn more about Table Formula Nomenclature (Structured References).
7. (09:58 min) See how to use IF function rather than IFERROR function to create efficient Data Extraction Array Formulas.
8. (11:13 min) Array Formula to extract the “Number of Days Late” information for each customer, given two criteria. See how to use the AGGREGATE function rather than the LARGE function to gain the benefits: 1) No need for special keystroke, 2) avoid errors from implicit intersection. GOOD FOR EXCEL 2010 or LATER.
9. (14:53 min) Array Formula to extract Customer Name and Invoice Amount using INDEX, AGGREGATE, IF, ROW, COUNTIFS and ROWS functions.
10. (21:01 min) Learn how to make Table Formula Nomenclature (Structured References) Absolute.
11. (23:30 min) Copy Array Formula Reprt to Multiple Sheets.
12. (24:35 min ) Test to see if data changed in the Source and Destination workbooks will be reflected in the Aging Reports automatically.
excellent
Glad you liked it, but didn't you mean:
EXCELlent!!!!
indeed
Mike Girvin !!! You're the best !!!!!! Man, you're real deal !!!!
As we say in France, "Chapeau bas "
Truly Appreciate !!!!! You're one of the major influence in my career.
STEPHANE NTONGA
Glad to help!
Taught me quite a few new things. Thank you so much.
You are welcome so much, Iqbal!!! Thank you for your support with your comment, Thumbs Up and your Sub : )
Awesome Mike with EXCELlent video on Aging accounts
Professor its fantastic
Glad the videos and files help, jyothi!!!! Thanks for the support of the excelisfun channel with your comments, Thumbs Ups and your Sub : )
I think it will be interesting If you do a new video for the same solution using the new dynamic arrays functions.
Hey there, great vid. Helped me out loads. But i was wondering how would you get the CUSTOMER NAMES and INVOICE AMOUNT if you are using excel 2007? Is the formula used the same?
Simply amazing
Thank you for sharing this awesome video, im just wondering if there is exact formula/process that I can organize my General Journal and linked it in different types of accounts/Ledger with the aid of links, for example if I will put a post reference in a certain journal it would automatically post in its respective ledger. thanks in advance
Another great video :) Love all your effort. Just wondering would it be possible combine Excel Magic Trick 907's Dynamic Calendar with Excel Magic Trick 1128 by adding the Names of the people to the calendar based on the invoices due date?
Thank you as always
I am not sure my small brain has envisioned such a great solution yet, but if you see it, WOW!!! That sounds amazing!! Given that Excel can do almost anything, I am sure that your creative idea would work!!!! Nice work, Excel Fan !!!!!
Mind. Blown.
Hello, I need help, I have monthly aging at work for AR. I am trying to find a more efficient way to work. For example, I have around 1000 rows and 12 columns ( acct #, company name, total, current, +15, +30, +45, and etc) . My task is to clean up accounts by manually moving credits forward. Is there a way I can sort my sheet by sorting it with multiple line data? I'm not sure what its called. Is there anything like that yet? For example, I have row #1 and they have 5 balances in 5 columns, and row #2 has 4 balances in 4 columns, and row #3 has 2 balances in 2 columns, and row #4 has 5 balances in 5 columns. I basically want my rows to be like #1, #4, #2, and then #3. Is there any way I can sort that? Is it possible? Can someone please tell me what is the correct term. It would make my job so much faster. Currently now, after I sort the data, I have to manually check to see which credits are more of a priority to move forward.
Nice work buddy,
Can you please explain how to create payable aging report having interest payment terms like 0/5, 1/15, 2/30?
Also assume prepayments and partial payments, if you can? :) ;)
THANX SUPERB
Great job Mike. Sent you an email also. 😊
Thanks!!
Why did you use a different formula to calculate the days late. I wanted to verify that I would get a different number of days since you prepared the video some weeks ago, and I am getting 155 as of today.
The correct formula for days late is (Today's Date) - (Due Date).
Great :-))
Thanks, Excel Master Bill Szysz!!!!
.i am from pakistan..plz help me...i have a master sheet of employees computer no like 1 to 100 employees .these employees working over different projects like 1 to 20 project A, and 21 to 50 project B and so on, so every project send me the overtime sheet of these employees at the end of the month. i have in master sheet only 3 column,employee no,name.overtime. .....so how i can put the overtime of these all projects in this one master sheet overtime column through vlookup? Many Thanks....
I am not sure. Try THE best Excel question site:
mrexcel.com/forum
que, que what?
23:30
Thanks for your loyal support : )
Hey there, great vid. Helped me out loads. But i was wondering how would you get the CUSTOMER NAMES and INVOICE AMOUNT if you are using excel 2007? Is the formula used the same?