SPREADSHEET
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. -- Steve Pont -- Steve Pont |
SPREADSHEET
"Steve" wrote...
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. |
SPREADSHEET
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) -- Steve Pont "Harlan Grove" wrote: "Steve" wrote... 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. |
SPREADSHEET
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 A1:D5 In cell B2 enter =MAX(0,B2-B4)+N(A3) and copy across through cells C2:D2 In cell B4 enter =MAX(0,B4-B2) and copy across through cells C4:D4 -- Regards Roger Govier "Steve" wrote in message ... 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) -- Steve Pont "Harlan Grove" wrote: "Steve" wrote... 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. |
All times are GMT +1. The time now is 01:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com