# 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
ADVERTISEMENT
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
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
Aug 26, 2006

I have two columns. Column A has lots of repeated values, and column B is a list of numbers. For example:

A 1

A 3

A 4

A 2

A 6

B 1

B 1

B 2

B 1

C 3

C 3

C 2

C 4

Is there any way to output the following:

A 3.2

B 1.25

C 3

Where the second column are the averages. Any Excel function or VBA method will be fine.

View 9 Replies
View Related
Oct 18, 2006

i have a excel spreadsheet that contains point of person. Every week a person can or can not compete. That means that there is some days that is blank wouthout points. I want to get the latest 5 points of a person and then out of that 5 i want to get the top 3 point.

View 6 Replies
View Related
Sep 24, 2007

I maintain a table with projects and their respective costs / revenues.

I have a formula that automatically sets the forecast and Year-to- Date periods based on the month and date.

I need to automate the year-to-date sums such that, when the date changes and a new month acquires the YTD status, that the monthly costs/revenue of the projects are updated e.g sum of Jan-Sept in the YTD column(for this month).

A sample workbook is attached.

View 5 Replies
View Related
Feb 27, 2012

I have a series of numbers in the column AB56 going downwards and in AC56 going downwards their respective counts in a data-set.

What I would like to do is find the average of the min and max, the min and max for the average calculation are both determined by looking at the count column, isolating the max, isolating the min, provided they both have a count greater than zero then averaging both results.

eg.

0.1562818960

0.1558039690

0.1553260431

0.1548481172

0.15437019 2

0.1538922643

0.1534143384

0.1529364124

[code].....

In this calculation the MAX is 0.155326043 because it has a count greater than zero, the MIN would be 0.138120697 as it has a count greater than zero, the average of both would simply be 0.1467. i.e. Average(0.155326043,0.138120697)

View 2 Replies
View Related
Feb 29, 2012

I am trying to work out average for a particular area based on a criteria.

eg:

E F

Area time

A 01:00

B 02:15

A 01:15

C 01:16

B 01:30

C 03:15

In Cell

E100 - 103 I'm trying to work up the average of each area and using the following formula.

=AVERAGE(IF($E$4:$E$61="A",F4:F61,""))

I keep getting an the following error #value!.

i changed the average to median and still get the error.

View 2 Replies
View Related
Jul 29, 2006

I'm trying to find the best Macro or Formula to get this done.

Sheet 1 has a list of salesperson in column "A" and the total numbers they made on Monday (in the same row, column "E"), Tuesday (In the same row, column "F"), Wednesday (In the same row, column "G"), Thurday (In the same row, column "H"), and Friday (In the same row, column "I"). I also have a sheet for each individual salesperson. I need to find a Macro or a Formula that I can use to give me the average of the numbers if "A5" = the salesperson in Sheet 3 then avarage the numbers from "E5" to "I5" and I want the results to show up in cell "D35".

View 9 Replies
View Related
Nov 30, 2007

I am trying to get the average of two columns

column A and Column B

Column A has text (survey type) i.e. Move In, Mid Year, Year End

Column B has the scores given on that survey type i.e. 70

I am trying to get the average of of all the Move In cells from column A

for each survey type so that I get average of all the scores in column B.

View 3 Replies
View Related
Sep 4, 2007

I am trying to get the sum of some cells (integer varies in column G), but comparing one column content (exact) and dates in a different column.

I tried the following:

=SUM((Tony!G30:G44)*(Tony!D30:D44="Crazy")*(MONTH(Tony!E30:E44)=7))

Column E would contain a date, such as 01-07-07 or 1st July 2007.

In the D Column, keywords such as "Crazy" are concise and standard. However regarding dates, am I better off finding a formula that looks for cell content (Contains "july", as opposed to ="July"), or using a month function (but getting it to work)? How can I do this?

View 3 Replies
View Related
Apr 24, 2014

I have 2 columns of data in Excel which I have brought a small section of it below. As can be seen the values in the left column have a large spike (difference of more than 10) at certain points (in this case at 34). I wan to find the average of the numbers in the right column but only till the point where the large spike happens (in this case the average of the first 5 numbers). I've tried AVERAGEIF but it's not what I want. I want this to repeat for the entire column and give me the averages of each of these groups.

10

32.4

11

38.6

12.5

23

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

View 2 Replies
View Related
Mar 31, 2014

I am looking sum formula to display my data with 3 criteria (display by this month, until this month & until last month) based on header column/correspondents, then in cell L3 as selected month display..

For further information, check workbook attached...

SUM 3 MODELS MONTH.xlsx

View 7 Replies
View Related
Apr 5, 2014

show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..

see my attached workbook..

View 9 Replies
View Related
Oct 16, 2011

Objective: to create an average variance among exact match RevCode

Example: all RevCodes with "666" should be used to make an average variance statistic

Situation: All RevCode's are not in similar group counts and this creates incorrect averages with a standard formula.

Set of Data

RevCode Observed Expected Variance RevCodeMean

666 200 220 -9.091%

666 205 220 -6.818%

666 207 220 -5.909%

777 500 355 40.845%

767 505 505 0.000%

668 100 105 -4.762%

668 105 105 0.000%

Formula that generates average variance between exact match revenue codes:

=(SUM(B2:B4)-SUM(C2:C4))/SUM(C2:C4)

The solutions can be in VBA or within the spreadsheet either way.

View 1 Replies
View Related
Feb 11, 2014

Here is my formula that does not return accurate results:

=IFERROR(SUM(SUMIFS(Tenure!$H:$H,Tenure!$E:$E,{"=1100","=1090"},Tenure!$C:$C,{"=12","=14","=13"},Tenure!$G:$G,"=1/1/2013",""})/SUM(COUNTIFS(Tenure!$E:$E,{"=1100","=1090"},Tenure!$C:$C,{"=12","=14","=13"},Tenure!$G:$G,"=1/1/2013",""}))),0)

I need to average column H from the sheet Tenure IF column E is 1100 or 1090 (formatted as text) and IF column C is equal to 12, 13, or 14 and IF column G is less than or equal to a date and column I if greater than or equal to a date OR if this column is blank.

I tried AVERAGEIFS, the above route and many others. For whatever reason, I have been unsuccessful, but close many times.

View 3 Replies
View Related
Aug 18, 2006

I need to monitor the average daily usage of a liquid tank for a customer. We fill this tank every few weeks. The formula I am looking for would ignore the fills and just count the daily usage.

View 9 Replies
View Related
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
Feb 24, 2014

I am looking for a formula that will select a date in the month based on certain criteria. Found the choose function but not sure if I can really get that to work. I basically have a list of clients, with zip codes, restricted days of the week - and would like the system to group them by zip code and select the best day of the month to schedule an inspection but not pick he restricted day. The goal being have zip codes scheduled together - but on a day other than garbage day. Is this even doable?

View 2 Replies
View Related
Jan 13, 2008

I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...

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