# Using Offset From Latest Month To Calculate 3-month Average Within A Range

Jul 1, 2009
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).

Jan-09

Feb-09

Mar-09

Apr-09........

View 9 Replies
ADVERTISEMENT
Mar 6, 2013

Cell C6 contains a drop down from which a month can be selected, range is Oct-12 to Sep-13

Column AB will contain a YTD sum of actuals, Column AC will contain sum of budget.

For example,

Actuals: If Mar-13 is selected in cell C6, I would like cell AB10 to sum D6+F6+H6+J6+L6+N6

Budget: If Mar-13 is slected in cell C6, I would like call AC10 to sum E6+G6+I+K6+M+O6

2nd scenario

Actuals: If Jun-13 is selected in cell C6, I would like cell AB10 to sum D6+F6+H6+J6+L6+N6+P6+R6+T6

Budget: If Jun-13 is selected in cell C6, I would like cell AC10 to sum E6+G6+I+K6+M+O6+Q6+S6+U6

I've been reading up about an offset function but can't get to grips with it - should I use that, or an index/match mix?

View 1 Replies
View Related
Jan 2, 2014

I have a table that looks like this (its basically a historical data of a stock exchange):

Date

Index

January 4, 2010

[Code]....

The List continues till the current Date.

I want to calculate Average Index Values of a Date of each month within a Date Range.

Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.

Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.

View 1 Replies
View Related
Mar 20, 2008

I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %

View 9 Replies
View Related
Jan 28, 2010

I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09

1 Jan 09

1 Jan 09

1 Jan 09

2 Jan 09

2 Jan 09

3 Jan 09

3 Jan 09

3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

View 9 Replies
View Related
Dec 23, 2008

I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:

Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)

- $25K-100K, recognize in two equal parts in months N + 1 and N + 2

- over $100K, recognize in three equal parts over 3 months

N + 1, N + 2, N + 3 ...

View 4 Replies
View Related
May 8, 2008

I collect unique prices each day. I am trying to find a way to determine the average of the numbers collected from the 21st of the previous month to the 20th of the current month. This formula will need to calculate for multiple months and years. So for example, I need Feb2008 average-which would be the average of numbers found between Jan21-Feb20, I then need Mar 08 average which would be data from Feb21-Mar20 etc. My spreadsheet is setup with the first column having the dates (ex. 01/01/08, 01/02/08 etc) and the second column containing the value for that particular date ($2.85, $3.00 etc).

As the number of days between the 21st and 20th change each month, I just can't seem to find a way to do it without a whole lot of manual effort.

View 9 Replies
View Related
Mar 20, 2009

I have log data in two columns:

Column A: Date/time (at 30 minute intervals)

Column B: Numeric data

On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.

The end of the range is determined by the month in the current row.

I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.

I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.

Manual

=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536

View 9 Replies
View Related
Oct 11, 2012

I am in need of a vlookup formula that will return a value that corresponds to the latest day in a month. Example:

Column A had dates in format 1/3/2012 ect....

Column B is a $ amount

Column C is a date in format Oct-12 ect...

I want column D to be a formula that match up column C's month in column A and return the $ in column B that corresponds to the latest day in the month. IN the example above it would returna value for Oct 31 (if there was one), Oct 30 next and then Oct 29 ect...

So if column A had October dates of: 10/1, 10/4, 10/17 and thats it in Oct then it would show the 10/17 $ in column B.

View 2 Replies
View Related
Feb 26, 2014

I have attached a spreadsheet which shows my desired result which often works.

Range C2:C13 is my desired result...

The problem /criteria is...

For every 140 meters sold the sales person receives Â£140 commission.

Meters (range B2:B13) can be carried to future months.

I am looking for a formula for range C2:C13 which calulates if commission should be paid in this month or not and if so, how much.

View 5 Replies
View Related
Mar 13, 2009

i have the following formula

=SUMPRODUCT(--(TEXT(DSA!$E$5:$E$10000,"mmmyyyy")=$C$4&$C$3),(DSA!$AW$5:$AW$10000)*(DSA!$F$5:$F$10000="")*(DSA!$AJ$5:$AJ$10000="TLS"))

which basically looks at the Month and Year in reference cells then looks through my data on a sheet called DSA..

I have a colum on the DSA sheet AX5:AX10000 where there is a number

I want to be able to calculate the average of these numbers but only if the conditions in the above formula are met.

For example, if the month was MAR 2009 then i would only have the average of numbers present in Colum AX on the DSA sheet for March 2009.

View 9 Replies
View Related
Nov 23, 2006

I am trying to input monthly budgets based on a yearly budget for which I already have a few month's budgets. For example:

Jan - ?

Feb - ?

Mar - ?

Apr - ?

May - ?

June - $1105

July - $1325

Aug - $1470

Sept - $835

Oct - ?

Nov - ?

Dec - ?

TOTAL YEARLY BUDGET - $10,000

I need to find out what the other 8 months are, on average.

I'm looking for something along the lines of

each month's average IF SUM(A3:L3)=M3

View 9 Replies
View Related
Jan 28, 2014

I have a list of dates in Col A, then data in B. I want to take an average of the data between the first Tuesday of every month. What's the best way to do this?

So in Jan 2014, that would be all data from the 7th until the 4th. Inclusive of the 7th, not inclusive of the 4th.

View 2 Replies
View Related
Feb 9, 2009

I have a sheet that contains a number of documents entered into a system by a user.

Each month those number of documents are average by the number of working days in a month.

I'm using this formula, =AVERAGE(C4,22)

I would have to edit this for each month with the number of working days.

Is there a way I can have this formula automatically find the number of working days in a month specificed and average it out?

View 9 Replies
View Related
Apr 25, 2008

I am looking for a macro that will allow me to find the mean for all of a specific time during a month. For Example, I want to find the average percentage of capacity for every day at 11:00 am for the month of January.

Percentage of Capacity By Hour Hour Month/Day/Year

0.00% 11:00 1/22/2007

0.00% 12:00 1/22/2007

0.00% 13:00 1/22/2007

0.00% 14:00 1/22/2007

0.00% 15:00 1/22/2007

0.00% 16:00 1/22/2007

0.00% 17:00 1/22/2007

0.00% 18:00 1/22/2007

0.00% 19:00 1/22/2007

0.00% 20:00 1/22/2007

0.76% 21:00 1/22/2007

1.59% 22:00 1/22/2007

1.38% 23:00 1/22/2007

2.68% 0:00 1/23/2007

1.75% 1:00 1/23/2007

3.65% 2:00 1/23/2007

3.40% 3:00 1/23/2007

2.41% 4:00 1/23/2007

View 5 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
Jun 4, 2014

So I have some data that I would like to have average only if that data was entered in the same month and year as specified in another cell. What I have tried so far is:

[Code] .......

-RenewalMonths is a dynamic range where each cell in the range shows the month of the date in that row.

-RenewalYears is the same but for the years.

-RenewalOverallStuff is a dynamic range where I would need to average the data that meets the criteria.

View 2 Replies
View Related
Feb 17, 2007

I have a list of groundwater level values in 800 different wells that were measured in April and October of each year from 1982-present. The problem is that they are listed in order:

Date ELV

04/16/1981120.37

09/17/1981119.25

04/10/1982120.52

10/10/1982118.1

04/10/1983124.36

10/10/1983120.43

etc....

I want a formula that will average all the April values and another one that will average all the October values.

View 5 Replies
View Related
Mar 2, 2014

I am performing a given exercise every day in the month of march. I have the dates all lined up in my spreadsheet, with the sum at the bottom. What I want to do is have another cell that will give me my daily average of performance. For example today is the 2nd and I have performed this exercise 360 times, making my daily average 130 per day. If I performed 200 of the exercise tomorrow I would have a grand total of 560 repetitions making my daily average roughly 186 per day (the day, march 3 divided by the number of repetitions, 560) and so on and forth.

I need a formula that will automatically calculate that daily average as I progress with the month. But I want the calculation to stop at the end (i.e. I don't want it to continue as the year progresses, meaning the divisor keeps going throughout the rest of the year). Basically, I guess, I am looking to fill in the daily repetition and the divisor to increase each day as I do so. My average is to be displayed in cell B36, so today my formula should read (=2/B36) and on March 31st the formula would be (=31/B36).

View 1 Replies
View Related
Oct 16, 2009

I have two columns: 1) Last Date of each month from 2005 and 2) Value.

Date format: dd.mm.yy

For example:

31.01.05 1.7

28.02.05 -0.7

31.03.05 2.6

30.04.05 0.4

....

How can I find the average of second column of january months only? I tried countif, sumif and many others, but cannot find the solution.

View 3 Replies
View Related
Mar 16, 2010

See Attached.

The YTD 10 column is fine that will change everytime you update each month. I was wondering if there was a formula I could use for the Rolling 12 month Column.

At the moment it is averging from Mar 09-Feb10 which is correct but when it comes to putting in data for Mar 10, the Rolling 12 month column will obviously not change.

Is there a way of moving the formula along one each time you input data into a new month. So when Mar 10 has been entered the rolling 12 month column will pick up Apr-09-Mar-10 then when Apr 10 has been entered the formula will change again to May-09-APr-10?

View 6 Replies
View Related
Jan 15, 2009

I have a workbook with two sheets - DATA and SUMMARY.

DATA has two columns - date and data_value. Data will be added to this sheet on a regular basis

SUMMARY has two columns - month and average

In the column for average I would like a formula to calculate the average of data_value for each month without having to manually determine the range for the particular month.

View 2 Replies
View Related
Jan 13, 2014

I am trying to create a macro (pretty new) to take a sheet of data and out put the average of each column by date. The data will be filled every month and the numbers i need should be one for the 1st, the 2nd and so on for each day.

Here is a sample of the data in the sheet, i would attach a spreadsheet but cant seem to find out how.

TimeStampUTC

Air Volts

Dry Volts

Dirt

Dirt Fraction

Temp

12/1/13 0:47

4.1180

2.3714

0.5735

-0.6800

11.1674

[Code] ............

View 3 Replies
View Related
Jul 29, 2008

I have a list of ages of people who cancelled their account. I'd like to find the average age of people within each month.

So column A = age

and column B = cancel date

Let's so for January 2006 I'd like to find the average age.

Logically this is how I see it...

Average Column A (If column B >= 1/1/2006 and < 2/1/2006)

View 9 Replies
View Related
Aug 20, 2008

I'm having difficulty understanding this concept, more so the formula.

So to figure out the MTD for Day 1,2,3,4, how would it be?

I want each day to have a MTD

etc.

Day 1: 33,966,611.42

Day 2: 33,966,611.42

Day 3: 33,966,611.42

Day 4: 34,074,098.36

View 9 Replies
View Related
Aug 13, 2007

I am after a UDF to calculate the month based on a a date. I cannot use the month function in excel as the dates are slighlty different.

ie

05/01/07 - 06/02/07 = Jan

06/02/07 - 05/03/07 = Feb

View 9 Replies
View Related
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
Jul 10, 2014

I have created a time sheet in excel (see attached) that will be part of the larger workbook that will be linked with other sheets to auto fill in most fields. I am wondering if there is a way for an user to enter a Month and a Year at the top of the page and that in turn automatically fills in the days of the month by week.

So in attached sheet there are 5 boxes representing 5 weeks in a month. So if we used May 2014 as an example I would like to know if there is a way that once May 2014 is entered in up to top that. Excel fills in the dates in Week #1 with under Thursday showing 1st, under Friday showing 2nd as on for the entire month...

So as the month go by all user has to do is state the month and year and excel fills in the weekly dates for each day in month.

Attached File : Time and Attendance.xlsxâ€Ž

View 2 Replies
View Related
Apr 21, 2009

I have a column called "Weekly Working Hours" which totals the number of hours worked per week. The cell is filled in every Saturday.

In the next column I have "Average Weekly Working Hours per Month" which needs to calculate the average number of weekly hours every four weeks, filled in every Saturday.

Please see attached file. I am referring to columns J and K ....

View 12 Replies
View Related
Aug 4, 2009

I have 5 years of data in 2 columns:

Col A. Col B.

8/2/2004 Value 1

to

7/31/2009 Value n

I have a table set up as follows

1 2 3 4 5 6 7 8 9 10 11 12

2004

2005

2006

2007

2008

2009

I was wondering how I could construct a conditional statement to pull the associated values with the given month and year in the table...

I tried the following to no avail... I'm just getting a zero value:

=AVERAGE(IF(MONTH(J6:J1255)=AC$35,IF(YEAR(J6:J1255)=$AB37,K6:K1255)))

View 9 Replies
View Related
Apr 25, 2008

How do I average the data in column G, based on two conditions (time in column H = $H$1, month in column I = month in $I$1)

Here's the formula I came up with, but it is including blank cells in the calculation.

{ =AVERAGE (IF ($H$3:$H$100=H1, IF (MONTH ($I$3:$I$100) = MONTH(I1), $G$3:$G$100, FALSE), FALSE)) }

View 6 Replies
View Related
Apr 4, 2013

I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.

I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.

VB:

Dim Cell As Range

' 1st cell with the posting date

Set Cell = Range("A2")

Do While Not IsEmpty(Cell)

If Cell = "Date" Then

[Code] .....

View 5 Replies
View Related
Feb 13, 2010

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 Related
Dec 1, 2012

In a sheet I enter the following:

... in A1 a year (say 2012)

... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)

How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?

View 3 Replies
View Related
May 1, 2006

I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.

=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday

to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.

View 11 Replies
View Related
Jan 21, 2010

I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name.

Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?

View 9 Replies
View Related
Oct 7, 2008

I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.

View 4 Replies
View Related
Apr 17, 2013

I am having trouble creating a formula to capture the "first of the month after 60 days" from the Hire Date.

Example, Hire Date is 01/15/2013, should return 04/01/2013. Would the ROUNDUP function be useful here?

View 8 Replies
View Related
Nov 28, 2006

I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.

Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?

IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".

The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).

View 9 Replies
View Related
Jan 12, 2010

Sample data:

02-06-09 to 18-06-09 earned $2500

19-06-09 to 23-08-09 earned $3500

24-08-09 to 31-12-09 earned $4500

I am thinking if there's a formula that can calculate the interest earned for each month from Jun-09 to Dec-09.

View 9 Replies
View Related