Formula help

[AnnMitchell855]

=IF($G8="yearly",$Y8*(1+$H8),Y8*(1+$I8))

In this formula Y8 is a growth rate that changes every 12 months. How can i update the formula that it will move that cell reference every 12 cells? I have added year markers but not sure how to reference them

[#2]

@AnnMitchell855 

It probably would make more sense to use a table of annual growth rates and a VLOOKUP (or equivalent among the many ways Excel provides for looking up data) that retrieves the growth rate for a particular year.  That's often a preferred alternative to making an IF function more complex, especially where the conditions change on an ongoing basis.

It would be easier--far easier--for us to help you if you could share a copy of the spreadsheet itself. Especially if an altogether different approach is called for, seeing the bigger context would be invaluable. If you're able to do so, just drag and drop a copy of your workbook to the area below the text window (like the one where you first posted your question, or where you might reply to this). You'll see an area that looks like this:

If that doesn't work for you, put a copy of your workbook on OneDrive or GoogleDrive and paste a link here that grants access to it.

[#3]

@mathetes thanks for the response. I can't post the workbook as it is confidential unfortunately. i aready have a table of growth rates. I'm trying to make the schedule understand that if the year changes then to use the last entry of the preceding year as the basis. I'm trying to do this in a formula i can drag across rather than manually altering every 12 months

[#4]

@AnnMitchell855 

Attached is an example of how VLOOKUP can refer to a year and retrieve from a table the associated growth rate. Your application may well (probably does) require modifications to that, but it illustrates the use of a table and VLOOKUP

[#5]

I'm targetting the correct growth rate already - what i need to be dynamic is the base figure the rate is applied to. Thanks for trying to help

[#6]

So maybe my sample of a VLOOKUP will help. But without seeing what you're working with--how it's arrayed, etc.--one can only speculate what would help. Can you post a mock-up that replicates the layout without disclosing the confidential info?