Split Amount into Monthly Columns

Download File: www.excel-univ...
Let's say you need to take an amount and split it evenly into monthly columns. For example, perhaps you need to recognize revenue you collect up front over time. Or, perhaps you have spent some money and you need to allocate the expense over time. There are other illustrations, but the basic idea is that you have a total amount that you need to spread evenly into monthly columns. As this is Excel, there are many possible approaches. In this post, I'll present a solution that uses dynamic arrays. The benefit of this solution is that you can quickly and easily adjust the number of columns displayed in the report.

[@kirstinlarson3880]

This is literally the best Excel tip I've heard in ages! I will now obsessively look at all your other content! Thanks :)

[@ExcelU]

Thank you SO MUCH for your kind note :) I hope you find some goodies in my other videos as well.

[@GP-jj5cv]

I had to rewatch pieces over & over again, but now POOF! I have a monthly projections sheet! Can't thank you enough, this has been hurting my brain for almost a week now.

[@ExcelU]

Ginny ... I'm glad it helped and I hope your brain doesn't hurt anymore ;-)

[@BetterMindJourney]

I had to watch through 3-4 times but yep, it works!! I missed the second # in the end date formula....

[@syedmusheer8792]

This is the best ever Excel tip I've seen in my Exp! I will now obsessively look at all your other content! Thanks make more

[@ExcelU]

Thank you for your kind note, I'm glad it was helpful :)

[@BetterMindJourney]

I needed this today and you've literally saved me hours and hours of work!!!! Thank you so much. Your walkthrough is excellent, very clear!

[@Onlyforyouify]

I have been watching lots of videos but this was just magic to me.

[@ExcelU]

Thanks, glad it was helpful :)

[@ms.locklear1220]

Oh how much time you just saved me, thanks so much!!

[@mateusz1227]

what a productive Sunday, I have learned a lot form this vid!!!! thank you!

[@ExcelU]

Glad it helped, thanks!

[@davidgray5764]

Awesome some the best Excel formulas I have seen.

[@ExcelU]

Great to hear!

[@Omarnfm]

Hard things become easy! Thanks a lot, Super explanation, a mind-blowing demonstration!!

[@ExcelU]

Thanks, glad it was helpful :)

[@slaymonanwaryar4388]

Great tips, thank you. How do you add a sum total which will follow the number of columns depending on number of months?

[@maria311ify]

very good, thank you! i managed to split my orderbook per month

[@LeroyFernandes-gh7bw]

This was awesome, is there a video on Split amount into Annual columns

[@KibokoPlastics]

Thanks it's very useful for me

[@ValeriaRodriguez-dr2ez]

This worked wonderfully in Excel but I'm having trouble moving the formulas to Google Sheets. Does anyone know a replacement for the formula carrying over the monthly amounts?

[@shrirajdeshpande8125]

I have a question - For row number 15 case, customer D - the start date is 15th of March which is mid of the month and hence the revenue recognition should be half of the total amount on pro-rata basis for the first and last month... so it should be 20, 35, 36, 35, 36, 18 for Mar-30, Apr-30, May-30, Jun-30, Jul-30 & Aug-30 respectively.

[@AngelaMelendez-x2u]

This is just what I needed! Can you also tell me how to split the amounts quarterly?

[@ammarali2880]

this is amazing thanks alot, but what if we have to divide an unequal amount equally? like 23/5 as, 5,5,5,5,3 ???

[@Boom19291]

What if I want to add an end date with the distribution? Example, 50% in the first 3 months then 100% rest if its past say Feb 2025

[@tom95428]

Very helpful, thank you!

[@ExcelU]

Glad it helped!

[@tatianasimonchik8778]

That’s amazing. Could you pease suggest if I need to distribute my totals not equally but based on a key. For example if my first line has code A, then it sold distribute it as 5% in first period, 95% in a second period. And if a second line is coded B, then the distribution should be 10% in a first period, 10% in a second one, and 80% falls to the third one?

[@Fishyninjabreadman]

Hi, Great video! In your example, you are obviously using a subscription based system, thus the same price every month. How would I do this if the values differ from month to month? For example I have an invoice of $6000, and its being paid in a 30%, 60% and 10% instalment.

[@iqrazonecommerce6126]

how can the total amount of the month be divided over 30 days? example : total sales for the month of january is 50000. this amount is inclusive of 5 % tax. how can this be differentiated from day to day ?

[@geoffffunston]

How do you do a bell curve in the same scenario??

[@subashans9285]

Hello !! Could you please provide with an help to split amount weekly rather than monthly.. Many thanks..

[@Onlyforyouify]

Can we do proreta in this excel, for example if 36500 is amount for one year and starting date is 11th Jan 2020 so, for Jan we should get 21 days amount which is 2100 and carry on, and last month will have 10 days so, amount will be 1000

[@swapnachavan5746]

@author can you please help with above question?

[@Sasha_5M]

@Author - we are eagerly waiting for your reply...please help.....Thanks in advance

[@PanduGaming691]

Every thing is automated but Amount distribution is not correct. if month is changing amount per month should be change.

[@lucygrierson1974]

Fabulous

[@ExcelU]

Thank you!

[@raghumangapoti1127]

Dear sir not visible your video

[@nigilv.d4237]

Great

[@mahi-pcl9856]

hi, if between the month start then how to come perticular days for this month cost

[@shrirajdeshpande8125]

=MAX[ MIN(EndDate,EOMONTH(PresentDate,0)) - MAX(StartDate,EOMONTH(PresentDate,-1)+1)+1, 0 ]* Amount / (EndDate - StartDate+1)

[@mahi-pcl9856]

any update on my below query

[@DanteMilazzo]

wondering if you could help me, i am trying to duplicate this in Google Sheets, I was able to figure out the months across the top with [=ARRAYFORMULA(EOMONTH(EDATE(control!B1,SEQUENCE(1,control!B2,0)),0)) ] i am struggling with the revenue portion and having that parse.=(T2