# Allocating costs

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.

### 2 Solutions

#### My Excel file contains two solutions:

1. show all costs and final formula adds it all up
2. 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.

1. Robert H. Gascon

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).

1. Kevin Lehrbass

Hi Robert,
I like your formula! Thank you! I default to using OFFSET. I should learn how to use INDEX like you!
Cheers,
Kevin