Help with inputting undefined cell

[josheeeee]

I want to make the cell I am referencing variable. I give out money on Month 1. And then I collect it in month X. So I want to be able to change X, and have excel know which $ amount in the cell X months ago to refer back to. If, for example, I collect the money on average 18 months, then it will look at the number in 18 cells before. If X is 12, it will look at the money I collected 12 months ago and then will look at the number in 12 cells back.

[bebo021999]

May the OFFSET can help:
=OFFSET(A19,-18,) will return the value/text in A1

[Moo the Dog]

Not sure I understand what you are getting at 100%, but say you have a value for Month 1 in cell A1, and a value in Month 18 in cell A18, and you want to look back a variable number of months to see a value, you could put the number of months you want to look back in another cell, say B1, and try this:

=OFFSET($A$18,-(B1),0)

- Moo

[josheeeee]

Thank you. But I want to be able to change the 18 in one place. So is there a way then to reference the 18 to another cel. Something like =OFFSET (A19, -Z39) and Z39 has the number 18 in it.

Thanks again!

[Moo the Dog]

Read my post (post #3)

- Moo

[OfficeCoach]

You could use offset (which will go find a cell value a certain number of rows or cols away). Use -negative numbers to go "back". So for example

Please Login or Register  to view this content.

Would return the value of the cell that is -(F22) rows back from G23 (in the same column because we stated zero). If cell F22 contains 12 this will return the value of cell G11.

Hope that makes sense!

[josheeeee]

Let me try and explain in business terms what I am trying to do, as I am new to excel and appreciate this forum and your help.

I loan out money to people and charge them interest. I charge 1 rate the first year, and another rate the second year. I am trying to show different scenarios of when I collect the money back on average. I want to be able to change the average up and down and see how that affects my whole formula. But my loan amounts are different every month. So in Month 1 I lend out $1,000 in A1 of excel, and Month 2 I lend out $1,500 and put that number in A2 of excel. If If I am trying to figure out what I collect in month 18, lets say it should be multiplying the interest rate times the principal I loaned. So if it was a 18 month loan it should multiply by A1. If its a 17 month loan it should multiply by A2. If it was 16 month loan it should multiply by A4. Ideally, i'd have the ability to change the avg loan period and have everything adjust as a result. Does that make sense?

[josheeeee]

Figured it out. Many thanks guys!