• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Spreading revenue

W

will31

New Member
Hi All,


I have absolutely no idea if this is possible but if it is then my life would be so easy.


I plot out the revenues of several departments and have to follow the forecast of the lead department. This means that I spend hours pouring over someone elses spreadsheets to ensure I have all the projects on my sheets that they have on theirs.


This week I created a sheet to find out what the revenue split is for each subordinate dept which is great but is it possible to use formulas to split that revenue based on the values in the lead depts forecast?


This would look like this;


<b>Sheet1</b>

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:82px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Project No</td><td >Dept</td><td >Revenue </td><td > </td><td > </td><td style="text-align:right; ">Jan-11</td><td style="text-align:right; ">Feb-11</td><td style="text-align:right; ">Mar-11</td><td style="text-align:right; ">Apr-11</td><td style="text-align:right; ">May-11</td><td style="text-align:right; ">Jun-11</td><td style="text-align:right; ">Jul-11</td><td style="text-align:right; ">Aug-11</td><td style="text-align:right; ">Sep-11</td><td style="text-align:right; ">Oct-11</td><td style="text-align:right; ">Nov-11</td><td style="text-align:right; ">Dec-11</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >AAO0001</td><td >Lead</td><td style="text-align:right; ">100000</td><td > </td><td > </td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">25000</td><td style="text-align:right; ">25000</td><td style="text-align:right; ">25000</td><td style="text-align:right; ">25000</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >AAO0001</td><td >Sub 1</td><td style="text-align:right; ">1000</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">500</td><td style="text-align:right; ">500</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >AAO0001</td><td >Sub 2</td><td style="text-align:right; ">10000</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">2500</td><td style="text-align:right; ">2500</td><td style="text-align:right; ">2500</td><td style="text-align:right; ">2500</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >AAO0001</td><td >Sub 3</td><td style="text-align:right; ">10000</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">10000</td><td > </td><td > </td><td > </td><td > </td></tr></table>

<span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span> Excel Jeanie HTML 4


I would need a formulas to split the revenue in the first two months of the project, across each month of the project and in the last month of the project. Is this possible or I am asking too much of excel?


Thanks in advance,


Will
 
Posted it here so you can see it;


http://www.mrexcel.com/forum/showthread.php?p=2510737#post2510737


A picture speaks a thousand words...
 
G'Day Will


Have a read of this post about posting pics and files:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Also Will, please don't cross post or if you do let us know.

No point spending time on a problem that was solved somewhere else hours ago.
 
You must log in or register to reply here.
Back
Top