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] .........
View 2 Replies
ADVERTISEMENT
Jun 15, 2006
I have a main soure data which consist of row & column information. What i want to do is search the data from the source data into my result data as per the attachment file. Example: I want to information of Jan & banana from the main source file to appear in the XXXX
Result data(criteria base on Month & type)
JanApril
BananaXXXX
Apple
Orange
View 4 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
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
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
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
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
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
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
Mar 7, 2012
I need to be able to find a value in another sheet based on 2 criteria
The criteria is a number from a column on my first sheet where the results will reside, the second criteria is based on a fiscal year (calculated based on the year of the current year).
The formula I use is as follow:
=LOOKUP(BA2,Budgets!B:B,Budgets!I:I)
and this is what I use to get the column:
=+INDEX(Budgets!B11:B200,MATCH(Budgets!B11:B200,Budgets!B11:AB200,0),3) getting #N/A here
and I use this too:
=MATCH(IF(MONTH(TODAY()) < 7,YEAR(TODAY()),YEAR(TODAY())+1),Budgets!E:Q) getting #N/A here too
If i keep it like that no problems but I will need to change the criteria 2 every new fiscal year.....
What I need is to replace the criteria 2 with something that will find the proper column wher the data is the fiscal year is listed on E11:Q11..
I have tried index and match and quite frankly, I never made it work, always getting an error.
View 9 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 2, 2009
What I am attempting to do is find the MIN value in Column C where values in Column A are equal.
The data would look like this
A B C D (D:D is where the "MIN" Formula will be)
Scope1 NameA $100
Scope1 NameB $145
Scope1 NameC $115 $100 (I want the min value to show up here)
- (this would trigger a break between scopes, and provide a conditional format separator)
Scope2 NameE $450
Scope2 NameG $345
Scope2 NameX $415 $345
-
So every time I put a "-" I would like the MIN formula to trigger in (Row#-1,D)
View 5 Replies
View Related
Feb 7, 2009
here is a sample of the data
150
21
352
422
53
69
755
852
1224
13268
119
242
30
444
543
67.................
I know if I use dmax for only where first column equals 13 I get 460 but how do I get the second highest value for only those rows that have 13 in the first column (expect the answer to be 268). Then I want to do the same for 3rd, 4th highest etc.
I know large does it for one column and not only when the first column matches a designated criteria.
View 3 Replies
View Related
May 19, 2007
I need to look at column B find anything that Starts with BBX (ie; BBX124343, BBX1348383 ) and move it at the same location only to column A
View 9 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, 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
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
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
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
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 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
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
Oct 2, 2008
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View 2 Replies
View Related
May 3, 2013
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
View 5 Replies
View Related
Jul 24, 2009
I'm looping through and finding a cell based on Column A, and I .resize(,5).select and from that selection I want to create a range called "LCrng"
View 9 Replies
View Related
Nov 7, 2008
This should be simple to do but I can't figure it out. I have a database that lists operating room numbers in one column and the length of the surgeries performed in those rooms in another column.
I need a formula that will give me the longest OR time for a given room. For example the room numbers are in column A and the OR times are in Column B. I've tried something like
View 6 Replies
View Related
