Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy spreadsheet data as shown to another spreadsheet? | Excel Discussion (Misc queries) | |||
In Excel I want to copy text from spreadsheet to spreadsheet | Excel Worksheet Functions | |||
convert ms works spreadsheet to excel spreadsheet on pda | Excel Discussion (Misc queries) | |||
conversion of MS Works Spreadsheet to Excel 2002 Spreadsheet | Excel Discussion (Misc queries) | |||
Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet? | Excel Discussion (Misc queries) |