Applying a set value to columns until the value is reached

Dee Wills

New Member
Joined
Sep 13, 2018
Messages
5
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.

ABCDEETC.
1Deposit$25,000.00
2MonthJanFebMarAprEtc.
3Estimated Income by Labor Hours$10,000.00$12,000.00$8,000.00$7,500.00
4Adjusted Invoice AmountNo InvoiceNo Invoice$5,000.00

<tbody>
</tbody>

I'm open to other recommendations.
Thanks again.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
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"?
 
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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