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).
Allocation Set-up
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.
Excel File
Download my Excel file and follow along.
2 Solutions
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:
=SUMPRODUCT(OFFSET(I$9,0,0,1,IF(I7<$C$2,I7,$C$2)*-1)/$C$2)
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 🙂
Data Art
I saw this giant data art object in Vegas called “HOTO“. It was created by Japanese artist Tatsuo Miyajima. Watch the YouTube video!
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.
Thanks for sharing, Keep up the good work!
Hello Kevin,
My formula is this:
=SUM(I9:INDEX(9:9,MAX(COLUMN()-$C2+1,5)))/$C2
The MAX(COLUMN()-$C2+1,5), which is the column_num argument of the INDEX function, calculates the Column Part, which must not be less than 5 (Column E), of the End of the Range, given the Row Part, which is 9 (9:9 array argument of the INDEX function).
Hi Robert,
I like your formula! Thank you! I default to using OFFSET. I should learn how to use INDEX like you!
Cheers,
Kevin
Pingback: Excel Chess Games Viewer | My Spreadsheet Lab