2

I'm building a spreadsheet that shows bonuses granted to an employee that vest and pay out spread over the following three years.

Easier just to see the example: https://docs.google.com/spreadsheets/d/1ZNLdun9PwSbfE6FSSmCatfqy2OQWp4rPKPujONbIoDg/edit?usp=sharing

In the first sheet, I have it working ok, but for every new year I have to manually place the {x/3, x/3, x/3} array formula into the correct year that vesting should start.

I want it work like the second sheet, where I just add new data to the green section, and the 3 payouts appear starting in the following year. In column C, I've calculated the offset where payout should begin. I just need a way for the array formula to start expansion at that offset.

Note that sometimes there's more than one grant per year, if that matters.

1
  • paste in C4 cell and drag down:

    =IF(LEN(B4), {REGEXEXTRACT(
     REPT(" ",   MATCH(DATE(YEAR(A4), 1, 1), C$3:$3, 0)), 
     REPT("(.)", MATCH(DATE(YEAR(A4), 1, 1), C$3:$3, 0))), 
     {B4/3, B4/3, B4/3}}, )

0

  • wow that's super cool. i had to simplify it down to ={REGEXEXTRACT(" ", "(.)(.)(.)"), B12/3, B12/3, B12/3} to understand what was happening here. the output of regextract is more array elements, each of them an empty space captured by the single character capture group. would be nice if sheets had some pythonic expansion like ={" "*3, "a", "b", "c"} or a function like ={EXPAND(" ", 3), "a", "b", "c"} – ack Apr 27 '19 at 4:53
1

Clear the middle C4:L16 area, paste this into C4, and drag-fill it over to L4:

=ARRAYFORMULA(IF(NOT(LEN($A4:$A16)),"",IF((C$3<=YEAR($A4:$A16))+(C$3>(YEAR($A4:$A16)+3)),"",$B4:$B16/3)))

In English:

  • In this column, but for each of the rows in the range A4:A16,
    • If that row's A isn't filled in, also output a blank here.
    • Otherwise,
      • If this column's row 3 year is either: the grant year or earlier, or more than three years after the grant year, also print nothing.
      • Otherwise, we're in a payout year, so output this row's B divided by three.

It should just keep working in the future, since it just uses dates in A, numbers in B, and years in row 3.

  • Let us know if your problem is solved, or comment if there are problems, or edit your question if you have more details. – Joel Reid Apr 26 '19 at 19:48
  • this didn't work as instructed but i follow the premise of each cell individually deciding if it should calculate a grant portion or not. cool approach! – ack Apr 27 '19 at 4:55

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.