Let’s say you want to allocate a value across multiple months based on a start date and how many months you want to allocate. The monthly allocation will be averaged based on the number of months. The solution isn’t that hard.
The table below shows the layout we will use. Columns A to D are for input and columns F to Q are formula columns. You can download the example at the end of the post.
The formula in cell F2 is
The formula has been copied across and down. Note the $ signs – these allow you to copy the formula down and across.
The AND function allows you to handle multiple logical tests and it returns a single TRUE or FALSE. When using AND all of the logical tests must return TRUE for AND to return TRUE. If any of the logical tests is FALSE then the AND function will return FALSE.
The first logical test is comparing the start month in column C to the month in row one.
The second logical test uses the EDATE function.
The EDATE function increments a date based on a number of months. The second logical test is confirming that the month in row one does not exceed the number of months from the start month.
The calculation performed is straightforward, dividing the value by the number of months.
If the month is not within the date range then a zero is returned.