Applying a set value to columns until the value is reached

[Dee Wills]

Hi and thanks for helping.

I'd like to apply a known deposit amount (B1) to a set of projected income for the year (B3 - M3)
If the monthly income is less than the deposit a "No Invoice" is returned under the related month (example B4 & C4)
If the deposit is applied, but there is a remainder due, the adjusted invoice amount is returned (example D4 = $5000)
If there is no impact from the deposit cells remain blank (example E4 Apr)

Projects can start on any month.

[TABLE="class: grid, width: 960"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]ETC.[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Deposit[/TD]
[TD]$25,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Month[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]Etc.[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Estimated Income by Labor Hours[/TD]
[TD]$10,000.00[/TD]
[TD]$12,000.00[/TD]
[TD]$8,000.00[/TD]
[TD]$7,500.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Adjusted Invoice Amount[/TD]
[TD]No Invoice[/TD]
[TD]No Invoice[/TD]
[TD]$5,000.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm open to other recommendations.
Thanks again.

 

[Drrellik]

try placing something like this =IF(SUM($B$3:B3)<$B$1," No Invoice ",SUM($B$3:B3)-$B$1) in B4 and drag to the right. see if that is what you are looking for ~DR

 

[Dee Wills]

try placing something like this =IF(SUM($B$3:B3)<$B$1," No Invoice ",SUM($B$3:B3)-$B$1) in B4 and drag to the right. see if that is what you are looking for ~DR

Thank you Drrellik. This almost works.

Where I'm hoping for blank cells (i.e. April) the formula is a value. Should there be an if statement for if sum is greater than = "blank"?

 

[Drrellik]

Ok,

I think this might be what you are looking for

=IF(IF(SUM($B$3:B3)<$B$1," No Invoice ",$B$1-SUM($B$3:B3))<0,"",IF(SUM($B$3:B3)<$B$1," No Invoice ",$B$1-SUM($B$3:B3)))