Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










How To Count Top 10 Values


I couldn't find the right formula for this... I'd like to sum top 10 values from a range of values in cells. Currently only way I can think of is to sort all the values first and then count the top 10 cells. But is there a way to find the top values with a automatic formula, which would fit to one cell?

Example:

A
4
6
8
3
13
4
16
11
9
2
8
8
10
5

Sum from top 10 values would be 96... But how to reach this with a formula?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Count Top % Contributors
I have a list of about 2000 Suppliers.
In Column A is the Name of the Supplier and
in Column B is the % Contribution of the business I am doing with that Supplier -
Column B is sorted in decending order.

Part 1: In Cell C1 I would like a formula that counts the number of Suppliers that make up 80 % of the contributions. ( the closest to 80% give or take 5% )

Part 2: If the List is not sorted - What would the formula be, to count the TOP 80 % Contributions ( the closest to 80% give or take 5% )

So I would get, for example, 160 of the 2000 suppliers making up the top 80 % of the business.


View Replies!   View Related
List Top 10 Records According To Count Totals
How do I create a formula that will list the records with 10 highest results
and if a tie, list the tie record with the lower value next.
Each record is is double digit value from 00 to 99:

- the records are in range ff7:ff106
- the record results in range FG7:FG106
- List the highest rankings in FH97:FH106

View Replies!   View Related
Get Top Three Values
I'm looking for a formula that will return the let me pull the top three values from a list. In column B there are 50 values, not sorted and in column A is the name of the person the value belongs to.

The formula will be entered on another sheet. When I copy the formula down tree rows it will place 1st place in spot one and then 2nd place in spot 2 then 3 place in spot three.

View Replies!   View Related
Top 10 Text Values
I have a column of text values in which I need to display the ten most
frequently occurring. I tried setting the AutoFilter and choosing "Top 10",
but it doesn't seem to be doing anything. I checked into some functions that
I thought might work, like Frequency and Rank, but those seem to be for
numbers only.

View Replies!   View Related
Lookup For Top 10 Values
I have a table that has 1000 people who have a duration of time associated with them. I need to get the names and times of the top 10 people. I would prefer to use formulas so that I can just make a template that I can paste my data into.

View Replies!   View Related
Return Top 10 Values In An Array
I have a spreadsheet of information (product codes and values) and want to use VBA to find the top ten list of products by value. The list of products is variable in length so I can pass the data to a two-dimensioned array.

How do I ask VBA to return the top ten products by value - there can be duplicate values so I just want ten (if there are 4 #9s, I just want two of them, any two)?


View Replies!   View Related
Extract Top X Maximum Values
How can I extract a list of the top five maximum values from a range of cells. This is similar to using the the "top ten list" in the Auto Filter but I don't want to filter my data in place. Instead I want to extract a list sorted in descending order along with desciption data located in adjacent columns for each maximum value.

In other words take the following list with descriptions in column A and values in column B

Item 1 = 10
Item 2 = 10
Item 3 = 2
Item 4 = 20
Item 5 = 4
Item 6 = 100
Item 7 = 5

and extract the top five values and their desciptions to another location in descending order

Item 6 = 100
Item 4 = 20
Item 1 = 10
Item 2 = 10
Item 7 = 5

I am wondering whether this can be solved with a combination of matching functions and indexing functions or whether I will need VBA code to solve this.

View Replies!   View Related
RANK Question - Top 20 Values
Got a question about RANK. As you can see from the example below, I want the rank of 1 to be 6, not 7 - same values keep the same rank but the next value takes the next rank number.

Is there a way to achieve this - I need to rank around 4000 lines...

View Replies!   View Related
Adding Top 5 Values In A Range Of 20
how I would get the top 5 highest values from a group of 20 and add them together?

View Replies!   View Related
Extracting The Highest Top 5 Values
extract the highest top 5 values from one column in a filtered table with hidden cells. How can I do this without using advance filter or a pivot table in excel 2003 that will show only the visible cell values and not the hidden cell values


View Replies!   View Related
Pull Top 3 Highest Values
I have a column of numbers that I would love it if Excel could pull out the top 3 or 4 highest values from the column.

View Replies!   View Related
Show Top 10 Values With There Associated Data
I would like a formula to return the top 10 products sorted by value, but to return the colour name in order starting with the larges and the value in a different column. Ideally I would like to do as little formating as possible. I have attached a spreadsheet as an example

View Replies!   View Related
Top X Largest Values From Column
How would I find the four rows with the largest value in a defined column and then export them to a separate worksheet.

View Replies!   View Related
Chart Top Or Bottom X Values
Chart Building With Ranked Data. I have adapted the offset formula for my spreadsheet, but what to do if 2 values tie. I need the top 10 values to populate my chart, but if there are two values which rank joint first place, the formula only extracts the first value. Is there any way I can get round this?

View Replies!   View Related
List Top Occurred Values
Does anyone might have a formula that it will list top occurred values? Sample file below.

View Replies!   View Related
Find Top/bottom 3 Values From A Range
I now have everything set up to do exactly what I want except this last bit.

All I want is for the overview page to show the top three referrers for each month. At the moment, it's finding the top and bottom 'scorer' but if more than one has the same score I can only see the first alphabetically.


View Replies!   View Related
Puting The Values At The Top Of A Bar Graph
I have a bar graph..and on top of each bar, i want the x value for that bar.

View Replies!   View Related
Selecting Top 5 Values From Multiple Ranges
I have four ranges of data in a worksheet that are sorted in order by the value in the final columns. Based on this value I would like to flag the highest five values from all ranges by adding a value in the adjacent cell. Is there a formula/function that I can use to identify the highest values from a number of data ranges?

View Replies!   View Related
Rank Values Into Top/Bottom X Scale
I am creating a Power Poll Spreadsheet for a Fantasy Football League, and I need to create a code that will take the win/loss columns and in a new column assign rankings so that the team with the most wins gets ranked '1st', then '2nd' etc. I'd like it to, in the case of a tie, rank both teams equally 'the 3rd and 4th best teams both have identical records, so both get ranked '3rd' and the '4th' ranking gets skipped.

I have searched for a formula to allow this, and I can't find it. Can anyone help? As an aside, is anyone aware of a place I can find a listing of formulas that can be used in sports ranking, etc?

View Replies!   View Related
List N Top Values By Single Condition
I want 3 extra columns in the attached sheet which is part of a 62000 row sheet.

column d..I want a formula for the lowest ip number(COLUMN C) in each event( event number in column1) printed on all rows in column d fo that event...

column e...second lowest ip number for each event..

coolumn f..third lowest ip number for each event..

View Replies!   View Related
Display The Top 5 Values Withinin A Range On A Different Row.
Excel Formula, that will display the top 5 values withinin a range in another area.

Example: I have a row of number, 16, 72, 87, 12, 45, 23, 12, 43 etc.
I want a different row to display the top 5 values from the this row/range. I do not want to simply conditional format the first row.

View Replies!   View Related
Finding Top 2 Values Per Group Based On Multiple Criteria
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 Replies!   View Related
Find The Top 5 /bottom 5 Values Using The Large/small Functions
I understand how to find the top 5 /bottom 5 values using the large/small functions, but the question I have is: How do I get associated fields. For example I attached a sample sheet with values (Fields: Ticker,TE,Sector,Return). How do I get the Ticker,Sector that the return is for. I'd like to show all the associated fields that relate to the retun, but I can't find the solution on how to do that.

View Replies!   View Related
Create Dynamic Top-x List Based On Cumulative Values
My objective is to automate the creation of a dynamic top-5 list by summing a number related to multiple entries of an item (text string). Data are added monthly and while the textual strings (imported) are constant (text1 always reads as text1) the text strings themselves may vary from "text1" to "text2", etc. What I'm trying to achieve is finding the top-5 five items and copy/paste them to a range on the same worksheet. The range into which they are pasted are source cells for DSUM criteria. The frequency with which a text string appears is not critical, but the associated count is.

View Replies!   View Related
Count Unique Values, For Specific Values
I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1))

Now, what I would like to do, is see how many of these unique numbers relate to another number in column D
See attachment.

View Replies!   View Related
Count Intervals Of 2 Numeric Values In Same Row And Return Count Across Row
I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) between each
individual occurrence of any designated PAIR of Numeric values (single-digit
/ double-digit) in the same Row of the Named Range "Results" and return each
calculated INTERVAL result to a separate Column on the same Row of a New
Sheet - starting with the most recent ( the LAST) occurrence.

For instance, each time 80 and 87 appear together in the same Row, return the
INTERVAL by calculating the number of Rows between the LAST instance and the
PREVIOUS instance in a column - locate when both Numeric values LAST appeared
together and Count back to their PREVIOUS appearance together to get the
required Count; i.e. count from the Row ABOVE LAST appearance to the Row
BEFORE PREVIOUS appearance.

The results are returned to a chart / matrix layout: I have the criterion
vertically and horizontally and they are referenced using the horizontal and
vertical cell address that houses each criterion, and the results are
returned across the Row of the intercept of the vertical and horizontal
criterion. At some point both criterion values being referenced will be the
same, can the Formula return empty text "" when this occurs?

Example Chart / Matrix Layout:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and B1 criterion 81 and 80
Cell Ref. A4 and B1 criterion 82 and 80

Criteria B1 houses 80
A2 houses 80
A3 houses 81
A4 houses 82
A5 houses 83

View Replies!   View Related
Count Some Values But Not Others
I am trying to count the number of the cells below EXCEPT when the value equals "OFF". So the answer should be 6 (8 cells - 2 OFF's). Would anyone know how to make a formula that would do that?

$0.10 BALANCEDOFFOFFBALANCEDBALANCEDBALANCED($1.00)

View Replies!   View Related
Count Values Less Than Zero
I am using and index and match formula to find what date the value is less than zero. However I am not using a continous time series in my figures but a time span for example 2008-01-30 - 2008-02-05. The formula works fine with a continous time series but not for time spans. Does anyone have a clue to get round this. I am enclosing an example where the formula works and also an example where it does not.

View Replies!   View Related
Count Consecutive Values
I'm trying to use this formula to count consecutive values but somehow it doesn't work properly.
Does anyone see what's going wrong here or is there a better formula to this?

View Replies!   View Related
Count These Sorted Values
Still i could manage with Sorting of data. Now i have following data

Name Project Artha Artha Prj Artha Artha Prj PBB ACR Seema Brew Seema SEMC Seema handset dev & testing Seema SEMC Seema handset dev & testing Shashi PRJ1 Shashi PRJ1 Surajit FLV Surajit FLV Surajit FLV
And i want to have following result using a macro
Name Project Count Artha Artha Prj 2 PBB ACR 1 Seema Brew 1 Seema handset dev & testing 2 Seema SEMC 2 Shashi PRJ1 2 Surajit FLV 3


View Replies!   View Related
Count Unique Values ....
So here is my dilemma and I haven't been able to find anything to exacly help me out:

I have a large (100k+ records) sheet of sales invoice details.

I need to add a total item line per invoice number to each record ....

View Replies!   View Related
Count IF, Then, Else Statements That Have Values
I tried Count if statements and sum statements, but nothing is working. I
have 2 columns Ex: If "Jane" from column 1 and "Dog" in column 2 then print
the total number it repeats. This sheet is large. Here's and Example:
Here's the data
Jane Dog
Jim Dog
Jeff Dog
Jane Cat
Jim Dog
Jane Dog
Here is what the cells should print (just the number)
JaneDog = 2
JimDog = 2
JeffDog = 1
JaneCat = 1

View Replies!   View Related
Count All Unique Values
Is there a formula that can be used to count all unique values in a specified column.
Example:
Col. A
123
123
Test
ABC
ABC
ABC

Result: 3 (since there are 3 unique values in col. a)


View Replies!   View Related
Count Consecutive Values ...
I'm looking for a VBA code or formula that will count consecutive values in a column.
Please look at my example sheet.

View Replies!   View Related
Code To Count Certain Values
for code that will test cells e3:g3 in sheet 1 of workbook "AR" for a value of "OK" or "Error". These cells contain If formulas that return "OK" or "Error"

If all Cells = "OK" then I need the macro to put "Ok" in D5 in sheet 1 of workbook "Guide". If 1 or more cells = "Error" then put "Error" in D5.

Also, I know I could use a formula in D5, but I really need the code for my specific application.

View Replies!   View Related
Count Non-repeating Values
It has several steps and i will now just explain the least complicated one, and hopefully once i get this done i will figure out the rest myself. If not then i will need some more help

I have 500 entries (names) in column A and each entry has a coresponding category in Column B (Large, Medium, Small).

I want to see how many entries are there for each category, but i need to count the names which appear in the list only once. If the name is entered more than once then i dont need to count it.

I hope i made it clear as i am not able to provide excel table as an example do to security settings.

I am usinng Excel`03

View Replies!   View Related
Count Unique Values
Card Number
XXXX5512
XXXX5512
XXXX4211
XXXX4510
XXXX6134
XXXX9035
XXXX6002
XXXX8653
XXXX7826
XXXX8819
XXXX8315
XXXX0274

I'm looking for a formula to generate the number of unique card numbers are in the column...I'm using this daily, simply pasting the new data in so if I want to keep it as automated as possible.

View Replies!   View Related
Count Values Greater Than Zero
I want to look to a range and if there is a value greater than zero I want to count it. I keep going round in circles trying to do this and now I give up.

View Replies!   View Related
Count Values In Column
I grow my business by dialing a massive amount of leads everyday which I manage in Microsoft Excel. In row "E" I have it labeled as Dispositions and each call I make gets labeled as..

WN = Wrong Number
AM = Answering Machine
UA = Unavailable
NQ = Not Qualified
DNC = Do Not Call
IV = Invite
SA = Sale

I would like to create functions or formulas which displays the total number of each of these dispositions from column E and shows the totals in separate cells so that I dont have to count them manually anymore.

View Replies!   View Related
Count At Least 4 Consecutive Values
I have a range of binary numbers and would like to count the number of times the value of 1 appears BUT there must be at least 4 consecutive 1's.

Here's an example:
1
1
1
1
1
1
0
1
0
1
1
1

The final count should be 6.

View Replies!   View Related
Count Of Multiple Values
I need a formula that will count all instances of A*, A, B & C in a row D3: Y3

so if

D3....E3....F3....G3....H3.....I3
D......C.....A*....A......E......B

would return 4.

View Replies!   View Related
Count And Sum Values
I am trying to enter a formula that will count the number of values in a column and multiply the total by a decimal. For example if A1:A5, contains the following A, A, B, C, B then I need a formula that would multiple the number of occurrences of "A" (2) * .25, the number of occurrences of "B" (2) * .125 and the number of occurrences of "C" (1) *.0625.

View Replies!   View Related
Count When Values Occur In Different Arrays
My difficulty is this. I have 2 columns, A and B. A contains only 0's and 1's. B contains any number from 1 to 100. I want to count all the instances of any given number in Col B that are matched with a 1 in the corresponding cell in Col A.

View Replies!   View Related
Find And Count Unique Values
I have a spreadsheet that holds customer information. What I want to do is find how many customers there were last month. I have a cell (C1) which has last month displayed as 2009/05. A1 holds the customer ID and B1 holds the date they used us. Each customer may has used us many times and I'm having a nightmare trying to solve this.

View Replies!   View Related
Sort The Data :: Count The Zero Values
In a column I have loads of numbers like this except there are 5000 of them.

3
2
0
0
0
0
2
0
3
0
0
2
0
9

how could i sort it into another column so it stays in the same order but counts the 0 values so it would look something like this.

3
2
0 (x4)
2
0 (x1)
3
0 (x2)
2
0 (x1)
9


View Replies!   View Related
Count Two Different Text Values Within Two Columns
I need to count two different text values within two columns.

Column A contains sentences and Column B contains various values.

Example of small sentences in Column A:
Custom XX Upgrade
Printing reports on XX
Custom YY Upgrade

Example of values in Column B:
AAAA
BBBB
BBBB

What I need to do is count groups of records, like only counting the records
that contain “XX” anyplace witihin Column A plus have “XXXX” in Column B.

View Replies!   View Related
Count Unique Values With Critera
I have a large list of data and need to filter unique values of 1 column with a criteria.

For example:

A B
Car 1
Bike 1
Car 2
Car 2
Bike 3
Bike 3
Bike 4
Car 4

I want to filter out only the lines whereby the column A & B is the same comment/value.

Other words: both column A & B have to be unique values.

Based on the list above this means that the list should look like this:

A B
Car 1
Bike 1
Car 2
Bike 3
Bike 4
Car 4

View Replies!   View Related
Count Values Greater Than Or Equal To Zero
How can I count the number of cells in a range that contain values that are greater than or equal to zero?

View Replies!   View Related
Count Of Unique Values With Duplicates
Good Morning Peps (Oh thats for users in Western Europe, got to get this right!)

I have these 12 columns (Well there is 12 Columns but this forum's HTML is not showing the last two lol, would much easier if Office Web Components was able to run on Firefox): ...

View Replies!   View Related
Count The Values Through Sumproduct In VBA
The problem facing by me that I have a worksheet in which I count some values through sumproduct function in vba but its not working but if i manually put in this in sheet it works.here is the code.

Dim Sal As Workbook
Dim rng As Range
Dim rng1 As Range
Dim Dept As Range
Dim Dept1 As Range
Dim rg As Range
Dim i As Byte

Sub salries()
Application.DisplayAlerts = False
On Error Resume Next
Set Con = Workbooks("Branch Wise Deparment Wise No. of Staff.xls")
Set Sal = Workbooks("salarysheet.xls")
Sal.Activate
Sheets("Working").Delete
Sheets("GT").Activate
Range("B3").Select
Set rng = Range(ActiveCell, Selection.End(xlDown))..............

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved