# Average For Numbers That Fall Within Criteria?

Nov 10, 2011
I need to figure out a way to get an average for the numbers in column A, but ONLY those numbers in column A that have the number "1" in column B. example:

Column A Column B

3 5

6 1

4 1

The average would be "5", because the numbers 6 and 4 have "1" next to them in column B.

View 1 Replies
ADVERTISEMENT
Jan 24, 2013

I have to columns of data

Date and number

4/1/2012861,808

11/1/2011594,930

11/1/20102,740,320

11/1/20121,041,948

10/1/20111,726,000

10/1/20091,628,000

10/1/20082,059,929

10/1/2011 4,002,000

9/1/20062,979,602

9/1/20073,774,000

9/1/2011NA#

I want to take the average of the numbers between if they fall between dates of 2006 and 2007.

I can not do it with averageifs because I have that NA# in the second column. I dont know what to do.

I have something that works but only if it does not contain NA#, below is the example of what works:

=AVERAGEIFS($D$2:$D$131,$B$2:$B$131,""&H2)

View 3 Replies
View Related
Feb 11, 2009

I need a formula to add numbers together that fall under a certain value in a row and a certain column from more than one pivot table.

I have used this

=GETPIVOTDATA("orders",$A$*,"Month","December,","WAIT BAND","2-3")

and this works fine for bringing across the data for that pivot. The problem i have is if i then also want to add numbers from another to another pivot.

=GETPIVOTDATA("orders",$A$*,"Month","December,","WAIT BAND","2-3") + =GETPIVOTDATA("awaiting orders",$A$*,"Month","December,","WAIT BAND","2-3")

View 9 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
Jan 29, 2013

So we had a month long, company paid (woohoo!) "weight watchers" challenge. On 1 worksheet ("Stats"), I have the Name of every Employee (A Column), then their Start Weight (B Column), End Weight (C Column), Department (D Column) and finally Location (E Column).

I know how sensative some people can be about their weight, so I locked the page and created another worksheet named "UI", which will display the "Average End Weight" for each department. So Human Resources would be the department in cell A2 on the UI sheet, with City 1 being the Column Header in cell B1. There are 8 cities (offices) and 23 departments.

So, for cell B2, I want to scan through the "Stats" worksheet and locate all of the cells in the department and location columns that read "Human Resources" and "City 1" respectively. Then grab the "End Weight" for every row that meets these 2 criteria, add them up, and divide by the number of "End Weights" that were grabbed.

My solution would be to make a separate column for each department (and then each city, so essentially 8 columns to represent all the cities for each department), use a nested (maybe 4) IF statements to then list only the end weight if that particular row meets the criteria, then at the bottom of each separate column, add all the shown numbers up and divide, then draw the information from THAT number into the "Average" cell on the UI worksheet. But it isn't very dynamic and if i want to use this sheet next year, there will be more employee info to be added and it would be a mess..further more, it would be...what...23 Departments x 8 Cities x 155 employees = ~28,500 added cells.

View 7 Replies
View Related
Dec 8, 2006

I have a large database that is updated daily. From within the database I need to:

1. Select all records where date field A is 14 days or less than date field B

AND

2. Where a field C matches a text criteria, i.e., =DOGGIE

AND

3. Append KITTY to field D of all records that match criteria 1 and 2

Finally

4. SAVE results as a text file.

View 9 Replies
View Related
Jun 14, 2013

E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).

E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.

I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.

View 14 Replies
View Related
Jan 27, 2012

I have a spreadsheet wtih a number of rows that contain answers to different criertia in each column.

The row may therefore have cells that either have numbers or letters.

Column A will be the title of the row.

I want to write a function to find the average value of the numbers in any row with title 'x'.

So it would look down column A, and look for those called 'x', and then average all the numbers across all those rows.

I have tried to use =averageif, but I think the fact there are letters in the cells being assessed (which I just want to be ignored) creates an error.

View 9 Replies
View Related
Aug 18, 2009

Field 1 input: Beginning Date (e.g 7/17/09)

Field 2 input: End Date (e.g. 7/23/09)

Need: Average costs on and between these given dates

From table with this information:

Date Market two

7/16/2009 80

7/17/2009 80

7/20/2009 82

7/21/2009 84

7/22/2009 82

7/23/2009 82

7/24/2009 82

7/27/2009 82

I need Excel to average Market two 7/17 to 7/23 (80, 82, 84, 82, 82) based on the dates I give it.

If I change the date to 7/20 to 7/24, I then need excel to average (82, 84, 82, 82, 82)

How can I get Excel to average based on different dates? Or basically, how do I get it to pull rows between 2 inputs?

View 4 Replies
View Related
Nov 27, 2005

I m making a worksheet for our shooting results. I need the average of the

top 3 results from collum E6 to W6 and top 3 reults from Y6 to AJ6. Can

View 14 Replies
View Related
Apr 30, 2014

I worked on a countifs function, but i needed a average of column N.

the countif formula is

A2 = current month

=COUNTIFS(

'Location'!J3:J999,">="&$A2,

'Location!J3:J999,"<="&EOMONTH($A2,0),

'Location'!M3:M999,"Name")

With the exceptions from above i need to get a average from column N.

View 4 Replies
View Related
Mar 10, 2009

I have 2 rows of data

1. title

2. value

eg

Row 1: Title1, Title2, Title1, Title4, Title2, Title1

Row 2: 2 , 3 , 0 , 1 , 2 , 6

In the above I am looking for the average of Title1, where the value is greater than 0. There are 3 occurances of Title1, but only 2 have values. The answer i am after is Sum is 8, average is 4 (ie 8/2 and not 8/3)

View 3 Replies
View Related
Dec 5, 2012

Ceate a formula for weighted averages according to criteria?

I want to know what was the average price I bought a product at on today's date. The average price needs to be weighted against the quantity of product sold.

View 3 Replies
View Related
Oct 10, 2006

I have information I'm trying to evaluate. Average Close time. but I need to evaluate it for each month. Forinstance if my average close time was B. and the close date was c. I would need the average(b:b) but only from fields that had a close date of July in column C. is this possible? I hate to have to break up the data set 12 times. (i'm reporting individually for each month of the year so we can see trends.)

View 12 Replies
View Related
Nov 5, 2009

I would like is the average of numbers that are on certain sheets with a certain criteria. I have an excel example that might explain more.

View 11 Replies
View Related
Aug 4, 2009

In column A is the staff name, column B is the start date, column C is the end date and column D is the difference.

I need to find the average number days for task completed for each staff for the week. I need a formula that will look for a particular staff name in column A ( which appears many time randomly in column A for each day of the week) and calculate the average days for the total task completed for the week.

View 23 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
Jan 5, 2007

I have trouble finding the right formula for a weighted average price. The formula which i used in cell B2 of the attached file doesn't exclude irrelevant numbers. In the formula "= sumproduct(a5:a21;b5:b21)/sum(b5:b21)", i like to exclude cells B8, B9, B15, B19, B20 because the list prices are missing. How can i exclude these irrelevancies in the formula above?

View 3 Replies
View Related
Sep 19, 2007

I have a large set of data. I need to be able to search this data routinely using an origin and destination zip code and sum the search results. Is this possible in Excel?

View 5 Replies
View Related
Oct 8, 2007

I am trying to create a macro using arrays to calculate average sales from a list of sale amounts that originate from different cities. Each city has its own city code and I want to display the average amount of sales for each city. The attached file is the template that ive created to do this. I am having trouble getting the arrays and loops to work. If anyone can help me out I would greatly appreciate it.CitySales2.xls

View 3 Replies
View Related
Nov 27, 2008

I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.

The sheet looks something like this, numbers are all in one row:

7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32

I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.

View 7 Replies
View Related
Apr 23, 2009

=AVERAGE(INDEX(E5:IV5,LARGE(IF(E5:IV5<>"",COLUMN(E5:IV5)-COLUMN(E5)+1),5)):INDEX(E5:IV5,MATCH(9.99999999999999E+307,E5:IV5)))

I have this formula where it averages the last five numbers in a collumn, but I want to average the last 5 numbers less the max and min number.

View 14 Replies
View Related
Oct 21, 2005

I have cells O1 to O300 numbers in random sequences of no more than 10

I need to average the 5 highest numbers in each sequence eg:

O P

1 2 16 (result)

2 20

3 4

4 6

5 14

6 12

7 8

8 10

9 16

10 18

11 blank cell

12 15 9 (result)

13 3

14 6

15 12

16 9

17 blank cell

18 blank cell

19 4 23 (result)

20 16

21 20

22 40

23 8

24 11

25 28

26 blank cell

down to 300 rows, the sequence of numbers can be from 1 to 10 but never

more than 10

I got a function : =IF(COUNT(O1:O10>=10,AVERAGE(LARGE(O1:O10,{1,2,3,4,5})),"

") but that works only for 10 numbers and not for less

View 11 Replies
View Related
Nov 1, 2008

Hello, I have a column with 100 numbers in it. I want to be able to choose a number N and then calculate the average, minimum and maximum of the first N numbers in the list.

I have played around with countif and sumproduct but can't figure it. I don't know how index works, and wonder if that is the key.

View 13 Replies
View Related
Oct 6, 2012

My worksheet contains values in D2:AA150 Some cells are blank Is the a formula that will average the lowest 10 values ineach row?

View 6 Replies
View Related
Jul 27, 2007

I want to pick the top five numbers out of a random list of numbers and get its average. What is the best way to go about doing this?

View 2 Replies
View Related
Jan 23, 2014

I have 4 columns of data, and need to average values that correspond to certain criteria from the other 3 columns. Attached is an example of my data and the desired output. The averages need to come from non-blank cells from Inputs 1 and 2 that match the criteria from the Name column.

View 5 Replies
View Related
Aug 25, 2009

I have a data set that looks something like the following:

TypeAmountCost

A100$50

A200$40

A300$35

B250$40

B275$55

B500$60

A700$20

B350$25

B450$35

C400$80

C400$60

I am having trouble creating a formula that would calculate the weighted average cost depending on the type (i.e. weighted average cost of $28.84 for A, weighted avg cost of $70 for C).

View 2 Replies
View Related
Jul 3, 2013

I'm trying to sort column G for any "Approved" or "Denied" outcomes then find the time difference between columns E and F then divide that difference by the number of approved or denied in column G. I've tried a number of combinations of SUMIFS, IFS, COUNTIFS, and Ave formulas but can't come up with one that works. Here's an example of what I'm trying to do:

Sum E-F if G= "Approved" or "Denied" / the number of "Approved" or "Denied" outc

View 12 Replies
View Related