- BY Kevin Lehrbass
- POSTED IN Tips
- WITH 4 COMMENTS
- PERMALINK
- STANDARD POST TYPE

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