# Finding Average Of Groups Given Multiple Criteria

Jul 26, 2012

I am trying to average PT ScoreS from multiple groups based off of different criteria. Please see below tables:

KEY:
MS = Class CO = Company PLT = Platoon SQD = Squad
A/1 = Alpha Company, 1st Platoon (Example of Company and Platoon PT Score Average)
A/1/2 = Alpha Comapny, 1st Platoon, 1st Squad (Example of Company, Platoon and Squad PT Score Average)
F14 is the average of all PT Scores

A
B
C
D
E
F

1
NAME
MS
CO
PLT
SQD
PT SCORE

[Code] ....

I need to find the equations of different groups of PT Scores. I want the averages/equations to go below the different groups.

A
B
C
D
E
F
G
H
I
J
K

1
MS 1
MS 2
A
A/1
A/2
A/1/1
A/1/2
A/1/3
A/2/1
A/2/2
A/2/3

[Code] .......

## Finding Average With Multiple Criteria?

Feb 27, 2013

I have to find the average of a set of data with a lot of different criteria. I think an array formula would work wonderfully but I just cannot seem to get it.

My example: I have a large set of data that I must get multiple averages from that fall within differing sets of criteria. For my example we'll just say that I have to find the average of the number column that occurs on or after Jan-12 (January 2012) and is type "p1" or one that has no date and is type "p1" and to not average numbers that are equal to 0. So logically it would look like this: If date

## 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)

## Finding Average Based On Certain Criteria Of Another Column

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] .........

## Calculating Multiple Criteria - Specific Groups

Sep 13, 2013

I have data in Columns A to P

Column B contains specific groups.

I need to show the following:

For each group what is the count in column P that is

## Finding Average Of Multiple Ratings?

Jul 30, 2014

I have a number of rankings, all from different spreadsheets that I wish to combine and average out.

Thus far I have pulled all the relevant worksheets into 1 spreadsheet. The issue I'm having is that each worksheet contains slightly different names.

So as an example, Sheet1 may have 4 columns: Bob Hope | 4 | SE | PM |

Sheet 2 may have 2 columns: | Bob Hope, PM, SE | 8 |

Sheet 3 may have 3 columns: | Bob Hope, SE | PM | 9 |

What I need to do is have a master sheet which just pulls through the name and then the average of the scores, i.e.: | Bob Hope | 7 |

I have approximately 400 people that I need to do this with.

## Finding The Average Across Multiple Sheets

Oct 18, 2005

I'm trying to find the average for a person with scores in column 4 on 3 separate sheets. The following function returns #VALUE.. Because the person may be in a different row on each sheet, I'm thinking I need to use the VLOOKUP function.... Also, as an aside, the person may have a zero on one sheet that would affect the average score...

=AVERAGE(VLOOKUP(A4,Week03:Week01!A\$4:G\$30,4,0))

## Excel 2003 :: Finding Average With Multiple Spreadsheets?

Dec 24, 2012

I am currently using Excel 2003. I have a worksheet with two tabs.

First tab has a list of bank Names. Second list has Bank Names and balances.

I need to find out the the average from a Bank in the first tab, to the same bank on the second tab that reflects the balances.

## Finding Multiple Names Within A Range And Calculating Its Total Corresponding Average

Apr 16, 2007

In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum�s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.

## 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?

## Finding Top 2 Values Per Group Based On Multiple Criteria

May 21, 2007

I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.

As an Example, here's what I'm trying to do:

Make Model Rating
Ford Bronco 64
Chevy Corvette 94
Dodge Intrepid 83
Chevy Chevette 34
Dodge Viper 72
Ford Escape 21
Ford Expidition 53
Chevy Impala 67
Ford Fairmont 11
Dodge Dart 33

## Finding Data In Column That Meets Multiple Criteria

Jun 16, 2014

Any quick way to extract data from a table. I need to extract a value from a column that meets criteria from two different columns. I thought I could get this to work with vlookup, but have had no success. Sample data below in table 1 and I would like to get my data into table 2.

elevation
type
percent
weight

5000

5000

5000

5020

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

## Array Formula For Multiple Criteria Average

Jul 9, 2014

I need an array formula that can take average based on multiple criteria present row wise and column wise.

Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.

Average.xlsx‎

## Multiple Criteria Text Weighted Average

Dec 28, 2007

It is a database in which each row represents a unique individual.

Columns A - G contains information which is irrelevant for this question
Columns H - CQ contains answers to the individual's questions (Yes/No or "-" for N/A)
Column CS contains a array formula which determines the % of "Yes" answered only in Question 1 (Columns H to X).
The formula finds all "S1*" (within H1 to CQ1) and if the corresponding "S1" has a "Yes" (within eg. row 2 - H to X) it adds it and divides by the total number of "S1" (17 in this example excluding the "-")

Eg:
In Row 2: S1 = 17, Yes = 7, "-" = 0, % = 7/17 =35.29%
In Row 3: S1 = 17, Yes = 8, "-" = 1, % = 8/16 = 50%

So I have the array formula in column "CS" which does the above.

What I want to achieve:

I want to get the average of all the individual rows in column "CS". This is shown in "CS1 (48.32%". I am looking for a way I can achieve this with just one formula - A formula which first calculates the individual %s and averages the %s within the same formula.

## Finding Unique Groups Of Values In Columns?

Dec 17, 2013

I have a file with immunophenotypic markers and their relationship to certain blood cells.

What I need to do is search the list and pull out the groups of defining markers for each blood cell. Some of the cells have many markers, some only have one but I need to find which combinations of markers identify each cell if possible.

I have attached the file (I think!)

## Finding Recent And Previous Dates Based On Multiple Criteria?

Apr 29, 2014

i have with noting the most recent date of a test and also the one prior to the most recent dependant on location and test type. I require most recent and previous test info to enable a comparrison to be made. All other dates are not required but need to be kept as historical data and can't be deleted.

Is there anyway i could get column E to auto populate and amend itself as more test dates and locations are added to the list?

## Index & Match Multiple Items/Criteria: Finding The Nth Occurrence

Jun 25, 2008

I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish:

On sheet1 there are three columns, Business, Amount, and Closing Date.

Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option.

On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.

## 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.

## Finding Minimum Value In Column Based On Multiple Text Criteria In Other Columns?

Jan 2, 2013

This formula allows me to find the lowest value in column U where column N contains the text "NO".

{=MIN(IF(\$N\$2:\$N\$10000="NO",\$U\$2:\$U\$10000))}

I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".

## How To Count Groups Of Cells In A Range Using Specific Criteria

Jul 24, 2014

I want to count the number of groups of 3 adjacent cells in a horizontal range (C5:EV5) in which any cell in the group of 3 has a value in it (as opposed to all the cells being blank or containing "0"). For example, if any or all of the cells in C5:E5 have a value in it, it would count as 1, and if any of the cells in the group F5:H5 have a value, it would also count as 1, and so on. Is there a way to use COUNTIF for this?

## Create Multiple Files For Multiple Groups Of Data

May 9, 2008

I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.

## Finding Max And Average

Mar 24, 2007

Sub maxTemperature()
Sheet6.Select

Dim myrange As Range
Range("b1").Select
myrange = ActiveCell. CurrentRegion
cellcount = myrange.Count
imax = ActiveCell.Value

i = 2
Do
Do While i < cellcount
If Cells(i, 2) > imax Then imax = Cells = Cells(i, 2).Value
i = i + 1
Loop
Range("a25").Value = imax
isum = 0
For i = 1 To cellcount

isum = isum + Cells(i, 2)
Next cellcount
ave = isum / cellcount
Range("a26").Value = ave

End Sub

## Finding Average With Few Conditions

Nov 20, 2009

I am using EXCEL 2003. I am to find Average using few conditions. I have explained the requirement in the sample workbook.

## Finding An Average With Sumproduct

Jan 2, 2009

I�m using the following formula:

SUMPRODUCT(--(Issue="Yes"),--(Month=Aug),--(Building=\$A\$9))

To find the number of instances where there was an issue in a certain building during a certain month.

What I can�t figure out is how to find the Average number of such instances for the past 3 months, the last 6 months & the trailing twelve months. If I change the formula to:

SUMPRODUCT(--(Issue="Yes"),--(Month>April),--(Building=\$A\$9))

I get the correct sum for the months of May, June, July & Aug but I need an average number of instances per month for the last three, last 6 and TTM but not to include the current month.

## Finding An Average Couple Numbers

Jul 9, 2008

I am trying to get an average of a couple numbers, but I have to enter both numbers in one cell.

I have to enter the numbers in a cell as a range (ex. "1000-3000"). I need to convey it as a range in the spreadsheet I am doing, but in a separate cell I need the average of the extremes (1000 & 3000). Is there a formula or anything that would let me get the average of those two numbers(2000) directly from that one cell? If needed, I could make the cell "1000,3000" instead. I just don't want to make two separate cells, one saying 1000 and the other saying 3000.

## Finding Average Of A Range With Macro

Jun 1, 2009

I'm working on a project in which i had to calculate the average of particular field and that also with a macro in this application i had done that that's working supperb but i'm coming accross a problem according to which the range which i had to take average dosen't contains all integers

eg if range is A1:A10

then data is like

79
80
98
TBA
98
TBA
TBA
N/A
N/A
N/A

now ave for this range can't be calculated directly as many values are strings
what i need is

using a avg function on this range where TBA(To Be Anounced) is to be treated as 0 and N/A(Not Applicable) as null value

here's da avg dunction which i had used in macro

## Finding Average Age From List Of Dates?

Jul 20, 2013

Finding an average age from a list of dates. This is just a small sample:

7/9/2013
7/10/2013
7/10/2013
7/10/2013
7/11/2013
7/11/2013
7/14/2013
7/14/2013
7/19/2013
7/19/2013

I tried using =AVERAGE(DAY(A1:A10)) CTRL + SHIFT + ENTER, but that just doesn't seem correct.

## Count No Of Unique Months For Finding Average

Mar 11, 2014

I am trying to calculate the average headcount for different categories (over a certain period of time, by product, by job type etc) and I want to do it using a formula instead of a pivot table.

I have this set of data with a detailed headcount for every month. And I can find out the total HC for different categories using sumif/sumifs but if want to find out the average for a certain period, I need to split that total to the number of months where I have a HC.

For example, if the HC per month would be:

May 2013 - 12
June 2013 - 10
July 2013 - none
August - 5

in a 4 months period I would have a total HC of 27 and the average would be 9 (27/3 - July shouldn't be counted). How can I get the average in a single formula?

Month
Type
L/H
Prod type
HC

[Code].....

## 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)))

## Excel 2010 :: Finding Average Value For Positive And Negative Set Of Numbers

Aug 25, 2014

Please find the attached MS Excel 2010 file <average set.xlsx>.

There is set of positive set & negative set of values available in the Column A. The values are plotted against Column A in Column B. Light green are positive sets and light yellow are negative sets.

Now I want to calculate the average for the positive set & negative set of values as shown in light majenta in the cells F4:F9. Also all majenta cells to be plotted by formula.

The Column H and Column I also same as like above , but here negative set of numbers starts first.