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

Average Of Numbers Between If They Fall Between Dates

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

Add Numbers Together That Fall Under A Certain Value In A Row

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

Average Of Numbers From List Based On Multiple IF-OR Criteria

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

Generating Average From Group Of Numbers In List That Match Two Criteria?

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

REcord That Fall Within Date Range, AND Meet Text Criteria

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

Average Column Of Cells But Ignore Errors And Return Average Of Numbers That Are There

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

Average Numbers In Table Containing Numbers And Letters?

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

Average Row Of Numbers Using Different Row #'s

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

Average Of Top 3 Numbers.

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

Average With Criteria From Other Rows?

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

Finding Average With 2 Criteria.

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

Weighted Average With Criteria?

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

Average With Criteria From Another Column

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

Average Over More Sheets With Criteria

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

Find Average With Criteria

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

Average Based On Criteria

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

Average Based On Many Criteria

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

Criteria Weighted Average

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

Multiple Criteria Average

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

Average By Condition/Criteria

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

Find Average Of Last 4 Non Zero Numbers

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

Average Last Five Numbers Minus The Low And Hi

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

Average Numbers In Sequence

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

Average Of The First N Numbers In A List

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

Average Lowest 10 Numbers In Row?

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

Average Of X Highest Numbers

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

Average Values That Correspond To Certain Criteria?

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

Calculating Weighted Average With Criteria

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

If Criteria Met Then Determine The Average Time

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







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