1

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:

Worksheet Screenshot

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
0

The formula required is a slightly complicated array formula.

Here is your spreadsheet with the formula showing the expected results:

Screenshot of worksheet

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 ;-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.