Revenue Summation By Account Name
Aug 15, 2013
Attached is my sample data...Indicative Data_Revenue 2.xlsx
My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.
The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage
Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.
View 9 Replies
ADVERTISEMENT
Sep 7, 2012
Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.
original formula from post 3261273
{=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}
New formulas needed or revised from the above:
Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)
Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"
Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.
Event Date
ID
CREATE
Suspend
STOPPED
Unsuspend
Elapsed Days (Create to Suspend but NO Stopped)
Elapsed Days (Suspend Only, No stopped or Unsuspend)
[Code] .........
View 1 Replies
View Related
Aug 21, 2009
I have two sheets. First contains Department # 0002549, and the other one contains 5 more digits(#02001 0002549). I will need to find the exact match for the first sheet to contain all 12 digits. THey are all in different order, so I probably have to match them first then add whatever the first 5 digits number. How should I tackle this issue? I was thinking to use vlookup but how could you use vlook up if only last 7 digit matches and add addtional digits to exisiting number?
View 9 Replies
View Related
Nov 21, 2007
I own a local restaurant and want to streamline my excel spreadsheets. Currently, I have yearly workbooks (2005, 2006, 2007, etc.) that have all of my revenues included in them, split up monthly by worksheets. I then have lines for each day within the month for the revenues earned at that day.
So, if I want to find how much revenue I earned on March 1st, 2006, I go to my 2006 book, March worksheet, and look at the 1st. Here's where my problem lies.
I want to create a new spreadsheet where all I have to do is type in a certain date (say March 1st, 2006) and all of the information for the date will be automatically displayed on the new sheet.
View 14 Replies
View Related
Apr 21, 2009
I am trying to figure how to get a formula to work that will spread revenue number over a four month period. The dates will be dynamic so they will change but the spread stays the same.
I have attached a snapshot of what I am trying to do.
View 9 Replies
View Related
Feb 6, 2010
I have a table which shows the monthly revenue of Company ABC , by client / by location / by business line.
ClientCountryBusiness LineJan-10 Feb-10Mar-10A001SingaporeResidential$ 50,000.00 $ 23,333.00 $ 54,115.00 D003SingaporeRetail$ 50,000.00 $ 21,548.00 $ 54,654.00A001SingaporeCommercial$ 63,321.00 $ 75,542.00 $ 21,564.00 D003AustraliaResidential$ 26,564.00 $ 50,000.00 $ 45,654.00 G002AustraliaRetail$ 50,000.00 $ 21,546.00$ 63,321.00G002AustraliaCommercial$ 26,602.00 $ 65,341.00 $ 24,568.00 A001Hong KongResidential$ 33,565.00 $ 26,564.00 $ 64,454.00 G002Hong KongRetail$ 54,232.00 $ 50,000.00 $ 15,454.00 D003Hong KongCommercial$ 26,564.00 $ 21,564.00 $ 23,333.00
If I want to calculate the total revenue for the month of January based on the following variable criteria:
Criteria
Client: A001
Country: Singapore
Month: Jan-10
I can achieve that with the use of SUMPRODUCT. However next month, I will then have to manually change the SUMPRODUCT formula so that it will extract data from the Feb column instead of the Jan column.
Is there a formula which will not require me to change the formula every mth? Ie. I can get my answer simply by changing the criteria?
View 9 Replies
View Related
Aug 6, 2006
I made 2 sheets: the first one contains the database and in the second one i want to analyze it. Now i am looking for a special sum- function. I want to sum total revenue for a particular company (criteria 1), in a filtered country (criteria 2), month (criteria 3) and class (criteria 4). I'd like to first execute the three filters (country, month and class) and then be able to total revenue of that particular company. Note that after the filters still several companies are visible in the database. Does anyone know how to calcalate this in cell B9:B12 (Analyze sheet) of my attached file.
View 9 Replies
View Related
Jun 26, 2014
I have sales data for Clients that has the client name month and year(combined for date) and revenue for each month. I would like to take all clients in a given month and see the average revenue per client and then be able to display that in a bar chart by month. I also have the data loaded into a PowerPivot Data Model.
View 1 Replies
View Related
Dec 27, 2013
I have a deferred revenue model. Revenue from each month earns out at an equal rate over twelve months. Sales for each month are in column B (B21) and C. I created formulas for the first year but when I get to the second year, I'm not sure what to do, because the earned premium will include values from previous year sales and current year sales.
View 4 Replies
View Related
Jan 8, 2014
I have to calculate the flat revenue split by month between 2 different dates. The two dates may be in the same year or different years.
excel formula for the same.
Is it possible to do the same in pivot?
View 1 Replies
View Related
Jan 13, 2014
Any way to set up Solver in order to maximize revenue, but with the constraints equaling ONLY one of two values (e.g. 0 or 100)?
View 1 Replies
View Related
Oct 3, 2008
So here's the setup: A customer purchases a service which lasts 6-months. As such, revenue recognition for the company can only occur as the service is provided. So if a person orders a $600 service at the beginning of a month, revenue will be recognized at $100 for the next 6 months. I'm trying to create an Excel Waterfall chart which will show the monthly revenue recognition amounts for all orders depending on the date which they were ordered.
I've attached a sample of what I'm looking to do. It's become tricky for me because Revenue Recognition is pro-rated based on the date ordered (i.e. order on the 20th of a month so at the end of the month 1/3 of the month is recognized as revenue). I'm looking for something that will populate the percentage of the order amount, each month, that will be recognized. I've populated what row 3 should be, but there doesn't seem to be an easy way to apply a formula or something across all cells.
View 2 Replies
View Related
Mar 4, 2009
I need to work out Revenue Rank & Year to Date calculations.
Consider a simple table:
| Partner Name | Year | Month | Revenue |
------------------------------------------
| John Smith | 2008 | Nov | 2000 |
| John Smith | 2008 | Dec | 2200 |
| John Smith | 2009 | Jan | 1898 |
| Mary Smith | 2008 | Nov | 1767 |
| Mary Smith | 2008 | Dec | 1867 |
| Mary Smith | 2009 | Jan | 1953 |
------------------------------------------
etc..etc...
I'm not using Pivot Tables since there are more complex issues around presentation which are preventing me doing this so are using good old formulas..
Revenue Rank is in reference to the Partner in this case. I need to be able to say John Smith is rank x out of xx by summing up his revenues for both:
a) one month
b) a range of 3 months back
How do I work this out? Especially the date calculations when I just have a year and month in separate fields?
View 9 Replies
View Related
Sep 27, 2007
I have a spreadsheet that holds a list of customers and the contracts they have with my company - this sheet includes the Total Contract Value in £s - so how much each customer will pay us for the service - the period of the service - so the start date of 01/01/08 to end date of 31/12/08 for example. What I then have is all the months across the top of my spreadsheet and I need to extrapolate the contract value, based on the period between the months.
Initially I had complicated formulas that simply divide the value by the number of months of the contract - however, my company has since changed the policy and requires it to be done to the day.
For example, if the value was £12,000 and the period was 12 months from 01/01/08 - to calculate January 08 it would be - 12,000 / 365 * 31, Feb would be 12,000 / 365 * 29 and so on...
Does anyone know how I can do this - bearing in mind that each contract won't be as easy as 12 months - it may be start date of 15/06/08 to 08/10/10 for example... If anyone can help me do a formula or formulae to calculate the value for each month - I would be very very grateful - as always... [/img]
View 9 Replies
View Related
Aug 23, 2008
I have created a model that shows me the money that I am owed each month. However, I receive the money over a 3 month period.
Using a formula I need to determine how much I actually receive in total each month, factoring in that I receive each months revenue in three equal payments over three months.
For example
Month 1 Month 2 Month 3 Month 4 Month 5 Month 6
$A $B $C $D $E $F
In month 1, I am owed $A, but this payment will be split over 3 months.
So in month 1 I collect $A/3. In month 2, I am owed $B but this payment will also be split over 3 months so in month 2 I collect ($A/3 + $B/3), etc etc. In month 3 I collect the ($C/3) + ($B/3) + (last payment of $a/3) etc etc
How do I present this in a formula so I dont have to adjust every single column manually?
I need to follow the same structre for another scenario using 18 months as well.
View 9 Replies
View Related
Nov 7, 2008
I have a question on data interpolation with Excel 2007. Normally, this wouldn't be too much of an issue for me, but for some reason I cannot figure it out.
I'm working on an Income Statement, which is designed for 5 years. I have values 1 and 5, which are given to me (B7: 3,500,000 and F7: 5,200,000). I'm trying to figure out the other 3 years of revenue assuming a growth trend. The values should fill the series B7:F7.
View 9 Replies
View Related
Dec 22, 2008
How can I summate cells in a column *except* for those rows which contain a specific value in another column?
=SUM(A1:A10 where B<row num> !="SOLD"). I'm trying to add up the weights of items which are not yet sold.
View 3 Replies
View Related
May 19, 2009
I have written an SQL Stored proceedure which drags this information into excel.
All is well here, however because the information changes regularly adding and subtracting in row length it means I am having issues writing code for sums as a standard macro is absolute and not realative.
What I basically have is this ....
View 7 Replies
View Related
Jun 26, 2007
I need to be able to search through category(column H) and match with machine size category. Then for each category split up total time spent on each function. Granted this will be 6 formulas. I've searched through the forums and came up with what I have....
View 10 Replies
View Related
Dec 17, 2008
I try to add some numbers, it will display ### symbol rather than that Value.If i give Summation for 10+10+10+10+10 the result displays ### than 50. How to resolve this?
View 15 Replies
View Related
May 25, 2009
how to make a summation formula in excel (not simply adding 2 numbers together)? I have one cell that I would enter a number into, and another cell which would specify the limit. So if the first cell A1 = 1, and cell A2 = 50, then the 3rd cell would automatically add up every number "n" plus 1 until n=50. ie: (1+1)+(2+1)+(3+1)+...+(50+1)=result. The limit would have to be flexible, so if I change the 3rd cell to 70 then it would calculate to 70.
View 9 Replies
View Related
Jul 24, 2008
I have been asked to go through some information which has over 200 000 lines, what I need to do is as follows:
where order number is unique, revenue source = "Unique"
where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package"
I need all of the above in formula line which will be inserted in the revenue source column
I have attached the file with a sample of the information I am using.
View 9 Replies
View Related
Jan 10, 2014
I have attached a screen shot below of a sheet I am using to collate fruit orders and calculate an order total for each individual person.
Untitled.png
The price of each fruit item runs along row 3 and then each persons order has its own row. The total price for each person is then calculated in column AM. However I am looking to shorten the formula I am using to calculate the total in this column.
I am currently using:
AM4=SUM(C3*C4+D3*D4+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
AM5=SUM(C3*C5+D3*D5+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
As you can see this formula is far too long so I am looking to write it in compressed formula.
View 4 Replies
View Related
Oct 8, 2008
I have a workbook with 11 sheets. In 10 first sheets i é use function PRODUCT(B35;$H$2) to calculate the values in each cell.
Then i tried to get the summation of 10 sheets in 11th sheet for every cell as following but i keep getting zero as a result instead of the summation:
Sheet11!B5 =Sheet1!B5+Sheet2!B5+Sheet3!B5+Sheet4!B5+Sheet5!B5+Sheet6!B5+Sheet8!B5+Sheet7!B5+Sheet9!B5+Sheet10!B 5.
View 4 Replies
View Related
Jan 11, 2009
I've run into an issue for which I can find no elegant solution. I consider myself a competent Excel user, so it's pretty embarassing...
What I want to do is to sum the values along the 'axes' of the table. In order to fully explain that, I'll give a small example:
Let's say, this is my source data
View 2 Replies
View Related
Jul 13, 2009
I'm trying to generate random numbers that will add up to a certain number and am having trouble brainstorming how to do so. For example, I'm trying to get a+b+c=6 with a, b, and c randomly generated to add up to 6.
View 4 Replies
View Related
Jan 23, 2012
Say, I have two columns each having unknown number of rows. what would be the vbscript code to get the summation of a column's values
For example,
Two existing columns are A1 and B1. In order to create C1.
Column C = 10/summation(a1)/(b1)
How to do the above operation?
View 6 Replies
View Related
Dec 5, 2006
I was trying to assist someone with 3D referencing and summing, but getting stuck on referencing text based sheetnames.
We are trying to sum range A1:A10 in the sheets between the range defined by A1:A2
Now, if the Sheet names were numerically sequenced, eg. Sheet1, Sheet2, etc...then we could use this formula with no problem
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(A1&":"&A2))&"'!A1:A10"),""))
where A1 and A2 housed the numbers 1 and 2, respectively,
but what if the sheets are text based? Is there a way....say the Sheets were named SheetX, SheetY, SheetZ....
I did some searching on the internet and found samples only with the numeric based sheetnames.
View 10 Replies
View Related
Apr 13, 2009
Currently I am trying to do a gantt like chart time summation. I'll try to explain this as clear as possible... Right now I have a program that finds the name of a part in a giant list's column and then copies a few of the other columns (priority and hours to complete) from the part names row and pastes them into a new sheet. I then sort all the found data by their priority number. The job that is gets sorted to the top is the next job to be worked on for the machine. So here is what I need to be able to do...
Based on the following example I need to be able to sum for the object bolt on Machine #1 the tenative date that the "bolt" will be completed by. It will take a minimum of 35 hours for the bolt to get off Machine #1 based on its current priority. However, the part: "bolt" needs to go to Machine #2 before being completed. The part will have to wait a hour for "hammer" before being started on machine 2. (Its data for the time it will take complete "bolt" on machine 2 is also stored in the spreadsheet.) Since the bolts priority is higher than the "nail"... the "bolt" will be the next job to be completed. My question is this: Is there a way to tenatively sum the future hours that it will take to complete the bolt and allow it to be open to changes, in case a higher priority part comes into the system?
Machine #1
PRIORITY HOURSNAME123nut212bolt334washer422kazoo
MACHINE #2
PRIORITY
HOURSNAME122screw22hammer31nail
View 9 Replies
View Related
May 5, 2014
i wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).
View 2 Replies
View Related
