Display the value of a cell depending on the value of another cell in the same row?

Jimmy

New member
Joined
Feb 21, 2013
Messages
3
Reaction score
0
Points
0
Here is my issue...

In a nutshell I have a spreadsheet made so you can enter the amount of someone’s debt and a proposed payment plan at the top of the sheet, then below a table shows all their future payments, calculated interest, payoff date, ext. What I am trying to do is, if someone sets up a payment plan that is less than what they are paying in interest and their payments pay the principle first, on what date their payments will start making a dent in the total balance. I have it so everything calculates but you have to scroll down a whole lot in order to see the date. I’d like to just have a cell at the top of the page that shows that date.

(When looking at hundreds of accounts this would save me tons of time)

Payment amounts are different for every account so I can’t just simply do a “=C38” I need something that can look at the values in Column G (which is showing the amount of interest in dollars) and find any values lower than the amount I would enter in "C2” (amount of proposed payment plan) “this part I can do”
Where I'm having a problem is once those values are identified I would like to display the values in C column (Due Dates) in the same row.

I've attached the file if that helps at all. (Look at Sheet 2!)


I’ve tried google and youtube but can’t seem to find the answer I may be asking the question the wrong way any help would be much appreciated.

Even if someone can help me just duplicate the whole row on the same page I can make it work in a round about way...

Thank you!
 

Attachments

  • int calc.xlsx
    239.9 KB · Views: 26
Last edited:

Jimmy

New member
Joined
Feb 21, 2013
Messages
3
Reaction score
0
Points
0
Sorry you will want to look at Sheet 2
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
This formula will give you the last date where the value in column G is greater than the value in C2:

=LOOKUP(2,1/($G$6:$G$1564>$C$2),$C$6:$C$1564)

If you want the first date that is lower than the value in C2, offset the last range in the formula above by 1...

i.e

=LOOKUP(2,1/($G$6:$G$1564>$C$2),$C$7:$C$1565)
 
Top