On Weighted Percentages - Create A Report That Rank Top Sales Agent For The Month?
Jul 29, 2014
I have been asked to create a report that rank top sales agent for the month with the following variables:
Total # of sales-35%
Total $ of sales-45%
Number of calls made-20%
Given that there is more than one variable they have placed percentage weight on each. Thus my task is to cull said percentage and add their total thus getting a figure which would represent their ranking depending on how higj or low the total is.
View 3 Replies
ADVERTISEMENT
Dec 25, 2013
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies
View Related
Mar 5, 2012
Let's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.
How can I create a report to show the TOP 20% based on sales to grand total.
Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?
Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.
View 3 Replies
View Related
Feb 4, 2013
I wanted to get the data from data sheet where i have entered the data manually for sales what have done
Now I wanted is when i enter date i should get the product and price in other sheet
Example:
If I enter date in a1 cell
I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet
And c1 - c10 price (if i sold 10 item @that price)
View 4 Replies
View Related
Aug 20, 2013
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
View 1 Replies
View Related
Oct 17, 2013
I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....
The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.
Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....
View 6 Replies
View Related
Jan 12, 2013
In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).
Every day, I update the numerical data of the database, with the facts of previous day.
In another sheet I've made some monthly, quarterly etc., reports for that data.
What I want, is to have:
1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.
2) the total workdays days for that period.
View 4 Replies
View Related
Jul 25, 2007
Each individual enters 6 different qualifying numbers (columns) resulting in a "place" in each column. Placement in each of those columns is equal to the other columns or as important as the other. Is there a way to set up a formula to reflect each individuals "place" in each column and then have those columns weighted equally and averaged to determine the Winner?
View 9 Replies
View Related
Feb 2, 2008
I have an Access DB that I query with excel and I pull two years worth of sales data. I have tried using a pivot table report to display the following data, but I can't figure out how to display the data in the following format.
The pivot table will give period and YTD but the totals for YTD are not cumulative for the year up to that period (it seems to total the period only).
For the current Year- period (month) and YTD (only up to the period displayed).
For the last year- period and YTD (only up to the period displayed).
The fields I query are Customer, City, Product, Salesperson, Period(month), Year and Sales
I have tried putting the queried data on one sheet and then using formulas on another but I am not having any luck.
I would also like to be able to select which period I am viewing but this is secondary.
I can upload an example if necessary.
View 5 Replies
View Related
Mar 16, 2014
I am trying to do my homework for college and the below excel grid was given to us to complete. I do not understand where to get the information it is asking. the first grid is the numbers we are suppose to use to input in the other grids. We are suppose to put a formula in on the last to two columns on each grid but I do not even know where to start.
Budget
Actual
Product
SaleUnits
$/Unit
View 7 Replies
View Related
Jul 24, 2013
I need taking thousands of line items of raw data and creating a model that can present the totals in an easy to read format. I've attached a sample of what my data dump looks like....though the actual dump is thousands of line items.
Data.xlsx‎
View 1 Replies
View Related
Jul 24, 2013
I have the task of pulling results of an Access query into Excel, formatting them in a "standard" way (the data will be formatted the same everytime in every report), then AUTOMATICALLY emailing them at a pre-determined time everyday.
1. I have tried using the record macro function, and it works as far as pulling in query results and formatting them
2. Then, using the macro i recorded, i added onto it so that when I manually run this new macro, the whole thing gets emailed, but it is not automated. My issue is not having to manually run these macros and have them run as scheduled tasks.
View 2 Replies
View Related
Jun 11, 2009
I have multiple customers in a list that I would like to create individaul tabs for each, with customer name and store #, and at the same time utilizing my sales sheet template for all customers. Is there a way to do this without copying and pasting one by one.
View 9 Replies
View Related
Jan 14, 2013
I got individual dates in Column A. The format is 01/04/2012, 02/04/2012, 03/04/2012 and so on.
The person enters sales everyday. Now I want to make a new sheet which should sum up the sales month wise.
So in the new sheet I will have
Column A- Products
Column B- Sum of days in April 2012
Column C- Sum of Days in May 2012
Column D- Sum of Days June 2012
And so on
View 5 Replies
View Related
Jun 6, 2014
I am trying to create a function where the user can type in a single month, and 2 different years, and it will calculate/compare that single month from the 2 different years entered.
For Example: I type in January, and I type in 2014 and 2013. The excel formula will spit out whether January 2014 is over/under January 2013.
See attached for my sales report. You can see where I left some space for the requested function.
as it is the last change I have to make to the sales report.
View 5 Replies
View Related
May 25, 2008
I am fairly new to VBA / Excel programming. I have been trying to write a report out of excel from our company DB (SQL2005). The database is run by our frontend accounting application - so i cant mess with it at all, must only run queries.
I need to pull the last 24 months of stock sales data(by stock code or category) out of our DB into excel by counting transactions on Customer Invoices / credits. Into a table as follows..
Stock Code--Month1-Month2-Month3
ABC1----------43------33------19
ABC2-----------2------10------25
I have managed to make a script that fullfills this need but it takes about 15 minutes to run(Due to having to loop many times per item/ per month)....
I was just wondering if anyone had any tips / advice on different ways to do this..??? Ive had a quick look at Pivottables but havent gone very far in, maybe they are the answer, but this amateur does not know.
View 10 Replies
View Related
May 6, 2006
I am trying to add a specific range of data
Column A include a code
Column B-X include actual data
Culumn X- AI include budget figures.
Also in cell A1i have the number of the month
For example the month is 3 (March)
I want in AK to create a SUMIF where the formula will sum columnsX+Y+Z
If month goes 4 then should calculate
X+Y+Z+AA
and so on
View 9 Replies
View Related
Apr 12, 2009
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies
View Related
Sep 18, 2009
I have created a Pivot Table to sumarize the monthly sales data.
Is it possible to select just one month and show the YTD sales?
e.g. We are in September, I select August the Pivot Table should show the August sales column and YTD (Jan to August) sales column.
View 9 Replies
View Related
Mar 27, 2014
I am writing a formula that will sum data for a 12 month period that is not based on a calendar year. For example may have data starting in June 2011 running to date, but not every month. If I set a start date for Year 1, say April 2012, I need to see Year 0 numbers (anything prior to April 2012), Year 1 numbers (April 2012 to March 2013), Year 2 numbers (April 2013 to March 2014), and so on. Date format is currently mmm-yy.
If this could be performed via a macro which references the database (spreadsheet), then even better. The database will contain multiple columns, of which two will contain the date and corresponding number respectively.
View 12 Replies
View Related
Sep 30, 2009
is there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?
View 2 Replies
View Related
Jan 26, 2012
Code that will do the following,
In sheet2 cell B2 i have a date (for example 12 Jan 2011)
I want to look at sheet1 columB and if the date is older than the date in sheet2 cell B2, then copy then all the data from that line in sheet1 be copied over to the first free line in sheet2 I need the data from colums B to ZZ to be copied.
View 1 Replies
View Related
Feb 7, 2008
I am using Excel 2007 and need some help. If you could please download and open the attached excel sheet then i guess you will be able to understand my problem easily. I am also not sure if what i am asking is really possible or not in Excel (would prefer without any VBA coding but if there's no other way then VBA coding would also do).
What i need is that based on the data from 'data' sheet i want to dynamically fill the ' Date' column in 'CatA' sheet. If you check the 'Expected Result' sheet where i have manually filled the dates and automatically calculating the 'Amount' but i was curious to know if somehow i could dynamically fill the values in 'Date' column based on month name in header?
View 9 Replies
View Related
Sep 5, 2007
I would like to calculate the sum of investments based on their expiry date and have the totals per month (and year). I have a table that looks like:
TermInterest rateMaturityBalance
24 Months7.12%11 November 200740,000.00
12 Months7.74%13 November 200750,000.00
24 Months7.05%10 January 200853,889.12
12 Months7.85%11 January 2008120,000.00
12 Months8.02%22 March 200817,000.00
36 Months6.68%30 June 200832,000.00
I'd like to have something like:
Nov 07 90,000.00
Dec 07 0.00
Jan 08 173889.12
and so on...
Admittedly I am an Excel novice, so excuse me if my question is dumb and has a simple answer (actually I hope it has :-) but I have tried to find a solution by searching forums, my books, online help, I tried my luck with sumif and SUMPRODUCT functions, even used the conditional sum wizard, but I can't get it right
View 4 Replies
View Related
Apr 7, 2008
Not sure if this is possible. From the attached spreadsheet (reduced to only 2 worksheets), i'm trying to create a formula that will find how much an individual trader made for the month (worksheets 1-31 being a month). The problem is a trader will not be at the same stall everyday and may pay by either cash or cheque.
View 3 Replies
View Related
Dec 22, 2008
1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.
2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.
3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).
View 5 Replies
View Related
Feb 3, 2008
I hv following table :-
Summary Report FGH1how urgent How ImptRank2highHigh 3midhigh 4LowHigh 5highmid 6Midmid 7Lowmid 8Highlow 9midlow 10lowlow Spreadsheet FormulasCellFormulaF1=+'Mega_Variable (#1)'!R4G1=+'Mega_Variable (#1)'!R5F2=+'Project (1) '!U63G2=+'Project (1) '!U64F3=+'Project (10)'!U63G3=+'Project (10)'!U64F4=+'Project (12)'!U63G4=+'Project (12)'!U64F5=+'Project (5)'!U63G5=+'Project (5)'!U64F6=+'Project (7)'!U63G6=+'Project (7)'!U64F7=+'Project (9)'!U63G7=+'Project (9)'!U64F8=+'Project (4)'!U63G8=+'Project (4)'!U64F9=+'Project (2)'!U63G9='Project (2)'!U64F10=+'Project (3)'!U63G10='Project (3)'!U64 Excel tables to the web >> Excel Jeanie HTML 4
I need code , when run it will fill in the ranking number :-
Summary Report FGH1how urgent How ImptRank2highHigh13midhigh24LowHigh35highmid46Midmid57Lowmid68Highlow79midlow810lowlow9Spreadsheet FormulasCellFormulaF1=+'Mega_Variable (#1)'!R4G1=+'Mega_Variable (#1)'!R5F2=+'Project (1) '!U63G2=+'Project (1) '!U64F3=+'Project (10)'!U63G3=+'Project (10)'!U64F4=+'Project (12)'!U63G4=+'Project (12)'!U64F5=+'Project (5)'!U63G5=+'Project (5)'!U64F6=+'Project (7)'!U63G6=+'Project (7)'!U64F7=+'Project (9)'!U63G7=+'Project (9)'!U64F8=+'Project (4)'!U63G8=+'Project (4)'!U64F9=+'Project (2)'!U63G9='Project (2)'!U64F10=+'Project (3)'!U63G10='Project (3)'!U64 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Jul 7, 2012
Every month I need to consolidate the individual report in a single sheet for that I was doing with copy paste options but I need this to happen through macro or some other short way.
View 5 Replies
View Related
Apr 9, 2014
I am trying to create a scoreboard or leaderboard for my job. I am using VLookup to pull information from another worksheet and that working fine. And when I get that information I am ranking it. Here is my problem... Some of the data cells that are being pulled using VLookup are blank totally blank so when I try to rank it, it say #Value!. Basically I want that blank cell to be a zero instead so that I can rank it 0 being the lowest score 3in the ranking.
View 3 Replies
View Related
Jun 27, 2014
I need a formula to create the following:
In Cell F5 is 26, so the percentage should be 101% (100+1). How can I modify the below to add 1% when the number goes over 100%?
Do I have to create two columns? one for the below and one if the F6 is over 100%?
IF(F6="N/A","N/A",IF(F6>=25,1,IF(F6>=19,0.8,IF(F6>=13,0.6,IF(F6>=7,0.4,IF(F6>=1,0.2,IF(F6
View 5 Replies
View Related
