Work has given me a hourly sales data for the last 2 weeks, and Last years hourly data for the equivalent 2 weeks.
The question is compare the average of the last 2 weeks against the average of the equivalent 2 weeks last year and evaluate if the trading performance is majorly different.
What formulas to use? Should I use percentages or leave it?
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 RelatedI 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.
I want to compare the sales from Jan'12 - Oct'12 VS Jan'13 - Oct'13 by showing them in stacked Columns. So will have 2 stacked columns one for year 2012 and beside it for year 2013 and each one will show the months.
View 3 Replies View RelatedThere are two columns in an excel sheet, one is date of birth and other is date of reteirment
looks somewhat like this:
D_O_B D_O_R
5-Mar-53 31-Mar-11
30-Jun-57 30-Jun-15
20-Jun-51 30-Jun-09
2-Feb-55 28-Feb-13
2-Jul-51
13-Oct-55
1-Sep-51
7-Jul-54
14-Mar-53
3-Aug-50 3 0-Sep-13
Some of the dates in D_O_R are missing. I need to fill in up all the dates in D_O_R column.
D_O_R is = 58years+D_O_B(NOTE the dates should come as last date of the month)
The formula that I currently have in E2, is giving me the number of years served by an employee. Is there another formula that can give me the number of years each employee has served? This is the formula that I have in E2
[Code] .....
Attached File : VACATION DAYS ACCURED.xls‎
Basically I need to add a column to my source data so I can use it as a filter on my Pivot Table in a different workbook. - something just as simple as TRUE/FALSE if the date is YTD for all years would be ideal.
Have attached an example if that makes it any clearer! nice simple formula would be ideal as sheet is around 600,000 rows long and growing!
I have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet).
I need to calculate the HIGHEST 3 consecutive year average salary for each (if they have less than 3 years with salary, then it should just average the years the do have, be it 1 or 2 years).
Here is the kicker: some people have breaks in service (for these years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.
So if someone had salary figures in years 1, 2, and 4, but a blank in year three, the average of years 1, 2, and 4 would constitute one three year average (whether or not it is the highest is a whole other matter...).
I have been round-and-round the best way of doing this. I was thinking of maybe creating a UDF that calculates a three average, then do it up to 8 times (one for each starting year) in 8 "helper columns", and taking the highest average.
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.
I am trying to develop a report for work comparing actual sales with previous year and id like to insert a traffic light system (eg red and green arrows) to semplify current performance vs last year.
View 2 Replies View RelatedI am using a formula for weeknumbers and formatting cells based on their values but have just realised it will not work at year end.
The formula in D2 is as follows:
=IF($C2<$O2,"green","blank")
C2 is the weeknumber of an incident based on the actual date in B2 (e.g. 01/06/2014) and uses the formula: =WEEKNUM(B2). O2 is the weeknumber of the current day and uses the formula: =WEEKNUM(NOW()).
So cell D2 states "green" if the incident happened over a week ago, and states "blank" if the incident happened in the current week.
If I used this at year end when the incident weeknumber may be 52 and the current weeknumber may be 1 - the formula would not work as it would state "blank" rather than "green", even though the incident happened over a week ago.
How can I get the year encorporated within the weeknumber?
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
View 4 Replies View RelatedI have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.
View 9 Replies View RelatedI have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.
My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.
I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....
All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.
Column A Column B
201 $200
202 $300
203 $450
I work in a computer lab and we have to keep track of how many people sign in (using excel 2010), and how long they were here.
I'm looking for a way to count how many entries were made per hour for every hour someone was signed in.
For example someone signs in at 9:22 am and leaves at 3:34 pm.They were here from 9-10, 10-11, 11-12 etc.. and its getting very tiresome to go through and manually enter a "1" for each individual hour in each cell under the hour.
Is there a way I can feed the in and out times into a spreadsheet and have it automatically count how many people were in the lab each hour?
A
B
C
D
E
1
2
4
2
2
In
Out
8 am
9 am
10 am
[Code] ...........
I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.
View 3 Replies View RelatedI 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)
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies View RelatedI 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]....
Is it possible to take data in format dd:hh:mm and convert to working days - ie. 8 hour days?
For example:
02:12:15 (60 hours and 15 minutes .. or 3615 minutes)
to be converted to:
07:04:15 (7 working days: 4 hours: 15 minutes)
I have a time table which some hours are 3 hours time period. I want to distribute to new table hour by hour this kind of hours, or hour to same hour period.
Attached file, defined example and detail.
I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.
View 4 Replies View RelatedI have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.
Starting in cell B59 and C59 I'd like to display their name and sales respectively.
Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
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.
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here
Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.
Month 2012 2013
Jan 300 100
Feb 275 100
Mar 400 150
April 650
May 454
June 800
July 500
Aug 375
Sep 525
Oct. 300
Nov 410
Dec 510
Sample file is attached : Comparison_Years.2011.xls‎
