# How to allocate a certain amount over a 12 month time period through formulas in excel

1. ## How to allocate a certain amount over a 12 month time period through formulas in excel

Hi guys,

I am trying to understand how to overcome this problem that I have been struggling with.

So there is a certain amount (say, \$1000) post which I have an additional transaction charge (per \$, say 50 cents). And say, every month I have a certain spend (which depends on formulas so, not fixed). I have a column for "additional charge" which essentially has to check for each month whether it crosses the 1000 threshold (annual) and then apply the per \$ charge to the cell. Now I tried using an "if" command, which works. But the challenge is that, once in any particular month (or cumulatively till that month) the amount spent crosses \$1000, I have to account for the per \$ charge. But the very next month (assuming it is still in the same year) will not have any more bandwidth for the \$1000 limit left. So the entire charge on that month will be above the \$1000 threshold so will be charged on that per \$ basis.

So, for now, I have been adding these formulae manually once the \$1000 limit crosses. But I would like for a formula/ something where it checks 2 things, whether the spend has cumulatively crossed \$1000 and whether that \$1000 deduction has already been taken into account in the previous month. Hope this makes sense and you guys can help me out with it!!

2. ## Re: How to allocate a certain amount over a 12 month time period through formulas in excel

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

3. ## Re: How to allocate a certain amount over a 12 month time period through formulas in excel

Note that you can easily check cumulative sum from all rows above current row using mixed addressing. Say your single amounts are in column C and you "are" in row 16 so want to look on sum from rows 1 to 15
use
Formula:
`Please Login or Register  to view this content.`
for that. If you copy this formula down, you will notice it changes to:
Formula:
`Please Login or Register  to view this content.`

and so on. So you can use it in your IF formula.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1