In this post I’ll show you how to allocate expenses (or any number) over a period of time. I will use years but it could also be months, weeks or days.
What Does Allocate Mean?
Allocate means to spread a number across several time periods (months, years, weeks, etc). This is commonly done for accounting but also has other uses.
Imagine we only have 1 cost. In 2020 a cost of 3500 that’s split over 3 years (as cell C2 = 3).
In cell C2 I selected 3 as I want to spread each cost over 3 years. We could change it at any time and the formulas will update. Enter your Start Year in cell C3.
Look at year 2022 below. Total cost is 5866.67 We take 1/3 of the cost for 2022, 2021, 2020.
The 2022 cost of 7500 is split over 3 years. Only the first 1/3 (2500) is allocated against 2022.
Download my Excel file and follow along.
My Excel file contains two solutions:
- show all costs and final formula adds it all up
- 1 formula adds all costs
Solution 1 uses more cells as each individual cost can be seen and then added up.
Solution 2 uses this compact formula in cell I11:
Solution 2 explanation:
(a) Get the numbers: =OFFSET(I$9,0,0,1,-1*$C$2) (I removed the IF function for now)
Offset can retrieve a range of values. Here, offset gets 3500, 6600, 7500.
(b) Divide by number of years: /$C$2 After getting these 3 numbers we have to divide by 3 (C2 value).
(c) Wrap it all with SUMPRODUCT which adds everything together.
(d) Finally change -1*$C$2 to IF(I7<$C$2,I7,$C$2)*-1 (this allows it to stop looking left)
….and now let’s take a break from the spreadsheet and look at some data art 🙂
HOTO and Me
My name is Kevin Lehrbass. I’m a Data Analyst. Below is a pic of me and “Hoto” in Las Vegas last year.
This is my personal blog about where I share tips, explore ideas and sometimes do nerdy things in Excel.