Formula to select a cell when a condition is met etc

<<What I need is a formula that will input the figure of A4 in the cell adjacent to the year which matches the age input into B5.>>

This is difficult to understand. Can you copy some of the values in your columns, and then explain it?

Hi,

Try:

=IF(B8<$A$5,"",IF(B8=$A$5,$A$4,INDEX($G$8:$G$53,COUNT($C$7:C7))))
In C8 and drag down.

Fairwinds,
Thanks for posting the sheet.

To clarify position away from balance of $10000 in cell A4 and Age 58 in cell A5. I am looking for the A4 figure to transfer to cell C11 which will be the opening balance at age 58, and your formula does that part well

It would be ideal if all cells in D8:D10 and G8:G10 could be left blank.
This is first part of exercise in transferring opening balance to the cell adjacent to applicable age.

Next, if we assume that the closing balance in that year is $11000 (this is dictated by O.B.+ Income-drawings through the year) I want that to transfer to cell C12 as the opening balance for next year and so on to age 100.

Thanks again
Pedro

OK.

I understand now that you need a formula for D and G too, in the same manner. They should be calculated based on Income & Drawings. Where can I find those and how must I index them to find the year?

Fairwinds,
From your sheet

Column D is Opening Balance for each year from commencment. This is the same as my sheet
Column E is vacant whereas in my sheet it is for Earnings and there is a simply formula based around opening balance and a forecast earning rate on that balance; so no need for this to change.
Column F is also vacant but in my sheet it is for Drawings
Column G is the Closing Balance from 1st year to last year. The formula for this is simply the cell reference across from Opening balance and is as follows ,assuming cell D11 is the Opening balance (from A4)

=D11+E11-f11
This is dragged down to last row.

To clarify in Column C I have a "factor" which is used to calculate the Drawing figure (column F) and would prefer to leave that column as is, if possible.

So where you used C8 in your sheet if that could be D8 that would suit my sheet ideally.
Having said that if I need to add a row I can easily do that.

To summarise
I need to transfer the Opening Balance in A4 to a cell in column D in row that corresponds with the age in A5 and which are listed in Column B (from 55:100- as B8:B53
From there Column E handles the Earnings which are simply (O.B.*Earning rate ) and in sheet it would be =D11*Earning rate (a rate I have set in cell B4 and is for the full year, and subsequent years)
In Column F is the year's drawings and using D11 as the Opening balance the formula is simply =D11/C11 where C11 is the Factor I mentioned earlier.
Each year has a different factor and these of course are listed in C8:C53.
Column G is the closing balance which is calculated in G8 as =D8+E8-F8 .
If cell D11 contained the Opening balance (it would be that at age 58) the formula in G11 would be = D11+E11-F11.

In case you want to input some figures to authenticate:
The Factors in column C start at 19.8 (age 55)and reduce down to 15.7 at age 65
Drawings are based on Opening balance(columnD) /Factor (column C)
The earning rate in cell B8 could be set at 8%
Opening balance in cell A4 could be $50,000

Thanks
Pedro

OK we try again.

It would have been so much easier if you would have posted your sheet...

D8: =IF(B8<$A$5,"",IF(B8=$A$5,$A$4,G7))
E8: =IF(ISNUMBER(D8),D8*$B$4,"")
F8: =IF(ISNUMBER(D8),D8/C8,"")
G8: =IF(ISNUMBER(D8),D8+E8-F8,"")


Drag all these dowm

Fairwinds,

Once again thank you very much, and sorry i could not send copy of sheet.

It worked perfectly and leaves blanks, in prior years as I wanted.

You have been most helpful once again.
Thanks
Pedro

To complete the sheet which Fairwinds posted above, which is really working well , I would like the year to appear in Column A against the age shown in column B.

In sheet above the age of 59 is shown and what I want is for the year 2006 to appear in cell A12 with all years thereafter to age 100 shown below it.
In other words if someone aged 60 (at 30/6/2007) inputs 60 in cell A5 this not only triggers the worksheet to place the opening balance in cell C13 it also places 2007 in cell A13.
Subsequent years to be dragged down in need.

Thanks
Pedro

Try:

=IF(D8="","",YEAR(TODAY())+COUNT($D$8:D8))

In A8 and drag down.

Not sure why you want to start with 2006 though...