Displaying the result of a formula in a different cell

[gschimek]

I've got a spreadsheet that does amortization schedules for me. I pu
in the figures and it displays the appropriate monthly payments for th
appropriate number of months that I tell it to.

I've also got it set up so that if the calculated payment is $300/mont
for example, and I tell it I'm going to pay $350 a month, it will facto
that in. The problem is that is still goes for the predetermined numbe
of months, but the balance becomes a negative number towards the end.

What I'm trying to do is have a formula that will check to see if th
balance has reached a negative number, and display the appropriat
month number in a cell towards the top of the spreadsheet. Fo
example, if it's a 48 month loan, and I pay ahead and reach a zer
balance at 40 months, It would display 40 to tell me which month i
would be paid off in.

Is there a way, for example, to have the calculation for the balanc
say something like if(Value<=0,Cell1=Cell20,CalculateBalance)

I hope that makes sense. Basically I'm looking to do a formula in
cell, and based on the ouput of that formula, display a result in
different cell.

Any help is appreciated

 

[Frank Kabel]

Hi
no, not possible. A formula can only return a value but not change
other cells. But why not put this kind of formula directly in your
target cell?

 

[gschimek]

That'd be great, but I'm not sure how. I know I could write a formul
to check for a negative number and return the value of th
corresponding month, but that negative number could come on cell d20
or d50, or d75, etc. Is there a way to tell it to look down a colum
and return the first negative number it finds

 

[Frank Kabel]

Hi
one way: use the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX(D1100,MIN(IF(D1100<0,ROW(D1100))))

 

[gschimek]

Worked like a charm, with one minor adjustment. I used:

=INDEX(C1:C100,MIN(IF(D1100<0,(C1:C100))))

instead of

=INDEX(D1100,MIN(IF(D1100<0,ROW(D1100))))

Which returned the corresponding label in the cell next to the valu
(month number) instead of using the ROW(D1100), which returns th
spreadsheet's row number, regardless of what label you've give to tha
row.

Thanks for all the help