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

View 2 Replies
ADVERTISEMENT
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

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

View 5 Replies
View Related
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.

View 3 Replies
View Related
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))

View 14 Replies
View Related
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.

View 6 Replies
View Related
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.

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

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

grade

percent

weight

5000

5000

5000

5020

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

View 9 Replies
View Related
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â€Ž

View 13 Replies
View Related
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.

View 7 Replies
View Related
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!)

View 14 Replies
View Related
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?

View 1 Replies
View Related
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.

View 2 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 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".

View 11 Replies
View Related
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?

View 5 Replies
View Related
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.

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

View 8 Replies
View Related
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.

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

View 9 Replies
View Related
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.

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

View 6 Replies
View Related
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.

View 9 Replies
View Related
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].....

View 9 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
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.

View 4 Replies
View Related