# Calculate Averages Of Individual Months Over 40 Years

Jan 5, 2012
I have a large sheet of daily river flows and precipitation amounts, over 40+ years.

I want to calculate the average flows in each month of each year (ie 11/1968, 12/1968, 1/1969 etc). Dates are in Col A, flows in Col D.

How to average a particular month over all years like so:

{=AVERAGE(IF(MONTH(A2:A15282)=1,D2:D15282))}

But I don't know how to average per month of each year, or how to make a formula that will allow me to quickly calculate the averages of 100s of months.

View 6 Replies
ADVERTISEMENT
Oct 5, 2006

I was asked by a colleague how they could put a persons date of birth in one cell, todays date in another, and return their age in years and months. Accuracy to within a month. This is what I gave them.

=TRUNC((B1-A1)/365.25)&" Years "& ROUND(((B1-A1)/365.25-TRUNC((B1-A1)/365.25))*12,0)&" Months"

View 4 Replies
View Related
Dec 20, 2006

I have two columns with dates (and times) in that I am trying to define how many days, hrs and mins have elapsed i.e. A1 has 12/12/06 21:00, B1 has 17/12/06 21:00. C1 has B1-A1 and is custom formatted to show as dd"days" hh"hrs" mm"mins". In this case it will therefore show as 5days 0hrs 0mins. Which is correct.

However, if more than 1month has elapsed then the format m"m" d"days" h"hrs" m"mins" does not work. For example 17/03/06 03:00 to 20/12/06 07:00 shows as 10m 4days 4hrs 00min, which it clearly isn't.

I know the reason it does this is because it calculates the difference between the two times and adds that to it's 0 value, which in my format is 01/01/1900 00:00. therefore when it adds 277days (the answer) it becomes 04/10/1900 04:00, so my formatting is just calling the month value ('10') and the day value ('4').

I understand the reason it does this, 277 days on from 01/01/1900 is indeed Oct 4th, but 277 days on from 17/03/06 is not 10months and 4 days as there are different length months in between. It also seems to add a month on, possibly because the format for 'months' is between 1 & 12 and therefore cannot begin at 0?

Does anyone know if it's possible to force excel to work out the correct number of months and days have elapsed between two dates and not apply it to 01/01/1900? Or any other possible solution, maybe with a different custom format?

View 4 Replies
View Related
Sep 13, 2009

I am using the following formula to calculate years months and days in Excell 2007

=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"

C7 3/24/04

D7 1/4/08

What is returned is 3 years, 9 months, 136 days

How might if fix it show the correct days?

View 9 Replies
View Related
Dec 20, 2013

I want to display the percentage of occupancy for each month for condo rentals.

I have columns with dates that represent bookings throughout the year. check in and check out dates to be more precise. (A1 and B1 for example) and I have columns with months Jan, Feb, etc. (C1, D1, etc. for example)

The problem I run in to is that I can calculate the number of days between the dates and the fraction of the year (*12 to get a monthly value, but I fail to understand how I can break that up so that I can populate each month with a correct percentage of the total term.

E.g. A1=1-jan-2014 and B1=20-jan-2014 is an easy one because it only covers 1 month:

formula: =(YEARFRAC(A1,B1,3))*12 gives me 62,47% which I could just use as is for that month.

So far so good, but when the period covers more than one month or when the months overlaps, I don't know how to break the outcome up into the appropriate months to display the correct percentage for each month in its own column.

e.g. A1=1-jan-2014 and B1=20-Mar-2014 gives me a result of 253% with the same formula.

100% for November, 100% for December and 63% for January, which I could somehow formulate to break up over different Months.

But then it gets more tricky. What if the booking starts on a day other than the 1st of the Month?

e.g. A1=5-jan-2014 and B1=20-Mar-2014

The result of the formula (243%) is correct, but isn't sufficient to put the correct percentages for each month in their respective column.

What formula(s) should I use to break down the percentages to match the correct fraction of each individual month?

I attached an example of what I have so far for your review : test.xlsx

View 8 Replies
View Related
Jul 31, 2007

I am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.

View 9 Replies
View Related
Jan 1, 2007

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.

View 9 Replies
View Related
Aug 7, 2009

The issue is i want years and months between two dates which are not in computer language. Date like 2008/12 and 2010/01. File is attached for you reference

View 2 Replies
View Related
Oct 14, 2009

Please refer to the attached screenshot of my working spreadsheet.

I'm attempting to concatenate lines 86 and 87, month and year together.

The formula I use is: "=C86 & C87" - but I get a large number instead of "October 2009"

View 4 Replies
View Related
Apr 4, 2012

I have been trying to figure out a formula to convert the amount of months in a certain cell to years.

I have come up with:

=INT(E29/12)&" years "&MOD(E29,12) & " months"

The problem is it comes up with 2 years 3.37063951499227 months

How I change it to come up with 2.4 months, round it to the highest number instead of loads of decimal places? I have tried change cell E29 do 0 decimal places but it doesn't work.

View 2 Replies
View Related
Apr 12, 2007

Which formula should I use to return years and months between two dates.

4/1/05 7/30/25

View 2 Replies
View Related
Aug 14, 2009

Here is my formula:

=E3*(1+E9/365)^(365*E5)

Cell E5 contains a place for you to put in the number of years you want

I want to modify this formula so that it calculates months instead of years, but still be based of a 365 day calendar year.

View 14 Replies
View Related
Dec 13, 2011

Is there a way to show in excel that something feel within a specific time frame? An example being if I want to see the amount of time it took to close a customer service ticket. I would take the closed date minus the raised date, but then I would like to show in a cell it was open from Feb to Mar 2011.

View 5 Replies
View Related
Jan 8, 2014

Was needing to calculate years and months returning it such as 2.10 if 2 years and 10 months.

And also if left blank would not return any value. (There might not be a date yet in H3 in some cells.)

G3 thru G1000 has the hire dates. H3 thru H1000 will be the cell for the formula.

View 3 Replies
View Related
Oct 17, 2009

my spreadsheet is setup with two dates: A date for when someone joins our group A date for when they are released/leave

I want to be able to see exactly how many years/months/days transpired between these two dates. I have tried lots of different formulas with no success.

View 12 Replies
View Related
Dec 3, 2013

See attached. (I added in some arrows for the first month so it is easier to follow the logic)

I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.

Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.

Essentially, I need the formula to return the correct amount that is being paid, even across different years.

I don't know if my formula can be modified or a new formula would be more efficient.

(This post originated from Commercial Services, which I just requested be removed from that forum)

View 3 Replies
View Related
Mar 27, 2014

I will set a base example first:

9 Nov 2012 Apple 5

12 Dec 2012 Apple 3

14 January 2013 Banana 8

17 January 2013 Apple 6

20 January 2014 Apple 3

I would like a sumifs formula that will only add the results for Apple - as follows:

Nov 2012 5

Dec 2012 3

Jan 2013 6 (i.e. does not include Banana - and also does not include Jan 14 Apple)

Although I have experience with sumifs I have not been able to use the correct syntax to identify the month AND year.

Ultimately I want to look through many hundreds of dates, and add anything that fits two criteria - 1) a 'name' criteria, and 2) it falls within a certainly month and year.

View 6 Replies
View Related
Dec 1, 2013

ith the example below, i am having a problem with the calculation of Total Month. If i put Dec 31 2012 in A2 the total month is 10 BUT if I change the A2 to Dec 30, 2012 the total month is 11. What i am trying to do is to calculate the number of months only disregarding the years. example August 15, 2010 and January 16, 2013 i need to have total months of 5 months only.

A

B

C

1

First Month

2nd Month

Total Month

2

12/31/2012

11/30/2013

=DATEDIF(A2,B2,"ym")

3

View 9 Replies
View Related
Oct 23, 2009

I have a start date dd/mm/yyyy, and require a formula that everytime a spreadsheet

is opened, based on the current date, to calculate how many years, months and days

have elasped yy mm dd since the start date...

My date formula knowledge is a little limited.

View 9 Replies
View Related
Apr 1, 2014

I have come accross this calendar online and it suits my requirements, however, the month s currently static and the year is dynamically controlled by a spin button.

The formulas in the relevent days are as follows

Day 1 =IF(AND(YEAR(JanOffset+1)=calendarYear,MONTH(JanOffset+1)=1),JanOffset+1,"")

Day 2 =IF(AND(YEAR(JanOffset+2)=calendarYear,MONTH(JanOffset+2)=1),JanOffset+2,"")

and so on, I need the formula and the spin button the also change the month as well as the year.

Please see attached file for details.

View 7 Replies
View Related
Jan 22, 2009

I would like to calculate the number of years and months that have passed since a certain date. Would like it in a number format so I can pickout those who have gone reached 5 year increments during each month.

Such as someone reaching 40 injury free years in June of this year I can let them know.

View 9 Replies
View Related
Mar 13, 2012

Cell A1 contains 02/29/2012

Cell B1 contains 12/01/2006

I am looking for a formula that will return the number of months (periods) between two dates, ignoring days and years. Using the above dates, which cannot be changed, the result needs to be 63. The formulas I have tried keep returning 64 because my later date is at the end of a period, and my earlier date is at the beginning.

View 7 Replies
View Related
Apr 26, 2007

I have some cells which must be in the format 15/06/2007 15:25

I then need to add either days, months or years onto it.

Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00

View 9 Replies
View Related
Oct 16, 2008

I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.

What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!

View 2 Replies
View Related
Oct 6, 2011

I have 3 columns (B,D,F) that have 15 minute data intervals and I need to calculate the hourly averages for each column but here is the catch, I need to do this from July-1-2011 00:00:00 to Sept-31-2011, 24:00:00 (that's 26,217 data points and 6,554 =average() calculations......

IS there syntax I could use that would calculate the average every 4-cells so I could get the hourly average without having to type =average(XX:XX) 6,554 times?

Example of set-up:

29-Sep-11

23:00:00

0.820412164= AVERAGE(B8735:B8738)

0.762574274 =average(D8735:D8738)

84.98389991 =average(F8735:F8738)

29-Sep-11 23:15:00 0.819168591

0.762781167

[Code]...

View 6 Replies
View Related
Jan 29, 2010

I am wondering if there is a function in excel to calculate the average of multiple percentages. Currently I rely on the following formula to achieve this result and I am curious if there is a quicker way:

Try this formula. .....

Just today I learned there is a function to calculate the compounded return (FVSCHEDULE). It does part of what I am looking to do by taking away the PRODUCT function and the array, but does not really simplify things. Using FVSCHEDULE my function would look like ....

View 9 Replies
View Related
Feb 20, 2008

Situation: I need to calculate a lot of averages of non-connected values. That is, they are separated by several rows (which also contain values). So far, I use the code-sample below (just to illustrate), which is not really elegant. Unfortunately, it seems that this technique comes to its limits when a high number of different non-connected values are combined into one string and get assigned to a formula- range (last line of code), which is where the program stops.

My question is: Is there a nice way to solve this? A way, which is not limited by the amount of data processed? The only way I could think of, would include a UDF, where a sum, sums all values up and then devides by the number of added sums ... but this would be relatively slow, and I have an awful lot of values to be averaged (100 sets * 6 rows * 200 values = 120.000 calculation- steps), as well as STDEV and a Standard Error of Mean (SEM) to calculate. Is there a better alternative?

Const intCells=9 'actually, this is no constant but a variable

Const i=4 'actually, this is no constant, but a nested for- loop-value

Const intLinesInBetween = 12 ' amount of lines, which separate my values

Dim j As Integer 'simple counter, runs through all cells

Dim rngUpperRow As Range 'Range of the upper row = normalized time vector

Dim rngCurrentRowAvg As Range 'current row into which avg formulas are inserted

Dim intCells As Integer ' amount of experiments in range............

View 4 Replies
View Related
Dec 31, 2008

On another thread I found a solution to creating a weighted average given a pair of columns of numbers but I have column B consisting of values given as High, Medium and Low and the weighted column C of 1, 2 or 3.

How can I use the formula below to accomplish this, or is this maybe not the right way to created a weighted average?

=SUMPRODUCT(B2:B11,C2:C11)

View 7 Replies
View Related
Aug 21, 2009

Code:.....

The names are not static and can be moved, meaning that John can be 1, 2, 3, or 4 (all depending on how the name is chosen). I want to find the average on where John ranks. In this instance, he would be 2 [(1+3+2)/3].

My question is - How can I create a formula that will look for where John appears in the column (B1) and call the previous cell (A1)? This formula needs to be repeated on each column, added and averaged.

View 5 Replies
View Related
Dec 22, 2011

I have a first column, which contains random integers typically with gaps of between 10 to 50 blank cells. The data may have up to 5,000 rows in total.

In the next column along I would like a calculation which averages the integer in the first column out across all rows until another integer is reached in the first column.

E.g. In the first column, the first number is 60, then 9 blank cells till the next entry. In the second column the first 10 cells each contain the number 6 ( = 60/10).

View 1 Replies
View Related