CODEWITHSUNDEEP
excelexcel-formula
Michi
Michi

Reputation: 5471

Dynamic starting point of OFFSET and SUM formula

I have the following Excel spreadsheet:

        A           B         C         D        E        F       G

1     Year         2015      2016     2017     2018     2019    2020

2     Revenue     5.000     4.000     6.000    

3     Years to go back:       2
4     Sum of Periods:      10.000

In Row 1 you can find the years 2015 til 2020 and in Row 2 the corresponding revenue of each year.

In Cell C3 the user should input the number of years to go back and sum up the revenues.
For example if the user puts in a 2 Excel goes back 2 years and sums up the revenue of 2017 and 2016 which is 10.000 in the case above. For this I used the following formula:

=SUM(OFFSET($E$2,0,-C3):$E$2)

This formula and the described calculation above work perfectly so far.

However, in 2018 I will have to adjust the starting point of Cell $E$2 in the formula above to Cell $F$2. Ohterwise the year 2018 will be excluded from the calculation.

=SUM(OFFSET($F$2,0,-C3):$F$2)

My question is now how can I avoid this permanent re-adjustment every year?

--> I think the solution should be a formula that identifies the first "non empty" cell within in a Row and then starts counting back the years from this cell. Somehow a combination of the SUM, OFFSET, ROW & COLUMN formula.

Upvotes: 0

Views: 2652

Answers (3)

Forward Ed
Forward Ed

Reputation: 9874

NON VOLATILE OPTION

Well non volatile if it were not for the TODAY() function. Replace TODAY() with a cell containing the starting year as a date. or replace Year(Today()) with a cell reference just containing the year (integer) and then it will be a non volatile option.

=SUM(INDEX($1:$1,MATCH(YEAR(TODAY()),$1:$1,0)):INDEX($1:$1,MATCH(YEAR(TODAY()),$1:$1,0)-($C$3-1))

Volatile functions recalculate every time something on the spreadsheet changes. Non volatile functions only recalculate when something affecting them changes.

The index function returns the cell address with in the range its looking. for a 2D range you need to give row and column reference. For a 1D range, you only need to give find the position within the range.

Match finds a value within a given range.

Upvotes: 0

Charles Williams
Charles Williams

Reputation: 23520

A simpler formula could be to use the 4th parameter of OFFSET to set the width and to calculate the starting point of the OFFSET using YEAR(TODAY()0-2016

=SUM(OFFSET($B$2,0,YEAR(TODAY())-$C$1,1,$B$3))

Upvotes: 0

Prisoner
Prisoner

Reputation: 1857

You may use this formula:

=SUM(INDIRECT("R2C" & (MATCH(YEAR(TODAY()),$1:$1,0) - $C$3 + 1) & ":R2C" & MATCH(YEAR(TODAY()),$1:$1,0), FALSE))

But be aware I'm assume the current year - $C$3 is within your data range.

Brief explain:

YEAR(TODAY()) - it will return the year of current date, then you don't need to re-adjust every year
MATCH(lookup_value, lookup_array, match_type) - it will find the value matched in first row, and return the index of cell
INDIRECT() - Form the result of match to a R1C1 notation, and convert the text to excel range

Upvotes: 1

Related Questions