Free Microsoft Excel 2013 Quick Reference

Formula for calculating 12 month rolling period

I'm trying to link two absence spreadsheets together, each worksheet being a
separate month. I need a formula to calculate the absences on a rolling 12
month period, therefore, each time I input the current month's figures, it
adds them to the 12 month rolling total but takes one month off the beginning
of the 12 month period!


Post your answer or comment

comments powered by Disqus
I'm trying to link two absence spreadsheets together, each worksheet being a
separate month. I need a formula to calculate the absences on a rolling 12
month period, therefore, each time I input the current month's figures, it
adds them to the 12 month rolling total but takes one month off the beginning
of the 12 month period!

Please can anyone tell me how to calculate 12 month rolling in excel? I have the data for 12 months, and i need to calculate 12 month rolling. Not sure how.Thanks

I cannot remember how to calculate a 12 month rolling average for my
emissions. I believe I know the formula but need some reassuring.

Thanks.

i have in column a the 12 month (a1:a12)
in column b i have to write the formula for the sales result of a saleman
eg in b1 i will have his sale for january (=january!m12)
i want to copy down the 12 month so i will obtain in b2(=february!m12) in b3 (=march!m12) and so on... i did not find the way of doing this and i dont want to do it by clicking the month sheet and then the cell in wich i have the sesult
thank

Hi Guys,
I'm trying to calculate a monthly leave accrual formula for staff on a monthwise table.

In the sample file attached, John has joined on 01-NOV-2010 so his leave accrual will start from Nov @ 2.5 per month. But I would like to have a formula that returns accrual as zero for the months prior to his joining.

Similarly, Mary has joined on 15-Dec-2010 so in the joining month of Dec2010 her leave accrual will be (2.5/31 x 17 days of service in Dec) and for the following months it will be 2.5 per month

Like Ali, who joined on 17-Feb-2011 wil have the zero accruals till the month of Jan and in Feb it will be (2.5/28 x 12days of service in Feb )

I hope the sample is clear. Any remote soultions to build on will be helpful.
Thanks

Hello,

I've looked at a few similar methods, but I am having a hard time getting my formula to work

I need a 12 month rolling chart that averages according to the 'start at row' box. Ex: setting it for oct-07 will average to sep-08.

I attached the file for an example. Let me know if i'm on the right track/missing anything.

Thanks!

Not sure where to start with this one as I'm not sure if its actually possible but here goes. Been asked to create a sheet that would track peoples record of totals of days off that would show up as a total in a cell for a rolling 12 month period. I would have:

the persons names
a total for each month i.e. days off that month

I need it to update the totals for the 12 months previous after each new month is added to the bottom of the list.

Hope thats clear, really looking for ideas how to go about this and maybe any examples.

Can someone please help???

What formula do you use to keep a 12 month rolling total of data for example:

August 2005 3.0
Sep
Oct
Nov
Dec
Jan 2006
Feb
Mar
Apr
May
Jun
Jul
Aug
September 2006 2.5
When adding the data from month to month it changes all data to the most
current so all other months will be 2.5 is there a formula I can use to stop
this from happening?

Hopeful

hello,

I have a collum in which i will be entering dates and i'm looking for a sum that will only count the dates that are within the last 12 month.

Can anyone help?

Hello,

I have a collum in which i wish to enter dates. I'm looking for sum that will only count the dates that are within the last 12 months from todays date.

Can anyone help?

Formula for Calculating (Accrual) Off Time (PTO)
I need to develop a tracking spreadsheet (for 18 employees) that will calculate accruing vacation time (on one worksheet) based on the following parameters:

Employees who have worked less than 1 year with the company:
- 10 days
Employees who have worked at least 1 year with the company:
- 12 days
Employees who have worked at least 2 year with the company:
- 13 days
Employees who have worked at least 3 year with the company:
- 14 days
Employees who have worked at least 4 year with the company:
- 15 days
Employees who have worked at least 5 year+ with the company:
- each year will add 1 day until reach the max 20 days

In addition I need to be able to deduct vacation time used. Does anyone have any suggestions for layout or for a formula that can do part of these functions? I appreciate any advice!

Hello I am having trouble turning the Dynamic Chart attached into a 12-month rolling chart. I am not very familiar at all with the Offset formula as well as Ranges and would greatly appreciate it if someone can help me. I created a very simple dynamic chart which has been modified from the most commonly known out there to work with horizontal data and Vlookups, thanks to Andy Pope from an older post linked below. I would like to know where to insert code into the Range formulas (CTL-F3) which would create a 12-month rolling chart. Thank you once again to any that could help!

Referenced old thread
http://www.excelforum.com/excel-char...tal-table.html

Vertical Dynamic 12-month rolling chart (w/out Vlookup solution modification)

DOES ANY ONY HAVE THE FORMULA FOR CALCULATING MIN MAX LEVELS FOR INVENTORY
CONTROL

Hi

I wonder if anyone can help with a grapfh query. I need to be able to plot 12 months rolling data on a graph, but I need to be able to refresh the data monthly and the graph to change to the next 12 months rolling data? But I don't want to have to ask the graph to change its source data every month.

thanks
Brissy

I am putting together a time sheet, and I am trying to figure out a formula for calculating overtime hours. Anything over 8 hours is considered overtime, and I would like those hours to appear in a different cell.

In my example (attached), you can see that the hours for 1/20 are over 8 hours, and 10 hours is showing in the "Total Regular Hours" column. I would like for all hours entered in this column to stop at 8, and any hours over 8 would show in the "Total Overtime Hours" column. (In this case, there should be an '8' in the "Total Regular Hours" column and a '2' in the "Total Overtime Hours" column.)

I've tried a few different options, but I can't figure out what the formula would be to achieve this result. Any help would be greatly appreciated!Timesheet Worksheet.xls

I have a spreadsheet that each month, we populate a new row of data. The rows are already set up in the spreadsheet, but we just populate the new row. We are calculating a rolling 12 month total. Each month, we have to modify the formula below to pick up the last 12 months. For example, next month we will populate data into cell M91, then we need to manually modify our formula to read M80:M91. Wondering if there is a way to have the formula below to look at a range, such as M100:M1, and count the last 12 months? This would eliminate us having to change this each month on several spreadsheets. In Summary: I would like to replace the M79:M90 to count the last 12 months instead of changing the formula each month.
Here is the formula:
=(FVSCHEDULE(1,M79:M90/100)-1)*100

Thanks in advance for any assistance.

Hi I am trying to set up a sick leave list which will work out the cumulative
total for the last 12 months (from last day of last sick entry).

The problem I have as the ist is added to I need a function/formula to work
out 12 months from last date and then add up the no. of sick days from then
to last date.

Below is the chart

Column 1 Start date of sickness
column 2 End date
Column 3 the number of days sick in period (column 2 less column 1 in days)
column 4 the number of days sick in period above excluding week ends (column
2 less column 1 less weekends in days)
column 5 total sick days in last 12 months from date in column 2 going back
a year including weekends
column 6 total sick days in last 12 months from date in column 2 going back
a year excluding weekends

What function or formulas can I use to calculate column 3,4,5 & 6.

Im a not very experienecd in Excel so any help appreciated and make it easy
as I struggle sometimes. Thanks Bill

I have been programming a workbook over the past several months to collect inspection data generated on a daily basis. I have three products (which = 3 worksheets). Each worksheet records the date, qty inspected, qty rejected, and the reason for rejection (reject codes). I need to summarize the data by product and by week and month (qty inspected vs. qty rejected). I originally set up the summary by using the DSUM function and “naming” the criteria for a specific time period, each week (with a begin date and end date) and each month (also with a begin date and end date). However, now that a new year is approaching, I will either lose the sum data for 2002, or have to re”name” all the criteria for begin dates and end dates (week and month) for 2003, and then re”program” a new sheet for 2003 with the DSUM function.
Is there a better way to do this?
Basically I really need to keep the 2002 data, and just add on to it with 2003 data, so that my summary will always contain a rolling 12 month chart. Does that make sense? I hope so. Any help in this matter would be appreciated.

Hello-

I'm trying to create a formula that will show a twelve month average and will change as the month changes. In addition, if i were to have a cell that I can put a "6", "12", or "18" to show those monthly averages. At the same time, have a graph that will update automatically.

Currently i have a workbook set up with a worksheet that has data from 2006 through 2007 for 480 categories. I anticipate using this worksheet through 2010. I was playing on having a second worksheet that has all of the categories set up so that i may be able to see a rolling average for them. I also have an input cell (A1) so that i could put a 6, 12, or 18 in it depending on the number of months i want to see the average for.

I used the following formula =SUMIF(A3:AH3,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-$A$1,1),A4:AH4) but not sure if it is what i'm looking for. Due to how my financials publsih, i almost need to be able to determine the months included, not so much from the current day/month being used in the formula. For example, i'm currently reporting on Dec. 21, 2007 numbers so if i put a "12" in cell A1, i want to see an average for the last 12 months. However, the formula i have doesn't seem to do that. Now if i put data in for all my dates (Through 2010), then it gives me an average but from Jan 07 through Dec 2010. I hope i'm making sense. Any assistance would be greatly appreciated.

Thank you.

Is there a way to add a rolling 12 month date filter for 'last 12 months' to
a pivot table? I see how to add last year, last month, etc., but I need a way
to filter for last 12 months (or even last 6) so that I don't have to update
the date filter every month. This pivot table is for manufacturing production
data and is connected to an analysis services data cube.

hello. i need help with creating a formula that will calculate the sum of balances based on a 12 month rolling period as of specified date. for example, as of 03/31/11 i would like a sum of balances for the period of 04/30/10-03/31/11; as of 04/30/11, sum balances 05/31/10-04/30/11, etc.

my data set is set up similar to table below...so in D8, based on date in D6, return sum for the 12 rolling months (04/30/10-03/31/11).....can someone help with this formula? not really sure where to start...index/sumif??? thanks in advance!!!

A B C D E F G H I J K L M N O P Q R 1 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10 Aug-10 Sep-10 Oct-10 Nov-10 Dec-10 Jan-11 Feb-11 Mar-11 Apr-11 May-11 2 50,000 50,000 2,500 75,000 51,000 3,652 58,236 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 3 4 587,888 expected total as of 03/31/11 5 6 As of date: Mar-11 7 8 TOTAL: 9

Hi, I have a list of months that i use in a combobox on a userform. The list is populated with the rowsource property referring to a list of months on a worksheet named "lists". I use formula's for the list so that the current month is always 2nd from the top, with the previous month on top and the rest of the months in order downward so that the most likely choice is always on the top.

In the next column each of the 12 cells say 1st - 15th. In the 3rd column I would like for each cell to say 16th - 31st or 16th - 30th or 16th - 28th depending on the month. Also would like it to say 16th - 29th for february on a leap year. The idea is that the user selects a month from the list and the 2nd combo box offers up 2 choices, one being 1st - 15th, and the 2nd being 16th - whatever day the last day of the month is.

example of worksheet list
column G..........column H..........Column I
Month...............1st - 15th..........16th - Last day of month

Hello, I am hoping someone can help me;

I am doing my own accounts for my business and I'm trying to set up a system which on one sheet, shows the running total of takings so far in the year, my allowable expenses, the resulting taxable profit and then the goverment deductions (tax, NI and student loan). I'd then like to have another sheet of monthly summaries, detailing the same as the above but so I can take a wage and put some money away for the tax bill at the end of the year. I'm struggling to think of a formula which brings in the tax built up so far, minus what I've already put away from previous months to give that months contribution- I'm hoping this makes even the slightest bit of sense lol.

I've based my annual summary on the threshold I'm allowed before I start paying and I'm using an IF formula- if my taxable profits are less than 7500 (not right I know but for the example) then I pay no tax, if its more, I take 7500 from my profit and it gives me 20% of that.

I basically need to know how to average this figure over the 12 month summaries. I don't know if its possible or even if my question makes any sense, but hopefully someone can help me?!

I AM TRYING TO SET UP A SPREADSHEET FOR THE YEARS OF SERVICE OUR EMPLOYEES
HAVE SERVED WITH OUR COMPANY. WE HAVE ONE PERSON WHO RESIGNED AND WAS
REHIRED, TWICE. BELOW IS THE FORMULA I USED FOR EMPLOYEES WHO WERE HIRED AND
ARE STILL CURRENTLY WORKING FOR THE COMPANY.

=DATEDIF(B27,C27,"Y")&"YEARS,"&DATEDIF(B27,C27,"YM ")&MONTHS,"&DATEDIF(B27,C27,"MD")&"DAYS"

PLEASE HELP ME EDIT MY FORMULA FOR THE EMPLOYEE WHO HAS COME AND GONE
SEVERAL TIMES. THESE ARE THE DATES THIS EMPLOYEE HAS SERVED WITH THE COMPANY.
02/01/88 - 05/06/88
01/15/89 - 06/15/06
02/01/99 - PRESENT


No luck finding an answer? You could always try Google.