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

Return Largest 15 Day Average Of A Range

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

Formlula: Subtract Largest Number Then Average

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

Average Of List Of Cells VLOOKUP Values?

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

Lookup A List Of Values & Average Results

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

Average A List Of Values But Some Values Contain A Zero

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

Find Largest Value In List And Input 1 In Corresponding Row To Its Right

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

How To Get Largest Number In List By Using Function

Jul 31, 2013

How can I get the largest number in this list by using a function?

View 4 Replies View Related

Sum Nth Largest Values

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

Sum Of X Largest Values

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

Down The List On The Largest Table Returns No Data

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

Summing Ten Largest Values

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

Top X Largest Values From Column

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

Finding The Largest Numerical Component From An Alphnumerical List

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

2003, Returning 2 Largest Values

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

The Largest Values Based On Criteria

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

Highlight Smallest & Largest Values

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

Retrieving Column Header For Largest Or Second Largest Value In Row?

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

Determine The Largest Magnitude Of A Subset Of Four Values

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

Find 4 Largest Values In A Row And Change Background Colour?

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

Extract Largest Values From Column B And Corresponding Text Values In Column A?

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

Index Matching: Formula To Give "name" Associated With The Largest Number In A List

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

Largest Number Starting With "9" In A List

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

Formula For Getting Average Of Values Without Counting Zero Values In Range?

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

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

Calculate Average In Dynamic List

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

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.

View 9 Replies View Related

Average With Huge List Of Criteria

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

Excel 2010 - Getting Average When Have A List Of Dates

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

Formula To Get An Average Based On A Dropdown List

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







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