Need IF Statement, equity column and interest column

L

LeahAllen

New Member
Joined
Oct 18, 2013
Messages
8
I have an IF statement issue that looks at two separate columns, one column is equity into a project and the other column is loan into a project. The equity dollars must go into the project first and then the loan dollars would pick up. I need an IF statement that will automatically switch from equity to loan.

First IF statement is pulling loan column ($2,808,254.65): IF(B2>$H$1,A2-H2,0) It seems this formula should work correctly.
Second IF statement is pulling equity column ($1,203,537.71): First Row answer I have IF ($H$1<A2, $H$1, A2) This also seems to work correctly
and then starting with the 2 row I have =IF(($H$1>A$2), IF(A3>($H$1-SUM($A$2:A2)),($H$1-SUM($A$2:A2)), A3), 0) THIS ONE DOESN'T RETURN A $0 WHEN THE EQUITY HAS BEEN FULFILLED AND MOVES TO THE CONSTRUCTION LOAN, IT CREATES A NEGATIVE NUMBER.

I hope this makes sense, wish I could paste the entire excel document, would be easier for you to review. Thanks for any response.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="width: 165"]Total Development Costs[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="width: 115"]Cumulative Costs[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl65, width: 52"]Loan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] 2,808,254.65


[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl65, width: 141"] Cumulative Balance [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="class: xl65, width: 47"]Equity[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] 1,203,537.71


[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl65, width: 141"] Cumulative Balance [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 658,357.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115"] 658,357.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl65, width: 52, align: right"]3.50%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115"] 963,215.39 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl65, width: 52, align: right"]3.50%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115"] 1,268,073.09 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl65, width: 52, align: right"]3.50%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115"] 1,572,930.79 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl65, width: 52, align: right"]3.50%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115"] 1,877,788.48 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl65, width: 52, align: right"]3.50%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115"] 2,182,646.18 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl65, width: 52, align: right"]3.50%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl65, width: 165"] 304,857.70 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Try:
Code: 
=IF($H$1>A$2,MIN(MAX(A3,$H$1-SUM($A$2:A2),0)),0)
 
Upvote 0
=IF($H$1>A$2,MIN(MAX(A3,$H$1-SUM($A$2:A2),0)),0)

This almost works, it doesn't work for the month that is partial equity and then switches to the loan for the remaining amount. Any thoughts on how to expand the formula?
 
Upvote 0
I can't see anything on your sheet that implies date values - where are those found?
 
Upvote 0
Each row represents a month, in the third month I have cumulative spending that exceeds the amount of total equity required in the project. How would I expand the formula to pick up the spending up to the total amount of equity required and the remainder of the spending would then move to the construction loan column? Hope this makes sense, thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
574
STARL0RD
S
M
  • Solved
Indirect reference of 2 cells in "sheet1" to look up value in table "table1"
Replies
8
Views
129
Kaper
Kaper
A
  • Question Question
banding by value
Replies
4
Views
236
ajm
A
C
  • Solved
Newbie Alert: Column-sorting question
Replies
4
Views
181
JamesCanale
JamesCanale
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
838
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top