Find Average Of The Specific Month

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


ADVERTISEMENT

Find Average With Very Specific Condition

Mar 14, 2014

Let's say I had the following data ...

47
50

50
50

44
50

46
50

Basically, I have 4 numbers, out of 50, as seen above...I want it so the last number (46 in this case) is special; what I mean by that is I want the following conditions to be met.

--If the special number is the lowest, just take the average of the four numbers, and divide it by 50
--If there is a (are) number(s) lower than the special number, then I want it to locate the smallest
---The smallest number should then be "replaced" by the special number (for calculations purposes only, not in the actual table)

So, in this case, since the special number (46) is not the lowest, excel would locate the lowest number (44), and calculate the average of 47, 50, 46, and 46 (the 44 was replaced by 46).

View 4 Replies View Related

Function To Find Days In Specific Month

Jan 30, 2010

Can Anybody help me with a function that will arange all days from January acorrdingly with the year 2010. I am attacing a sample file to make it clear. I need the function to Check which month is in Cell H1 and arrange the days accordingly to the month and the year.

In my example the code must change the Cell values Like this: ...

View 9 Replies View Related

Find Specific Value In Array Which Appears Multiple Times And Average All Relative Cells

Jan 24, 2014

I'd like to search for a specific value like (1) in an array of cells like (A:A). The result needs to be the average number of a different column (B:B) in the same row as the searched value (1). And the resulting number should be rounded up to the nearest 10.

Example below: (Find value = 1 in A:A) ... 1 is found 4 times.

A | B
1 | 295
3 | 123
1 | 400
5 | 425
1 | 354
1 | 400

In the example above there are 4 (1's) found. By adding all the values in column (B) of the same row, we get 295+400+354+400 = 1449, dividing that in 4, the average is 362.25, rounding that up to the nearest 10... THE RESULT I'M LOOKING FOR IS: 370

View 6 Replies View Related

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 View Related

Macro Loop Find Specific Times, Extract Date, Average Values Of Times

Feb 23, 2010

In my workbook I have about 20 sheets (less in the attached sample), and on most sheets Id like to query data for each day and find an average value based on the time and then copy that result into another sheet. Im not sure if Ill need 2 macros for this or if one can be used.

Heres a quick description:

Assuming that I am currently in the sheet I want to run the macro, then I will either input 1:45 or 0:45 into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. Im flexible

1:45:

If I input 1:45, then Id like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....

View 11 Replies View Related

Automatically Get All The Dates Of A Month Entering Specific Year And Month

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

SUM By Both A Specific Month, And A Specific Year From A Single Date Field

Nov 30, 2007

So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}

But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}

SUM by both a specific month, and a specific year from a single date field?

View 3 Replies View Related

Average By Month

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

Average By Month ...

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

Take Average Between First Tuesday Of Every Month?

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

Average By Weekday Of Month

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

Average Times By Month

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

Getting Average Revenue Per Client By Month?

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

Average If Data Is In Same Month Of Same Year?

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

Average Values If Month Equals?

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

Daily Average Based On Day Of Month?

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

Formula For Rolling 12 Month Average?

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

Average Daily Data By Month

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

Macro To Average Data By Day In Month

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

Average Based On Criteria :: Within Each Month

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

How To Create Month To Date Average

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

Cumulative Sum For Weekly Average Hours Over A Month

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

SUM / AVERAGE / OFFSET To Specified Columns Containing Month Range

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

Finding Average Based On Month And Year

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

Average Based On Time & Month Cells

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

SUMIF Month & Year: Find Total Cost By Month Only For Year 2009

Dec 17, 2009

In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?

View 2 Replies View Related

Formula To Return Average Days Between Two Dates In A Given Month?

Jan 13, 2014

I have a workbook with two sheets. The first has a list of job positions open, columns designated to stages in the employment process and in these columns, my staff enter the dates that they completed a particular stage.

"Position | Date Opened | Stage 1 | Stage 2 | Stage 3 | Stage 4"
Pos 1 | 01/01/2014 |02/01/14|03/01/14|07/01/14|09/01/14

This has a great number of entries and they are increasing and decreasing every day depening on the amount of jobs available.

On the second sheet, I am trying to set up a table which shows the average working days it is taking to complete each stage, divided into the months in which the job position was opened (i.e. for positions opened in january, the average completion working days for stage 1 was X amount of days etc...)

I have tried using =IF(AND(Logical, Logical),TRUE,FALSE) but this doesn't return any info as the logicals will always be false. I tried the OR function, but that requires only one of the criteria to be true to return a value. What I need is for the formula to return a number of days between two dates, ONLY if the opened date falls in one month.

View 1 Replies View Related

How Do I Create A Running Average That Will Only Calculate The Averages In % Each Month

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

Average With Conditional Month, Year And Date Range

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







Copyrights 2005-15 www.BigResource.com, All rights reserved