Match? Index? Offset? HELP!

[Murphyk]

Hello, I'm hoping that someone can help. I have a business spreadsheet that I would like to update automatically. There are currently three calculations (a CAGR, a median and a sum in that order in the table below) performed on the past ten years of historic data. They only reference the historic data (i.e. 2016 and prior) rather than the forecasts, and it is easy to hard code the cells in each formula so that they work.

However, once a new year is added, I have to manually adjust each formula, as they should only perform the calculations on the past ten years (rather than including the forecasts). And there are dozens and dozens of them repeated for every division for every line of the accounts. And it's difficult to ensure that I've done it each time for every line!

The ideal is for Excel to recognise which column is the last year of historic data, and use that as the starting reference and go back 10 years to perform the calculations.

For a variety of reasons, I would prefer not to have excel use a data function to calculate the last historic year, but to recognise the that the last financial year does not have an 'e' next to the number. Or to introduce a specific reference for the latest financial data (a '1' entered once at the top of the column?). Whatever works, we can do that.

An example is below, and any help you can give would be wonderful.

Thanks.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017e[/TD]
[TD]2018e[/TD]
[TD]2019e[/TD]
[TD]2020e[/TD]
[TD]Calculation[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]85[/TD]
[TD]100[/TD]
[TD]90[/TD]
[TD]99[/TD]
[TD]98[/TD]
[TD]100[/TD]
[TD]105[/TD]
[TD]108[/TD]
[TD]115[/TD]
[TD]120[/TD]
[TD]121[/TD]
[TD]125[/TD]
[TD]130[/TD]
[TD]135[/TD]
[TD]4% (C.A.G.R)[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD]-82[/TD]
[TD]-85[/TD]
[TD]-92[/TD]
[TD]-88[/TD]
[TD]-89[/TD]
[TD]-90[/TD]
[TD]-90[/TD]
[TD]-91[/TD]
[TD]-92[/TD]
[TD]-93[/TD]
[TD]-95[/TD]
[TD]-96[/TD]
[TD]-96[/TD]
[TD]-99[/TD]
[TD]-90
(Median)[/TD]
[/TR]
[TR]
[TD]Profit[/TD]
[TD]3[/TD]
[TD]15[/TD]
[TD]-2[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]17[/TD]
[TD]23[/TD]
[TD]27[/TD]
[TD]26[/TD]
[TD]29[/TD]
[TD]34[/TD]
[TD]36[/TD]
[TD]128
(Sum)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Murphyk]

Apologies, it should say I would prefer not to have excel use a DATE function.

 

[Greg Truby]

Works in testing:


Let's say the range you posted was in cells A1:P4,


Then in A1 (or whereever would be out of the way) you could drop an array formula that would tell you the first cell in the year headers that has an "e" in it:


{=MATCH(TRUE,ISNUMBER(FIND("e",B1:ZZ1)),0)}


(Note that this is an array formula, i.e. confirm with Ctrl+Shift+Enter) [The use of "ZZ1" as the back end of the FIND() call was arbitrary -- that would cover you for the next 700 years. You could just as easily select a much closer column like "BZ1".]


Then you can use this in OFFSET() functions to fetch the cells you wish to use:


Your Median formula would be:
=MEDIAN(OFFSET($B$1,ROW()-ROW($B$1),$A$1-COLUMN($B$1),1,-10))


Your Sum formula would be:
=SUM(OFFSET($B$1,ROW()-ROW($B$1),$A$1-COLUMN($B$1),1,-10))

 

[Murphyk]

Thanks Greg for your swift response. The match array definitely works, but the others don't quite work for a reason I can't quite understand at the minute. They return values (which is better than I could manage!) but they're out for a reason I'm not sure on.

I'll test it over the weekend and come back to you with an explanation if that's OK. I imagine it's me not changing the formulas in the median and the sum correctly to ensure I get the 10 years in.

Cheers for your help so far.

 

[Greg Truby]

Use the Evaluate Formula tool on the Formulas tab to see where your other formulae are misbehaving.

 

[Murphyk]

That's wonderful Greg. Evaluate tool is a GREAT feature.

It shows that your formulae work perfectly, and my cross-check of simply summing them was wrong as I had dragged an additional cell into the reference! What an idiot.

The last thing I would like to be able to do is to work out the Compound annual growth.

The basic excel formula for that is (Latest year)/(early year)^(1/years)-1.

By messing around with the formula you have already helped with, I can return the latest year using the match array you supplied above and:

=OFFSET($A$6,ROW()-ROW($A$6),$A$1-COLUMN($A$6))

However, I've been unable to work out how to return an individual cell that is 10 years prior to the latest. I thought a -10 would work, but I must have the syntax wrong as it returns #ref! error. Can anyone help??

Thanks in advance.

 

[Greg Truby]

I believe what you'd need would look about like this:

=(OFFSET($B$1,ROW()-ROW($B$1),$A$1-COLUMN($B$1),1,1)/OFFSET($B$1,ROW()-ROW($B$1),$A$1-COLUMN($B$1)-9,1,1))^(1/9)-1

 

[Murphyk]

Re: Match? Index? Offset? HELP! v2.0

Hi,

As per the above, Greg kindly helped me sort out how to return the FIRST cell of forecast data by building a Match formula. The =(Match(true,isnumber(find("e",range)),0)) works perfectly, but I now have a follow on problem. I now need to return the column number of the LAST forecast year.

I can't simply add a constant number to the first formula as there will be a variable number of forecast years.

To complicate matters, it is possible there are other cells within the range off to the right hand side that contain plain text in, some of which may contain an "e". However, this will be a large amount of text, whilst our target cell will always have five digits, and be in the format 2018e or 2028e etc.

So I am trying to find a way to get the match formula to return the column reference for the last column with an "e" next to the year.

Hopefully, that makes sense. Can anyone help please?

Thanks