SPREADSHEET

[Guest]

Want a cell to enter "0" for values = to 0 or <0, and then take the positive
difference in that same cell and put it into another cell.

ie: In the worksheet I have a descending loan balance that will be
progressively reduced to zero from positive cash flow until the the loan is
ultimately paid of. In the last month of paying off the loan, the amount
owed will be less than the positive cash flow for the month leaving a credit
balance instead of reducing the balance to "0'. if I can get the results in
the cell to show '0" I would then want the cash flow over and above the final
loan payment to go into another cell that would start accumulating the profit
or positive cash flow for the remainder of the project.

 

[Harlan Grove]

Want a cell to enter "0" for values = to 0 or <0, and then take the
positive
difference in that same cell and put it into another cell.

....

X is the balance, Y is the payment, use

=MAX(0,X-Y)

for the balance after applying the payment and

=MAX(0,Y-X)

for the credit balance after applying the payment. Prior to the last
payment, the former will be positive and the latter zero. Just after the
last payment, the former will be zero and the latter either zero or
positive.

 

[Guest]

New at this. Are both formulas in the same cell? And if they are, are they
in every cell say Jan - Feb, etc.
Jan Feb Mar
payment from closings 3000 4000 2500
gross profit 0 2000 4500
beg loan balance 5000 2000 0
end loan balance 2000 -2000(want this to be "0")
(want the 2000 to go into
gross profit Feb cell)

 

[Roger Govier]

Hi Steve

Since you want your Gross Profit to be accumulating, you need a slight
modification to one of Harlan's formulae to add the previous column's
value.

Assuming your data as shown is in cells A15
In cell B2 enter
=MAX(0,B2-B4)+N(A3)
and copy across through cells C22

In cell B4 enter
=MAX(0,B4-B2)
and copy across through cells C44