Average Largest Several Values In A List
Dec 29, 2009
I have lists of numbers in a columns and would like to elliminate high outlier values in each column. Each list is different so there's no way to just set a max value and say that any value above it is an outlier. What I'd like to do is to average the top 5 or so max values for example, and then say that any vale above this average is an outlier. So a formula such as =average (max1,max2,max3,max4,max5) would work if it's possible to derive max1, max2 and so on. Or maybe there's a different way of accomplishing this?
View 3 Replies
ADVERTISEMENT
Jun 22, 2014
I tried a few searches for what I need but I think my wording might be off or there isn't anything on it yet. So the formula I'm looking for is: I have a single colum of say 700 rows with random values in each. I would like to average rows 1 to 15 then 2 to 16 then 3 to 17.... all the way through to 686 to 700. Now out of all that I need only the highest 15 period average value returned. Seemed simple until I tried to do it. Whats that saying, the idea is 1% of the invention..
View 4 Replies
View Related
Oct 10, 2009
I am trying to subtract the highest number from a row of numbers then find the average for the remaining numbers in Excel 2003, as: =SUM(B2:E2)-MAX(B2:E2)AVERAGE(B2:E2).
View 5 Replies
View Related
Jun 20, 2014
I need to find the average of a list of text cells. My list is in column A, and in column B I've assigned each of the text cells with a numeric value. I'm then doing the average on Column B which will return a numeric value which will represent one of the text values in column A.
My average formula then looks like this:
=VLOOKUP(ROUND(AVERAGE(B2:B48),1),$G$2:$H$25,2,FALSE)
Can do this without using the helper column B and do an average formula using a VLOOKUP?
View 3 Replies
View Related
Jan 25, 2007
I have a simple one today but on a time crunch so I don't have time to go through the book.
I am trying to hlookup a list of values from a table and average the results. The values are setup horizontally.
View 9 Replies
View Related
Aug 13, 2007
I have a list of values on my spreadsheet in a similiar order to this:
17.91
16.59
15.00
14.86
13.56
12.17
11.01
0.0
I use the average function to work out an average for all the values, but since the value of 0.0 is showing i should not include this as an average. the range of values above (8) will always be the same for the month - so i need to find a way of omitting the 0 value without keep changing the formula
View 9 Replies
View Related
Feb 24, 2014
Any way to determine which is the best selling single of that week and print a 1 in the row associated with that CD.
View 1 Replies
View Related
Jul 31, 2013
How can I get the largest number in this list by using a function?
View 4 Replies
View Related
Dec 30, 2006
I need a formula that would return the sum of the largest 3 numerical values out of a range of values, i.e. ignoring the lowest values in the range.
View 5 Replies
View Related
Jul 11, 2007
I have a row with 30 cells and each cell has a different value. i want to count the highest 15 cells of those 30, so the sum of the total only counts the largest 15 digits.
View 2 Replies
View Related
Sep 22, 2009
I have created a monitoring spreadsheet for a public School system. It pulls data from 5 seperate excell files. however when a lookup value that is further down the list on the largest table it returns no data. other cells can lookup that same number and return correct information but from a different table.
The table that has issues is A1 - Y12430.. if the lookup value is low in the first few thosand rows it works.. but if thye value is in say row 9,000 it can not locate it..
View 9 Replies
View Related
Sep 26, 2008
is there a simple way to sum the ten largest (or smallest) values? I just can't think of any formula to do the job, and a filter isn't an appropriate permanent solution.
View 9 Replies
View Related
Jan 7, 2008
How would I find the four rows with the largest value in a defined column and then export them to a separate worksheet.
View 2 Replies
View Related
Jul 20, 2006
I have a small problem with a list of event codes in the form XX###, (Xs are letters, # numbers), I need to be able to find the largest numerical component in the list, and am having problems finiding a neat way of doing it.
I could do it by using the RIGHT command to get the numerical bit in a seperate column, then convert it to number, then use LARGEST, but this seems somewhat cumbersome!
View 4 Replies
View Related
Aug 27, 2008
I am looking to create a formula to add together the two highest values of a list of 10 numbers.
I know you can use something like large(a1,a10,1)+large(a1,a10,2) if numbers are in an array but my problem is numbers arent
they are in random positions along a row....
View 12 Replies
View Related
Oct 14, 2009
I have 3 columns. In 1st there is a name of the product.In 2nd there are different types of product (6 in total) and in the 3rd column a figure attributed to the correspondend type of product. I need to create a summary for every type. For this I want to show the 5 largest figures(based on the name of product) for every type.
Like this:
AAA GK 40
BBB CD 50
CCC FD 55
DDD GK 60
EEE FD 70
.....
View 9 Replies
View Related
Jul 1, 2008
I have a work document with multiple rows of information. In each row I need to locate the maximum and minimum values. (The rows aren't long - there are about 10 values in each). I then would like to turn the font of the lowest value in each row red - and the font of the highest value in each row blue. I would also like to be able to skip blank or unimportant rows.
View 2 Replies
View Related
May 12, 2012
I have a table showing interest levels in training courses from a group of schools, eg:
English Maths Science
School1 3 4 2
School2 7 1 0
School3 3 2 5
I want to identify the column heading for the first, second and third most popular courses. ie for School1 the most popular course is Maths, second most popular is English and so on.
I have tried using the OFFSET function, which worked if I provided the cell location of the required value. I then looked at the ADDRESS function to provide the cell location: eg For School2 find the 2nd most popular course:
=ADDRESS(ROW(A3),COLUMN(data?)+MATCH(LARGE(B3:B5,2),B3:B5,0)-1)
But I have got stuck with what I should enter for COLUMN(data?) as I do not know in which column the second largest value is.
I am sure Excel has the required functionality.
View 12 Replies
View Related
Feb 16, 2010
I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
View 6 Replies
View Related
May 25, 2013
I have a large list of students (Col a,b) who do several sports (up to 10) each, which are arranged as sport name (Col c) followed by score (col d), repeated on pairs of columns for the other 9 sports.
I need a macro to find the 4 highest scoring sports for each student in turn and to highlight the score and sport cells. I have tried to find permutations using the LARGE function but I cannot see how I can do this.
View 5 Replies
View Related
Oct 1, 2013
There should be a formula to extract the largest values and its corresponding values. For example, if column
Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100
The desired outcome
Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100
The second goal is extracting the top 3 largest values using a formula.
Sample file is attached for your convenience. Desired outcome is in GREEN.
View 6 Replies
View Related
Nov 10, 2009
INDEX($D$19:$D$22,MATCH((LARGE($E$19:$E$22,1)),$E$19:$E$22,0))
I use this formula to give me a "name" associated with the largest number in a list. But I can't get it to work when I use it on a win/loss number such as
"7-1". It won't read it.
View 5 Replies
View Related
Oct 17, 2008
I'm wanting a function to display the largest number starting with 7,8 and 9.
I have a list of 4 digit numbers, beginning 7,8, or 9, and want 3 different cells.
I need:
One displaying the largest number in the group starting with 7.
One displaying the largest number in the group starting with 8.
And one displaying the largest number in the group starting with 9.
Could I make use of an IF function and the MAX function?
Something like:
M20:
=IF("1st digit = 7", "display the largest of the list")
M21:
=IF("1st digit = 8", "display the largest of the list")
M22:
=IF("1st digit = 9", "display the largest of the list")
I've used =MAX(H5:H75), but this always displays the top "9" number.
I want something so I can show the top 8xxx number, and the top 7xxx number too.
View 9 Replies
View Related
Dec 23, 2011
Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.
=AVERAGEIF($E$4:$E$34,">0")
=AVERAGEIF(E4:E34,">0")
View 9 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
Jan 28, 2012
How do you calculate the average of the last 20 values in a dynamic list? The list is in column A and updates every day by adding a new value in column A.
View 3 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
Jun 25, 2008
I'm currently stuck with the following problem. I have a fairly large database with 10,000+ companies (in the rows) over 10 years (in the columns). I want to aggregate industry averages of a financial item in so-called SIC codes (there are approx. 1,000 different 4-digit SIC codes).
I've been using the daverage function, which works for one criterium (e.g. SIC = 0100) but not for the entire range of criteria. Does this mean I'll have to manually adjust every daverage function for every single SIC code? Or can this process be automated in some way?
Attached is a simplified version of my database file.
View 9 Replies
View Related
May 29, 2014
Data example is displayed below. I need to know how to find the average, not date average, but count. What is the average per day? Example on 2/9/2014 there are 12 entries but on 2/10/2014 there is only 1. Example: For all the lines of data that I have, what is average per day?
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
2/9/2014
[code].....
View 4 Replies
View Related
Jul 14, 2009
Im creating a spreadsheet at work, which has a drop down box with the options of open and closed in it.
I also have a column for length of time of the claim, what i need to do, is now find a way of getting a average length of a claim, but only to see the closed option from the drop down box.
for example,
row 1
open date - 02/06, closed date - 01/07, dropdown box - closed, life - 29
row 2
open date - 10/06, closed date - n/a, dropdown box - open, life - -39400
as you can see from the example above, i cant simply do a average of the whole column, as would read the open ones as well. so is there a way of doing the formula, but telling it to only see the open, or closed ones?
View 12 Replies
View Related
