LinkBack URL
About LinkBacks
Hi, I'm having trouble figuring out the right IF formula to accomplish what I need. Details are explained in attached pic. As well, I've provided a file if someone is willing to look at this.
Thanks!
Hi, I'm having trouble figuring out the right IF formula to accomplish what I need. Details are explained in attached pic. As well, I've provided a file if someone is willing to look at this.
Thanks!
In cell I12, try:
Does that give you the result you're looking for?
Please Login or Register to view this content.
If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.
If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.
Hi Melvosh, I had a feeling you might show up for me again!
So, I tried your formula, which, from my understanding is similiar to the one I tried, and it is also giving a circular reference when copied to the upper cells.
The formula I tried (and is actually in D31) is =IF(E31>0,E30*($J$4/12),"") Your formula, =IF(J12>0.001,J11*($J$4/12),"") is only different in that (I think) your formula says if J12 (adjacent cell) is less than 0.001 ... where as the formula I tried is saying (I think) if E31 (adjacent cell) is less that 0. I think < stands for more than, and > more less than. Is that correct?
Anyway, the circular reference has me puzzled since the adjacent balance cell to the right of the interest cell is not involved in the calculation.
If you try your formula on the cells above the bottom interest cell in the spreadsheet I provided, you will see what I mean.
Anyway, as I was thinking through the meanings of the < & > I got an idea that worked, being: =IF(J11>H12,J11*($J$4/12),"") which translates to (I think): If the payment in the adjacent row is greater than (or equal to) the balance in the row above, then 0.
Perhaps you could enlighten me on what exactly the formula I just described, (and works perfectly) actually says...
Thanks again Melvosh, your reply did actually lead to the solution!
Bryan
Glad you found something that works. The reason the circular reference shows up in my formula is because it's basing the Interest column on the Balance column in the same row, and the Balance column is partially based on the Interest column in the same row. It works great if the FALSE condition is triggered, but causes a circular reference if the TRUE condition is triggered.
What your formula that works basically says is: If the Balance column in the previous row (J11) is greater than the Payment column in the current row (H12), run the formula ( J11*($J$4/12) ), otherwise return blank.
To answer your question regarding the signs, < tests for whatever is on the left side being less than whatever is on the right side. > tests for whatever is on the left side being greater than whatever is on the right side. Hope that answers your question
Thanks for taking the time to explain. Very helpful!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks