
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
I have attached a spreadsheet that shows a summary of an account to date. I want to put in formulas to continue as new charges and payments occur. Thanks in advance  and I hope the upload worked!!
Similar Excel Video Tutorials
Excel
 Excel! Excel Is Fun! Excel Is Power! See a long formula that uses the IF, AND, MOD, ROW, RANDBETWEEN, and COLUMN functions. See how to add a Condition ...
Hide Bank Rec Formula
 Use 2 SUMIF functions to create to Bank Reconciling formula. Also: 1) Add Conditional Formatting with TRUE/FALSE formula that hides formula ...
Formulas Operators and Math
 The Excel Basics Series shows a systematic description of what Excel can do from beginning to end. #5 Video topics: 1)Excel operator symbols ...
Similar Topics
I am working on zero balance accounts.
I have the following fields:
Class
Total Charges
Total Payments
I ran a query to get Charges and Payments from Zero Balanced accounts but now need to group it by class and have a field that shows Payments/Charges for a percentage.
I can get the data but that's for each account within a class and I want it grouped by class.
When I take out the Payment/Charges field, then I get each class listed properly with TOTAL charges and TOTAL payments. When I add the calculated field, then I get a detail with dupliate classes with each line calulating my %.
Does this make sense?
Hi the
I have a business that I need to report (and pay) sales tax on. In my spreadsheet I have the following formula:
=SUMIF('Charges & Payments'!H3:H307,A4,'Charges & Payments'!E3:E307)
...where "A4" is the deposit number. This formula collects all the check amounts on the 'Charges & Payments' sheet for a particular deposit number and adds them together. A subsequent cell calculates the tax. Everything works fine.
Now I have a new condition. Some new clients have tax exemption status. On the 'Charges & Payments' sheet I can easily add a column to indicate whether a particular clients' check is taxable or not. However, I do not know how to test for the additional condition within the above formula.
I did come up with the following formula:
=SUMIF('Charges & Payments'!H3:H307,A4,(SUMIF('Charges & Payments'!D3:D307,"'N'",'Charges & Payments'!E3:E307)))
...to test for a new column that could have a "N" in it indicating tax exempt statue. Excel does not like it, although the reason is not clear. I also tried just using an "IF" in place of the inner or outer "SUMIF" and although I was able to get around the error messages, the result was incorrect.
Anyone have an idea?
Thanks  TomR
I have spreadsheet1 (Aging) with names and dates, Names can appear more than once. Spreadsheet2 (Customers) has a list of customers and formulas to calculate aging date. Customers appear only once. I want to match Aging names with Customer list then apply formula to caluculate aging date. Exp:Aging shows Customer Smith with a date of 3/01/09, Customer list shows Smith with a formula of B2+10. Column B is the date col. Answer would be 3/11/09. I have used VLookup. It returns the formula not the result.
Any help for this novice would be appreciated.
I would like a more detailed Aging of Account in Excel in a scenario like this: If I give advance to a customer and he clears his oustanding with supply of goods. Outstanding will be cleared on FIFO basis.
I want an aging analysis of the balance.
So I kind of have a strange situation and I'll do my best to explain.
This is what the formula needs to do:
We have 2 spreadsheets one with account numbers and destination companies the other with account numbers and aging. What I'm trying to do is write a formula in the spreadsheet that has the aging and remove any of the account numbers that have a destination company, or just puts a simple True or false so I can sort by that.
I know how to do a Vlookup to match the accounts numbers, but I'm not sure how to include the destination filed in the formula. I'm not sure if this was clear or not so if you need more information let me know. Thanks in advance for any help
I also attached the file all the information is on sheet one and sheet 2
Is it possible to create an IF formula that considers two cells in the logical argument? If so, how can it be done? If not, any suggestions?
I'm writing a formula to calculate a due date for payments. It must take into account the first two payment dates (they need to be 1114 days apart) as well as the number of days in the month. For example  recurring payments can't occur on any day past the 28th of the month because February only has 28 days. Lastly, the due date can't be more than fifteen days away from any of the payments (in either direction  late or early it doesn't matter). It just can't be more than 15 days.
Thanks for your help!
Hi All
I have a spreadsheet that I am currently using to input payments onto and then create a CSV file using VBA which is working well, but I now need to batch the payments together so therefore need to do a summary before saving as a CSV file.
The column headings are *Matter Number, *Payment Amount, *Beneficiary Account Number in columns A:C,
On the new summary page it should just show *Payment Amount and *Beneficiary Account Number, one line for each different account number and the payment amount should be the total for all payments being sent to that account.
Any help would be greatly appreciated.
Cheers
Mike
Hi All
I have a spreadsheet that I am currently using to input payments onto and then create a CSV file using VBA which is working well, but I now need to batch the payments together so therefore need to do a summary before saving as a CSV file.
The column headings are *Matter Number, *Payment Amount, *Beneficiary Account Number in columns A:C,
On the new summary page it should just show *Payment Amount and *Beneficiary Account Number, one line for each different account number and the payment amount should be the total for all payments being sent to that account.
Any help would be greatly appreciated.
Cheers
Mike
I have seen a number of ticket aging requests, and there was some good information in there, but I have so far not been able to find something that would help with my specific needs.
We have a report which contains some member information, including the date the date received. We would like to have a separate column which would express the ticket aging, in 30, 60, and 90 day intervals.
In this case, however, we do not want the aging to be from the date received, we would like the aging to be from the 1st of the month of the date received. So if the line is dated 2/27/2011, and it is now 3/1/2011, the record should be marked as being 30 days old.
I have attached a spreadsheet which will hopefully explain what I am talking about better.
Additionally, if there is a way that the entore row can change the background color based on this aging value, that would be great as well.
Thank you.
Daniel
Hello everyone,
I would like to use VB to update a sheet labeled "Summary". What I am looking for is if there is a value in row 2,columns AO of the "Upload" tab: paste this data into row 6, columns AO of the "Summary" tab. Then, this needs to be repeated for rows 3 of "Upload" and paste into row 7 of the "Summary" tab. Then this process needs to be repeated for all the data on the "Upload" tab.
My data probably will not go past row 30,000: however, I do not know that for sure. If there is an issue with the 65,536 rows in Excel (since we are starting 5 rows lower on the "Summary" tab), we can stop at any row higher than that. If it is easier, I can move the data on the "Upload" tab to begin in row 6. Just let me know if you would like me to do that.
Some examples of formulas I would use (if I cannot get the VB code) a
In Cell A6 of Summary: =IF(Upload!A2="","",Upload!A2)
In Cell B6 of Summary: =IF(Upload!B2="","",Upload!B2)  repeat this pattern until Column O
In Cell A7 of Summary: =IF(Upload!A3="","",Upload!A3)
Thanks,
Pete
Hi all,
I get a report every month with about 3000 entries which shows the progess of projects carried out and I have to work out how much money is owed from the % achieved on each project. Different Projects earn different amounts of money, the layout of the sheet is as follows (I have tried attaching part of the sheet with no luck):
Col A & B is first & last Name
Col C Is the Start Date
Col D Is the Planned end date
Col E is % complete
Col F is the Project Number being carried out
Col G is the Project Assessor
This is the information I recieve, what I have to manually input and what I would like to do is as follows:
Col H is the money owed for the full project (What I want is a lookup table to detirmine how much is to be awarded for each of the 10 or so different projects)
Col I Is the number of payments available (this is worked out by the number of months between the start and planned end date inclusive, i.e. jan 09 to July 09 = 7 months, Is there a formula to work this out?)
Col J is Col H divided by Col I
Col K is Payments YTD
Col L is Earned Current Month and it's where I really need help, What I want to be able to do is work out how many payments are due by the % of work carried out. All people get one payment at the start of the projects. then one each month if they have reached the required %, the % target depends on the number of months between the start and end date, for instance 4 months will means 1 payment every 25% but payments cannot be given in advance, so if one person completes a 6 month project in two months the most they can have is two payments, and once 100% is reached the final payment can only be given once the completion date is entered in the % complete column.
I hope this makes sense and I hope somebody can help me on this.
If you want to see the spreadsheet to help make sense please PM me and I will arrange something.
Thanks in Advance
I have a pivot table that has "date" as a row field and "sum of charges" in the data field.
In a macro I need to remove "sum of charges" and insert "sum of payments". Both Charges and Payments columns are included in the PT source. How may I do this in vb?
(I have an area on my worksheet referencing the PT and is the source for my chart).
Thanks very much..
I have a excel file with 8000 rows. It is an worksheet of AR invoice aging.
I want to be able to create spreadsheets for the following. 1. Total Company
Aging  (summary of this spredsheet) 2. Summary Aging per Customer #
(summary of each customer) I have a total of 400 customers in this file. I
don't know how to use VGA  just formuals. I can go the vlook ups, but I am
just a beginner with those. Any suggestions/ideas as to how I can manage
this data? If you have other ideas I would like to hear them. I know
someone out there has done this before. At my old job I had some programers
that could do this for me. Here I am that person if I want the report.
HELP!! Thanks!
EX: these are the column headings that I have. I have to manually age
into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 130 3160 6190 91120 >120
I'm having some trouble figuring out how to determine how many hrs were worked per file based on the attached spreadsheet. The spreadsheet is exactly how I get the data. For Underwriters the file is considered complete when it is moved to "Ready for QC" status and for Leads when it is moved to "QC Complete" status. I was going to pivot the Aging sheet to determine how many files were moved to "Ready for QC" status on a certain day or week for each Underwriter/Lead then divide it by the hours works. However, I can't figure out a way to make this automated to where I can pull in a new Aging report and Payroll report and have the calculations get updated. Any suggestions would be greatly appreciated. The spreadsheet attached is just a portion of the real spreadsheet. There are a little over 100 employees on the payroll sheet and roughly 26,000 rows on the aging sheet.
Hello everyone
I need to know how to sum the entries in one column that belong to duplicate data in another column. In my case, I have account numbers (many thousands of them) listed in one column with customer payments in another column. Account numbers may have only one payment for some customers or multiple payments on other customers (customers with multiple children have multiple payments but only one account number). I want to sum the payments of customers with multiple payments and have the sum total added to a new column in Excel. I am not great with Excel, so formulas would work better in my case than macros (never done one) or VBA (whatever that means) if possible. Many thanks in advance for your help.
I have a list of vendors, with corresponding data (account numbers and payments made) per vendor in a specific time frame.
For each vendor, I need to count how many times each account number appears. It's not the same as the number of payments, because each account no. can have multiple payments associated with it. So simply counting the account numbers for each vendor returns a much higher number.
In my attached sample, cell C31 is where I need the formula. In the data I show, there are 16 unique account numbers listed. So the formula should return a result of 16 in my sample.
Thanks!
Hello there
I'm looking for some help with the following scenario.
This spreadsheet has a date coloumn (i.e. A1:A200) and a payments coloumn (i.e. B1:B200), and the current date in C1.
I have been using the following formula to calculate payments received in the last month, but only on one worksheet.
What I need to do is extend this formula to cover payments recorded over several sheets (same coloumn layout on each sheet) within the same workbook and return the result in one cell on a summary sheet.
Code:
=SUMPRODUCT((TEXT(A1:A200,"mmmyy")=TEXT(C1DAY(C1),"mmmyy")),B1:B200)
Thanks in advance, any help greatly appreciated.
Hello,
I know there are many questions on this topic already, but I have tried the different formulas, and they are not working for me.
Basically I just need to do a simple aging for health claims, using the Date of Service (DOS) and paid amounts.
Example:
Patient DOS Paid Amount
John Doe 20100801 15.55
Jane Doe 20100601 62.00
All i want is a formula or solution to go through about 10000 claims like above and just sort the data into Current (030 days), 3160, 6190, 91180, 181360, over 360
Also, Column G has the Date of Service, Column P has the Paid Amount
and Columns R, S, T, U, V, W are the aging columns respectively.
THANK YOU IN ADVANCE!
Hello all... I have attached a spreadsheet that's sort of like a finances timetable. It shows the ups and downs of a balance throughout the year (including future balances based on projected payments). What I want to do is have a display on the top row that shows what my minimum future balance will be and what date that will occur. I have a cell right now showing the minimum of the column, but I want the range to be only from today forward.
The logic (in my head) would be:
1) find the minimum in column D,
2) compare the date in column A of that same row to today's date and see if it is equal or greater to today,
3) if the date in column A is greater than or equal to today's date, then display the balance and the balance date on the top row,
4) if the date in column A is less than today's date, then find the next lowest minimum balance in column D, repeat the same date check, and continue until the "greater than or equal to today's date" argument is satisfied.
I have no idea if this is possible within the confines of Excel, but if anybody had any ideas or suggestions, they would be most appreciated! Thanks!
Gary
I am preparing an analysis of my company's expenses for the last two years by account. One on excel sheet I have the raw data,
bank account, payee, payment date, clear date, amount, payment type
(This is from all accounts for the whole 2 year period.)
In the second sheet, I have a summary schedule setup. The summary is setup like this:
Account #
Row 1: Begin Date
Row 2: End Date
Payment Type 1 AAAAAAA
Payment Type 2 BBBBBBBB
I would like to come up with a formula that will populate AAAAA & BBBBB for a given account during a given time period. For example, I would like it to give me the total of Type 1 payments from account 12 during the period January 2, 2005 through January 27, 2006.
Right now it is only giving me the info for 1 payment type and 1 account. Here is what I have now {=SUM(IF(Detail!$G$10:$G$3942>=R$5,IF(Detail!$G$10:$G$3942
I use aging reports at work for accounts which I import into excel 2003 each month. These are worked according to amounts owed on the accounts and how far past due they are. A lot or most of these accounts are carried forward from last months report because they are still in aging again the next month. I would like to link the individual accounts either by workbook or worksheet when they are a repeat from the previous month or months report to avoid having to research the account anew.
This way I would know I worked on this account last month and would have access to the notes from the previous month and even though they are on a new report it would be like a continuation of work done on the account rather than having just this month's information
Need a formula to enter invoice amount in correct aging column by comparing invoice date to a random date entered in cell A1 to determine days outstanding. See attached worksheet.
Hi,
got little bit troubles with my work, hope somebody will help:
On the first sheet (payments) I've got several payments we have received (column A are payment IDs) with any values (column B).
On the second sheet (sums) I've got several summary invoices (column A = sum ID) for any values (column B).
For each summary value I need to get all of the combinations of payments that will correspond with the summary value with tolerance of + 1.00
Need to get back payment IDs and the sum of those payments.
I hope screenshots will help to imagine better. In the column C,D etc.. you can see possible right options.. the way i need to get back the result.
sheet (payments)
sheet(sums)
Here is the code, so you don't have to write the numbers manually:
Code:
payID payVAL
1 105,10
2 2 001,00
3 135,00
4 209,00
5 2 001,50
6 18 000,00
7 55,90
8 102,65
9 0,09
10 0,99
11 109,80
12 88,00
13 900,10
14 78,00
15 1 327,58
16 4 182,17
17 109,00
18 780,77
Code:
sumID sumVAL
1 4504,6
2 4451,6
ofc i will have there more than 2 summary values.. but this is just a short example.. ;o
I was looking at Microsoft's website and they had a nice amortization schedule (see attached) but I have a few problems and questions with it.
1. If you have a base year of 2005 and a base month of 6, the Payments in the First 12 Months box shows payments for January through May. That isn't right. If it started in June, there wouldn't be any payments right?
2. It would be nice if it had a drop down for principal and interest loan versus a interest only loan. I know there's 0 amortization for interest only but it would be nice to have a spreadsheet that adapts for that as well. Any ideas?
3. How can I make this schedule account for a mortgage that begins in the middle of the month? I would only want to pay half that month's payment. Not so worried about days but would like it to be able to account for a mortgage that begins in the middle of the month.
Hi,
I need to create a formula to look up 2 dates from another report and bring back a total $ for those within that date range.
Example is below...this is the summary sheet. On another report will have my data. If the amount billed was in Jan but received in march, it would go in the march column. Sort of like an AR aging.
Jan Feb Mar
Jan $50
Feb
Mar
Please help.
Thank you,

