A recent question from a CPA in Canberra allowed me to combine the MOD function and helper cells to offer a solution.

The image below is the structure that we needed to populate.

The entries are keyed in, but we need to automate the allocation with a formula.

You can download an example file at the bottom of the post.

The date in column C determines which financial year is the first allocation. The number of years in column B determines how many years between allocations.

The structure made it difficult to perform a straightforward calculation. To make the final formula easier I inserted an extra column (column D) and inserted an extra row (row 1). See image below.

This extra row and extra column contain what I refer to as helper cells. These are extra cells that contain simple formulas that can make the final formula easier to create and understand.

The formula in cell D3 is

=YEAR(C3+184)

This formula has been copied down and calculates the financial year of the start date. This is a hack l learned from Matt Allington .

We can use this financial year to compare with the financial years in row 1.

The formula in the cell F1 is

=RIGHT(F2,4)*1

This formula can be copied across and extracts the last four characters from the heading and converts it into a number by multiplying by one. When you use the RIGHT function it returns text. To convert a text number into a real number you can multiply it by one.

With the helper cells populated we can create the formula in cell F3.

=IF(AND(F$1>=$D3,MOD(F$1-$D3,$B3)=0),$E3,0)

We need to check two conditions.

The AND function first checks to make sure the financial year in  row 1 is greater than or equal to the year in column D.

The second condition uses the MOD function to determine in which year to allocate based on the starting financial year and the financial years listed in row 1.

Both conditions have to be met for the AND function to return TRUE.

The MOD function returns the remainder after dividing one number by another. When the remainder is zero it means that the number divides evenly into the other number. This is how we identify when it is an allocation year based on the number of years in column B.

By subtracting the starting financial year in column D from financial year in row 1 we can determine how many years have passed. We then divide that number by the number of years in column B to determine if there is a zero remainder. If there is a zero remainder then that year is a year that should be allocated.

This is an example of needing to identify a year for each row – the starting financial year in column D. And a year number for a column – row 1 with the financial year. Rather than creating a long formula that did those extra calculations it is more efficient to perform the calculations once and link to them.

Using the layout in the second image the following formula doesn’t require any helper cells. This formula is harder to understand.

=IF(AND(F$1>=$D3,MOD(RIGHT(F$2,4)*1-YEAR($C3+184),$B3)=0),$E3,"")

This is duplicating the RIGHT and YEAR calculations in multiple cells, instead of centralising them for the row and the column.

Download Example File