Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Top of the line difficult excel formula

I am in need of a more complicated formula.  I have updated a few things in the spreadsheet, so I am attaching the new one so you can understand what I am trying to ask.  I need Cell C16 in the Report tab to equal the amount of interest paid for the payment made on the "today's payment."  In the Data tab that would be any time that the "Interest Accrual Account" column goes from one $0.00 to the next $0.00.  For example: the payment on 11/18/2011, the interest paid would be the sum of Cell G62:G64.  But if we make payments on different dates, we would need the range of the interest to also change respectively.  Is there such a formula??
Copy-of-Amortization-on-a-Simple.xls
0
binaryman101
Asked:
binaryman101
  • 5
  • 3
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
Insert the following formula in cell C16:
=INDEX(Data!$B$16:$K$11015,MATCH(A16,$B$16:$B$11015,0)+7,6)+(INDEX(Data!$B$16:$K$11015,MATCH(A16,$B$16:$B$11015,0)+6,7))

This adds the interest on that payment date and the Accrued Interest up to that date (one day earlier, one column to the right).

0
 
Glenn RayExcel VBA DeveloperCommented:
Whoops...here is the corrected formula...I was referencing the incorrect range above.

=INDEX(Data!$B$16:$K$11015,MATCH(A16,Data!$B$16:$B$11015,0),6)+(INDEX(Data!$B$16:$K$11015,MATCH(A16,Data!$B$16:$B$11015,0)-1,7))
0
 
binaryman101Author Commented:
That almost worked the way i needed it to.  It needs to add one more line in the interest column to the total.  Also, when I changed the start date and end date in the report, it doesn't work properly.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Glenn RayExcel VBA DeveloperCommented:
The sum of values in cells G62:G64 on the "Data" sheet equals $102.76.  The above formula returns that value also (no other values changed).  Isn't that the correct result for 11/18?  For additional checks, I find that the value for 11/15 payment is $482.41

When I changed the End Date (cell B22 on the Report sheet), that only affected the output on the bottom. I'm not sure I understand why it would need to change the value in C16.

0
 
binaryman101Author Commented:
whoops, my bad i was trying to do too many things at once.  i think what you gave me works correctly.  I greatly appreciate the help.  i think i just miscalculated, probably from staring at spreadsheets all day.
thanks again
0
 
Glenn RayExcel VBA DeveloperCommented:
Okay...let me know if you need any other assistance.  I'm offline for the next few hours, but will check back later.
0
 
Glenn RayExcel VBA DeveloperCommented:
Hi binaryman101....did you have a chance to review my proposed solution again?  If so - and if it appears correct - please acknowledge.  Thanks! -Glenn
0
 
binaryman101Author Commented:
much obliged...worked great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now