# Formula to calculate flat-lined revenue over 12 months after a 1 month delay

I'm having trouble devising a formula enabling me to calculate the revenue based on delaying the revenue by 1 month and then flat-lining the revenue over 12 months.

This is my worksheet: The base data for 2018 orders are in cells `F6:Q9`.

The base data for 2019 orders are in cells `S6:AD9`.

Currently there is an order for Mar 2018 (cell `H6`) and the revenue profile should show a 1 month delay so there would be no revenue for April but from May 2018, the March order of £20,000 should start to reflect £1,667 (up until Apr 2019).

The formula would also need to take into account both the orders from `F6:Q9` (2018 orders) and from `S6:AD9` (2019 orders).

In cell `I1`, I have entered `1` (to reflect the one month delay).

In cell `I2`, I have entered `12` (to reflect the number of months the revenue needs to be flat-lined by).

The revenue profile starts at cell `AE6` and this is where I'm having trouble ascertaining what formula to use.

I tried to use an offset formula but I couldn't get it to work. I don't know how to approach the solution.

• can you share your sheet? – gamer0 Jun 8 '18 at 12:10
• Please upload the Sheet & the Formula you have used with !! – Rajesh S Jun 9 '18 at 6:39

The formula required is a slightly complicated array formula. This formula needs to be array-entered in `AE6` and then filled/copy-pasted down and to the right as far as required):

``````{=SUM(IFERROR(INDEX(6:6,N(IF(1,COLUMN()-(COLUMN(\$AE:\$AE)-COLUMN(\$F:\$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,\$I\$2))-\$I\$1))+(COLUMN()-(COLUMN(\$AE:\$AE)-COLUMN(\$F:\$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,\$I\$2))-\$I\$1>=COLUMN(\$R:\$R)))/\$I\$2,0))}
``````

Explanation:

The prettified version of the formula is as follows:

``````{=
SUM(
IFERROR(
INDEX(
(6:6),
N(IF(1,COLUMN()-(COLUMN(\$AE:\$AE)-COLUMN(\$F:\$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,\$I\$2))-\$I\$1))
+(COLUMN()-(COLUMN(\$AE:\$AE)-COLUMN(\$F:\$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,\$I\$2))-\$I\$1>=COLUMN(\$R:\$R))
)/\$I\$2,
0
)
)}
``````

The formula is a lot easier to understand if you consider that for a 12 month duration and a 1 month delay, the second argument of the first `INDEX()` is roughly equivalent to:

``````COLUMN()-(COLUMN(\$AE:\$AE)-COLUMN(\$F:\$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-1
``````

The formula basically works by generating an array of offsets to access the orders of the previous `I2` number of months, delayed by `I1` months, relative to the current cell.

Stepping through the formula in `AK6` should make the above clearer:

• `COLUMN(INDEX(6:6,1):INDEX(6:6,\$I\$2))`
`{1,2,3,4,5,6,7,8,9,10,11,12}`
• `COLUMN()-(COLUMN(\$AE:\$AE)-COLUMN(\$F:\$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-\$I\$1`
`{37}-({31}-{6})-{1,2,3,4,5,6,7,8,9,10,11,12}-1`
`{10,9,8,7,6,5,4,3,2,1,0,-1}`
• `N(IF(1,{10,9,8,7,6,5,4,3,2,1,0,-1}))`
`N({10,9,8,7,6,5,4,3,2,1,0,-1})`
`{10,9,8,7,6,5,4,3,2,1,0,-1}`
• `{10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>=COLUMN(\$R:\$R))`
`{10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>={18})`
`{10,9,8,7,6,5,4,3,2,1,0,-1}+{0,0,0,0,0,0,0,0,0,0,0,0}`
`{10,9,8,7,6,5,4,3,2,1,0,-1}`
• `INDEX((6:6),{10,9,8,7,6,5,4,3,2,1,0,-1})/\$I\$2`
`INDEX(6:6,{10,9,8,7,6,5,4,3,2,1,0,-1})/12``{24000,0,20000,0,0,"Opportunity Name1","bWmd1","Col C val","Col B val","Col A val","Col A val",#VALUE!}/12`
`{2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}`
• `SUM(IFERROR({2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!},0))`
`2666.67`

`INDEX(6:6,N(IF(1,expression)))` is a required hack* in order to force Excel to return an array for `expression` since the second argument of `INDEX()` is evaluated to a single value by default. Using just `INDEX(6:6,expression)` in `AK6` would lead to

`INDEX((6:6),COLUMN()-(COLUMN(\$AE:\$AE)-COLUMN(\$F:\$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,\$I\$2))-\$I\$1+(COLUMN()-(COLUMN(\$AE:\$AE)-COLUMN(\$F:\$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,\$I\$2))-\$I\$1>=COLUMN(\$R:\$R)))`
`INDEX(6:6,37-(31-6)-COLUMN(\$A\$6:\$L\$6)-1+(37-(31-6)-COLUMN(\$A\$6:\$L\$6)-1>=18))`
`INDEX(6:6,12-1-1+(12-1-1>=18))`
`INDEX(6:6,10)`
`24000`

since inside an expression returning a single value, `COLUMN(multi-cell-range)` returns the column of the first cell of the range.

The `+(COLUMN()-(COLUMN(\$AE:\$AE)-COLUMN(\$F:\$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,\$I\$2))-\$I\$1>=COLUMN(\$R:\$R))` adjusts for the gap between the order tables for 2018 and 2019 (column `R`). Note that the `N(IF(` hack is not required in this case as the previously used hack has already forced an array evaluation of the second argument of `INDEX` and thus the `COLUMN()` function evaluate to arrays.

The `IFERROR()` function is required in case the formula exists in a cell close to the left side of the sheet, resulting in either accessing text, or attempting to access a cell to the left of column `A`.

Notes:

• The prettified formula actually works if entered.
• The brackets around `(6:6)` in the prettified version are required to force the `6:6` to remain on its own line.

Caveats:

• There can be no numbers in the cells of the n columns to the left of the orders (where n is specified by the value in `I2`). If there are any numbers there, the formula, as is, will include them in the revenue calculation.
• There can be no gap between the Dec 2018 Revenue and the Jan 2019 Revenue columns. The formula can be modified to allow for such a gap if one is desired.
• The gap between the two orders tables (`R:R`) must remain exactly one column wide. Otherwise, the formula, as is, will break.
• The inter- orders tables gap cannot contain any numbers. Otherwise, they will be treated as additional orders.

* The explanation of exactly why the hack works will have to wait until I figure it out for myself first ;-)