Calculating Totals From A Date Range On A Rolling Calendar Year?
Feb 25, 2010
I have a spreadsheet that tracks “points” for hourly associates on a daily basis that totals weekly and monthly. This spreadsheet works on a rolling calendar year instead of a fiscal and I need to be able to capture the totals for periods of time by days.
For example, I would need to view how many points ‘employee x’ has from 2/25/09 through 2/25/10. I have attached the spreadsheet, which includes tabs for each week ending and a summary page.
I'm having a data only pull week number and year. We are using Fiscal calendar starting in July. For example, A1 = Week number and A2= Year. How to set up a formula to retrieve a date for this? If A1 = 2 , A2 = 2013, the date will be 07/14/2012. I want the date pull of on Saturday every week.
I have an excel sheet setup with several tabs which are organized by work days in a week. (i.e. aug 18, aug 19, aug 20, aug 21, aug22, aug 25, aug 26, aug 27, aug 28, aug 29) Sample file has been included.
I would like to do a rolling total of 1 particular cell (the same location on all the sheets, i.e. cell a2 on all sheets) for the past 4 weeks, and it automatically adjusts itself based on today's date (i.e. today is august 27, it will total everything from jul 30, 31, aug 1, 4, 5, 6, 7, 8, 11, 12, 13, 14, 15, 18, 19, 20, 21, 22, 25, 26.).
Is it possible to do this?
I am not familiar with VBA or Macros, so solutions using either of these will require some detailed explanation for me to understand how to apply them.
i'm trying to count dates within a 2 week date range. I don't want to update the formula every day to the actual dates that would make up two weeks so I tried this formula but it keeps giving me an error.
What it is, I have a rolling report for the whole year, each with a score for the week.
However, it needs to show the whole year, but I need a seperate box with a summary of only the last 4 weeks on a rolling basis, ideally this would automatically update depending on the week.
My employer uses a scoring system to give a value to an individual’s sick record in the current rolling year.
Each person, has recorded, the number of sickness occasions and the total number of days in this rolling year period, these details go onto the final sheet where a score is calculated.
At present to get an accurate period, I have needed manually to deduct days to produce the correct time frame, ie 27/3/06 to 28/3/07 or when the sikness carrys on into the folling month.
I have tried without success to modify the rolling year formula, which as it is, will include the whole of March 06.
I've started with an Attendance tracking template that I found in Excel. My company has implemented an attendance policy where each employee is allowed up to 10 points before they are terminated. A 1/2 point is given for arriving late or leaving early and a whole point is given for an unscheduled absence. The points stay on the employee's record for one year, after that time period it drops off their record. We want to monitor each employee's cumulative points.
So, I'm looking for a way to look back 1 year from the current date and add up the cumulative points over that period.
I have just been asked to update a file on a quarterly basis which contains a large number of charts based on the performance of various funds against their respective benchmarks. In column A I have dates going back to 2008 and then the fund and benchmark figures in the next columns. Each quarter there will be a need to update the charts to the new rolling 3 year period, i.e. in October when I update the file, the charts will need to show the results from 30.09.09 to 31.09.12
As there are 20 charts this can be quite time consuming? The charts sit on the same tab as the data, under the columns at the bottom.
I have several worksheets with thousands rows (independent variables) and hundreds columns (all dependent variables). Each line basically gives me hundreds values for each independent variable - see below: ... C9 39.65 653.95 5.28 163.56 99.56 14.49 ... ... E9 7535.92 21500.56 2835.88 3122.98 7225.34 5371.25 ... ... G9 111568 298021 12940 31645 181797 36996 ... .... ...
I need to know how the values in each row are distributed, and I ideally plot a 2D column graph of the distribution. Is there a way to do that and create/program a macro (with relative button on the workboook) that does it automatically once clicked?
Very often there are outlying values (bigger or smaller by a factor of 1000 or even more), mistakes, which I would like to identify and fix possibly
What I have is a spreadsheet with a whole bunch of sporting data on it. see attachment for an idea of the layout, Stats.jpg
In these data each round a player is given a rating. What I want to do is calculate is the average rating for each player across the last 3 and 5 games they have played. When a player misses a game they get a rating of 0 for the round. I want 0 ratings to be ignored in the running averages. Additionally, games yet to be played have a 0 in the cell and should be ignored. To get overall averages ignoring 0's was simple I used the averageif function, but I couldn't figure out how to use that but only extract the last 3 non 0 values. I currently have data up to round 12, so if a player has played in 12, 11 and 10 then the 3 round average should just be the average of these 3, but if they missed for example round 11 then the 3 round average should return the average of 9, 10 and 12.
The data is set out in the following way.
In column B I have a list of player names from cells B3:B618.
Across row 1 from N1:AM1 are labels "round 1" "round 2" etc.
The ratings for each player in each round are in N-AM and from 3-618. i.e. N3:AM3 contains the weekly rating for player 1, 4 contains player 2 etc.
I would like the 3 round averages to be returned for each player in column L and 5 round averages in column M.
I am trying to calculate a total of a supplier between 2 dates. The total must fall in between the entered dates but the dates can be any given date and not limited to the dates in the supplied list. See attached scaled down example?
I currently have a worksheet that when a user clicks on the cell, a calendar form opens. From here they can select a date, click ok, and the date gets placed in the corresponding cell they clicked on.
Now if the user needs to change that date, i want them to be able to click on the cell again, and when the calendar opens, the date in the cell would be selected on the calendar. I was able to do this in a userform, but im not sure how to edit it to work on the worksheet. I am using the below to populate the calendar.
Code: private sub worksheet_selectionchange(byval target as range) if not intersect(target,range("aa16:aa24")) is nothing then .show calendar When the user clicks the date, the below code places it in the cell.
Code: private sub ok_click() with active cell .value=calendar1.value end with unload me end sub
Now the below code was used when I was using a userform, but im not sure how to edit this to pull the date from the active cell.
Code: privatesub userform_activate() me.calendar1=date if not tb is nothing then if isdate(tb.value)then me.calendar1.value = tb.value end if end sub
I am attempting to build a spreadsheet for work, and I'm having trouble calculating a correct total.
In essence, the bit of my spreadsheet I'm concentrating on is 2 columns:
Column A is headed "Type of Work" and Column B is headed "Completed".
The Type of Work will either be Letters or Memos, and the Completed will either be Yes or left blank to indicate No. For other reasons, No has to be represented by a blank cell.
At the moment, I have 12 rows, going from A2 to B13, with data such as the following:
I need a summary section at the side with the following calculations:
Total Number of Entries Total Number Outstanding Total Number of Letters Outstanding Total Number of Memos Outstanding
However, as I have only populated it with 12 rows of data for my testing purposes, I need to build formulas to take into account the fact that a maximum of 2000 rows may be filled in by other people over the next few months. Therefore, my formulas look like the following:
Total Number of Entries (stored in E2): =COUNTA(B3:B2000)
Total Number Outstanding (stored in F2): =E2-(COUNTIF(C3:C2000,"Yes"))
Both of the above formulas work perfectly, and when I enter a 13th and 14th row, the totals update as I want them to, so I have no problems there.
However, I am struggling to enter a correct formula which will calculate the total number of Letters that aren't Completed.
I did set some names and tried to use: {=SUM((Type="Letters")*(Completed="Yes"))}
but it gave me a #NA error, presumably because the named range was looking at all the empty cells up to A2000.
I need to make a sheet that give totals based on monthly figures from last year. Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments $75.
One month Last year the store made 31.82% on it’s money. The bonus for anything over is $75 per 10% increments. Ie 0.00% $400 0.10% $475 0.20% $550 0.30% $625 0.40% $700 So this month was over last months, 37.18% so the bonus was $3,975.
What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with $75. added to a base of $400 every 10%. This hurt my head trying to get it right and im new to this more complicated formulas.
I need is a formula that averages totals from different months out of the year. I already have a yearly average. That was easy. But what I need is a 3 & 6 month average.
I also need it to be most current, so when I am in September, it will take the 3 previous months and average them and same with the 6 month. Then when I move into October, it would take its 3 previous months, i.e. - July,August, September.
best formula for adding figures in a column that correspond to each month of the year?
I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet.
I have an Incomes & Expenditures spreadsheets. When I add an expenditure to the sheet I can choose which category of expense it is from a drop down list of expenses. At the lower part of the sheet away from main section I have a list of the categories which I would like to have monthly totals alongside. with the formula?
I attach a couple of files that i receive a few times a year and wish to be able to count the numbers in a more effective fashion than i have attached. Essentially, the source data file naturally changes its numbers each time i get the file so the file that i tinkered about with (also attached) would need to have each and every formula moved to tally correctly. This seems to be a waste of time and would gladly appreciate any assistance. Columns A & C are irrelevant for the purposes of the exercise and can be ignored.
I have to be able to count the data according to employee number groups:
0-19 employees 20-49 50-199 200-499 500-25000
While I have already done this on the attached file you will be able to see that its a long drawn out process. Can anyone advise a better way?
I have a program where I can update the calibration due date of an item. I have attached a cut down version of my program showing the relevant areas. There is usually password protection on the worksheet so it can only be edited via the form (the vba coding removes the password protection before editing, then re-enables the password protection after editing). The "Update Calibration" button is usually on a "Menu" sheet.
Once the form is opened a serial number is typed in the textbox. The calendar button is then clicked, which brings up another form with the calendar on. The due date is selected on the calendar. When "OK" is clicked, the date label caption is then changed to the selected calendar date. When "Submit" is clicked, the spreadsheet will search for the Serial Number, once found, the label caption (being the date selected) will be entered into the cell to the right of the serial.
If the day selected on the calendar is greater than 12 the date is entered correctly onto the sheet. example: calendar date selected = 15/01/2010. shown on sheet as 15/01/2010. However, if the day selected on the calendar is 12 or less, the date is for some reason entered incorrectly onto the sheet. example: calendar date selected = 08/12/2010. shown on sheet as 12/08/2010???? What is going on here? how come the day and month are swapped around if the day is less than 12????
I want to select a date of choice from a calendar in a field within an Excel spreadsheet.
I do not know how to do userforms and so that is why I wonder if I can add this in just a spreadsheet.
This is how far I have gotten so far: After doing the following, I have a static calendar setting in my spreadsheet like a text box showing the current date.
Developer Insert More Controls Calendar Control 12.0
I have a report at the moment which shows me the calendar year that an action was carried out in, what I would also like to show is which of our financial years (they run April to March) the action was carried out in.
I have a column that contains dates for an event. I would like to tally quarterly and yearly totals for these dates. What formula can I use to accomplish this?
I have just finished sorting my anniversary list by Month (in alpha order)
B C D E F 1 Hire Date MonthDay YearLast Name First Name 2 04/04/2011April 042011Emp 1 3 04/09/1996April 091996Emp 2 4 08/02/2012August022012Emp 3 5 08/09/2004August092004Emp 4 6 08/13/2001August132001Emp 5 7 08/16/2010August162010Emp 6
Is there a way to sort by Calendar year? Jan, Feb, Mar, etc.
I want to find the average value for each Jan 1st, 2nd, 3rd ..., for a data set of five years. How do I do this quickly? I can filter, but would have to do that 365 times and involves a lot of pasting. I tried a pivot table, but basically came down to the same problem, although I don't understand pivot tables that well.
I've attached a workbook which contains two sheets: PAYM DEVICE_PAYM
Data is updated on DEVICE_PAYM each day with the figures I need located in column "AN". I need to be able to copy the data from "AN" and paste it into a column on the PAYM sheet. The destination column on the PAYM sheet needs to be specified by inputting a specific date - ideally, I would like to use the calendar Add-In as part of this process. I've already input some code for the calendar but am unable to develop this further so that it finds the correct date column and pastes the data across from the other sheet.
