Spreading revenue over months

S

sps21

New Member
Joined
Apr 3, 2017
Messages
8
Hi there

New user here! I am building a financial model and want a quicker way to spread the revenue. I have a monthly P&L going for 5 years and I have to spread the monthly revenue across 12 months in a separate tab.


Source file:



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan 17[/TD]
[TD]Feb 17[/TD]
[TD]Mar 17[/TD]
[TD]till[/TD]
[TD]Dec 21[/TD]
[/TR]
[TR]
[TD]Monthly Revenue[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



which needs to expressed in following manner in a different tab


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan17[/TD]
[TD]Feb 17[/TD]
[TD]Mar 17[/TD]
[TD]Apr 17[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]so on[/TD]
[TD]Dec17[/TD]
[TD]Jan18[/TD]
[TD]Feb18[/TD]
[TD]Mar18[/TD]
[TD]till Dec 21[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]X/12[/TD]
[TD]X/12[/TD]
[TD]X/12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X/12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb [/TD]
[TD][/TD]
[TD]Y/12[/TD]
[TD]Y/12[/TD]
[TD]Y/12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y/12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD]Z/12[/TD]
[TD]Z/12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Z/12[/TD]
[TD]Z/12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]till Dec [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I would really appreciate if any one can suggest a quicker way to do this..something like a formula which can be dragged across to pull in the value from the source.


Thanks in advance
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
If the spaces in between are supposed to be filled, something like:


Excel 2010
ABCDEFGHIJKLM
11/31/20172/28/20173/31/20174/30/20175/31/20176/30/20177/31/20178/31/20179/30/201710/31/201711/30/201712/31/2017
2Monthly Revenue754541541202752253202658750223333842714792418209
3
4
51/31/20172/28/20173/31/20174/30/20175/31/20176/30/20177/31/20178/31/20179/30/201710/31/201711/30/201712/31/2017
6Jan628.75628.75628.75628.75628.75628.75628.75628.75628.75628.75628.75628.75
7Feb346.16667346.16667346.16667346.16667346.16667346.16667346.16667346.16667346.166667346.166667346.166667
8Mar100.16667100.16667100.16667100.16667100.16667100.16667100.16667100.166667100.166667100.166667
9Apr626.83333626.83333626.83333626.83333626.83333626.83333626.833333626.833333626.833333
10May443.33333443.33333443.33333443.33333443.33333443.333333443.333333443.333333
11Jun221.5221.5221.5221.5221.5221.5221.5
12Jul625.16667625.16667625.16667625.166667625.166667625.166667
13Aug194.41667194.41667194.416667194.416667194.416667
14Sep320.16667320.166667320.166667320.166667
15Oct595.583333595.583333595.583333
16Nov770.083333770.083333
17Dec684.083333
Sheet11
Cell Formulas
RangeFormula
B6=IF(AND(COLUMN()-ROW(A1)>=1,COLUMN()-ROW(A1)<=12),INDEX($B$2:$BI$2,ROW(A1))/12,"")
 
Upvote 0
sheetspread said:
If the spaces in between are supposed to be filled, something like:

Excel 2010
ABCDEFGHIJKLM
Monthly Revenue
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/31/2017[/TD]
[TD="align: right"]2/28/2017[/TD]
[TD="align: right"]3/31/2017[/TD]
[TD="align: right"]4/30/2017[/TD]
[TD="align: right"]5/31/2017[/TD]
[TD="align: right"]6/30/2017[/TD]
[TD="align: right"]7/31/2017[/TD]
[TD="align: right"]8/31/2017[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]10/31/2017[/TD]
[TD="align: right"]11/30/2017[/TD]
[TD="align: right"]12/31/2017[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]7545[/TD]
[TD="align: right"]4154[/TD]
[TD="align: right"]1202[/TD]
[TD="align: right"]7522[/TD]
[TD="align: right"]5320[/TD]
[TD="align: right"]2658[/TD]
[TD="align: right"]7502[/TD]
[TD="align: right"]2333[/TD]
[TD="align: right"]3842[/TD]
[TD="align: right"]7147[/TD]
[TD="align: right"]9241[/TD]
[TD="align: right"]8209[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/31/2017[/TD]
[TD="align: right"]2/28/2017[/TD]
[TD="align: right"]3/31/2017[/TD]
[TD="align: right"]4/30/2017[/TD]
[TD="align: right"]5/31/2017[/TD]
[TD="align: right"]6/30/2017[/TD]
[TD="align: right"]7/31/2017[/TD]
[TD="align: right"]8/31/2017[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: right"]10/31/2017[/TD]
[TD="align: right"]11/30/2017[/TD]
[TD="align: right"]12/31/2017[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]
[TD="align: right"]628.75[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]346.16667[/TD]
[TD="align: right"]346.16667[/TD]
[TD="align: right"]346.16667[/TD]
[TD="align: right"]346.16667[/TD]
[TD="align: right"]346.16667[/TD]
[TD="align: right"]346.16667[/TD]
[TD="align: right"]346.16667[/TD]
[TD="align: right"]346.16667[/TD]
[TD="align: right"]346.166667[/TD]
[TD="align: right"]346.166667[/TD]
[TD="align: right"]346.166667[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]100.16667[/TD]
[TD="align: right"]100.16667[/TD]
[TD="align: right"]100.16667[/TD]
[TD="align: right"]100.16667[/TD]
[TD="align: right"]100.16667[/TD]
[TD="align: right"]100.16667[/TD]
[TD="align: right"]100.16667[/TD]
[TD="align: right"]100.166667[/TD]
[TD="align: right"]100.166667[/TD]
[TD="align: right"]100.166667[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]626.83333[/TD]
[TD="align: right"]626.83333[/TD]
[TD="align: right"]626.83333[/TD]
[TD="align: right"]626.83333[/TD]
[TD="align: right"]626.83333[/TD]
[TD="align: right"]626.83333[/TD]
[TD="align: right"]626.833333[/TD]
[TD="align: right"]626.833333[/TD]
[TD="align: right"]626.833333[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]443.33333[/TD]
[TD="align: right"]443.33333[/TD]
[TD="align: right"]443.33333[/TD]
[TD="align: right"]443.33333[/TD]
[TD="align: right"]443.33333[/TD]
[TD="align: right"]443.333333[/TD]
[TD="align: right"]443.333333[/TD]
[TD="align: right"]443.333333[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]221.5[/TD]
[TD="align: right"]221.5[/TD]
[TD="align: right"]221.5[/TD]
[TD="align: right"]221.5[/TD]
[TD="align: right"]221.5[/TD]
[TD="align: right"]221.5[/TD]
[TD="align: right"]221.5[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]625.16667[/TD]
[TD="align: right"]625.16667[/TD]
[TD="align: right"]625.16667[/TD]
[TD="align: right"]625.166667[/TD]
[TD="align: right"]625.166667[/TD]
[TD="align: right"]625.166667[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]194.41667[/TD]
[TD="align: right"]194.41667[/TD]
[TD="align: right"]194.416667[/TD]
[TD="align: right"]194.416667[/TD]
[TD="align: right"]194.416667[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]320.16667[/TD]
[TD="align: right"]320.166667[/TD]
[TD="align: right"]320.166667[/TD]
[TD="align: right"]320.166667[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]595.583333[/TD]
[TD="align: right"]595.583333[/TD]
[TD="align: right"]595.583333[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]770.083333[/TD]
[TD="align: right"]770.083333[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]684.083333[/TD]

</tbody>
Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=IF(AND(COLUMN()-ROW(A1)>=1,COLUMN()-ROW(A1)<=12),INDEX($B$2:$BI$2,ROW(A1))/12,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Hi Spreadsheet

Thanks for your reply! It works! However could you explain your thought process behind AND(COLUMN()-ROW(A1)>=1,COLUMN()-ROW(A1)<=12). This would help me devise my own formula for situations like these in future. If you don't feel like typing a big reply to question then ignore! I am grateful for your help :)

Thanks again and have a good day!

SPS
 
Upvote 0
You're only trying to return 12 cells per row, starting at the first column for the first row, second column for the second row, etc. Row(A1) will change as you copy down (from 1 to 2 to 3.......) Likewise Column() in B6 (the same as typing Column(B6)) will start at 2 and change to 3, 4... as you copy across. So test whether the difference falls between 1 and 12 inclusive, if it does, you return the revenue/12, otherwise a "" (fake blank). One good way to understand multi-function formulas is by examining them in pieces, i.e. seeing the results of each component.
 
Upvote 0
sheetspread said:
You're only trying to return 12 cells per row, starting at the first column for the first row, second column for the second row, etc. Row(A1) will change as you copy down (from 1 to 2 to 3.......) Likewise Column() in B6 (the same as typing Column(B6)) will start at 2 and change to 3, 4... as you copy across. So test whether the difference falls between 1 and 12 inclusive, if it does, you return the revenue/12, otherwise a "" (fake blank). One good way to understand multi-function formulas is by examining them in pieces, i.e. seeing the results of each component.
Click to expand...

Got it! Thanks a lot

SPS
 
Upvote 0
You must log in or register to reply here.

Similar threads

ricafonyat
  • Solved
Total Revenue according certain periods
Replies
4
Views
167
Phuoc
P
K
  • Solved
Calculate Average with Criteria
Replies
2
Views
236
kumara_faith
K
C
  • Question Question
Allocation Across Months
Replies
3
Views
492
NickFru
N
J
  • Question Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
3K
felixstraube
F
F
  • Solved
Look up monthly budget/actual based on input month
Replies
2
Views
201
fishmonger
F

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top