Solved

Top of the line difficult excel formula

Posted on 2011-09-16
8
170 Views
Last Modified: 2012-05-12
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
Comment
Question by:binaryman101
  • 5
  • 3
8 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36551814
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36551827
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
 

Author Comment

by:binaryman101
ID: 36551853
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36551914
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:binaryman101
ID: 36551982
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36551997
Okay...let me know if you need any other assistance.  I'm offline for the next few hours, but will check back later.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 36560777
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
 

Author Closing Comment

by:binaryman101
ID: 36561273
much obliged...worked great
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now