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


Advertisements:










Calculate A Top Ten List


Now that I have my Keno board established ,I would like to be able to have a list of the "top 10 numbers drawn that day" I have attached a demo sheet of what I would like to do.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Top Ten List
I have a list of about 300 names with corresponding values - names in column A, values in column B for example. There are some values in the range. These will be changing on a daily basis. On a different tab, I want to list the top ten values in one cell and the corresponding name in a cell next to it. I am using the Max function but it is duplicating some of the names because the values are the same. Is there a better function that I should be using?

View Replies!   View Related
Top And Bottom Ten (10)
I'm trying to create a new report and looking to get the top and bottom ten from a list. My original idea was to use the LARGE and SMALL funcions. However, when I use those because some values may be the same, the corresponding lookup values would be incorrect.

If I have Vendor A,B,C

VendorA 100%
VendorB 100%
VendorC 98%

My report would read this:

VendorA 100%
VendorA 100%
VendorC 98%

I'm using INDEX with MATCH to bring back the vendor. I then figured, I could just link the cell to the top 10 cells from where I am pulling from, which wrks for top ten, but won't work for the bottom ten.

I'm pulling the the data from a sheet with a query so I won't know where the ending data is unlike where the starting data begins. I then thought that maybe I could use an option button to control the query and just change the sort order. But, I really don't want to use VBA on this report, which I think I would have to do for that idea to work.

View Replies!   View Related
Extrapolating A Top Ten
Every month I use a worksheet that has the following information:

Countries Amount
argentina $5
brazil 10
chile 13
china 54
denmark 8
Finland 23
France 15
India 23
Indonesia 4
Japan 3
Russia 68
south africe 39

The list is actually a lot longer but is there a formula to allow me to extrapilate the top ten amounts without using a macro to sort and filter?

View Replies!   View Related
Calculate Formulas Based On Top 20%
I've got a report that I churn out every month that calculates total spend, average daily spend, median, number of people etc and as well as that I have to calculate formulas based on the top 20% and bottom 80% of the data. The attached sheet shows the number of people under different business areas, and costs in descending order.

At the moment I do it manually so I filter for each business area and work out the top 20% of people in range and then work out the number of employees, mean, median and SUM of the top 20% and bottom 80%. Below I have 20 people and I take the top 20% which is 4 and then I work out the Total number of people, SUM and median of the top 20 and I do the same for the bottom 80.

EXAMPLE:

TOP 20%
1,330.00
1,070.36
997.50
840.00

Employees - 4
SUM - 4,237.86
Median - 1,033.93............................

View Replies!   View Related
Top 10 List
How would I set up a sheet so I can return the position of the nth highest value in a range.

View Replies!   View Related
How Do I Extract Top 3 Most Frequent In A List
I have a column of names of people who have logged calls within a period. Some of these names obviously pop up more than once and I am wanting to report on those top 3 people who have logged calls in that period.

In short, I want the top 3 most frequent in a list.

View Replies!   View Related
Rank Bottom And Top Of List
What I want to do is rank both the top and bottom of a list.

I know how to rank but unfortunately the bottom will be changing (i.e.: the table size may change from month to month and this is where I have a problem.

I am ranking a table, then doing a lookup on the list to return other values based on the ranking.

What excel formula can I use to ensure I get the bottom 5 if the list length/table size is changing from month to month?

(Please note - I need to do this in excel - not as excel VBA)

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
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
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
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
Ten Oldest Dates
I need a formula on my "Problem Areas" sheet that will search and then list
from my "Overall" sheet the ten oldest dates (column O), that have not been
closed (column B), and do not match the text "Repair Clin" (column S). Each
row of data begins with the purchase order# (column A).

On the "Problem Areas" sheet it will only list the PO# and Date Opened.

View Replies!   View Related
Tallying Totals Of Ten
I have a column of wickets (M). M5 and M6 are the number of wickets for one match, M7 and M8 for the next, and so on. Two wicket entries for each match.

I needed a formula to count the amount of times the combined wicket total for a match is greater than 10. Initially there were only a few M values, so I used this:

IF(M5+M6>=10,1,0)+IF(M7+M8>=10,1,0)+... and so on. Now I want to expand it to have more M values and this formula would become huge.

View Replies!   View Related
Finding Ten Closest
I'm looking to try and find the ten closest markets to a certain market and having trouble trying to break it down. once finding the closest ten from the annual sales, either bigger or smaller I'll be linking it to another sheet to automatically update the other sheet....

View Replies!   View Related
Return Top N List & Return Corresponding Data For N
I am trying to find the largest value, second largest, and third largest value for a column of numbers then return a content value for the adjacent column that contains the name for which each. The problem I am running into is when the second highest has the same value as the highest and also when the third value is zero (n=meaning there are only non-zero numbers for the column). I am using these formulas currently whic work when the numbers are not the same and I have atleast 3 non-zero numbers but I need to modify in order to account for the problem stated above...

View Replies!   View Related
Format Cell Value To Ten Characters
I have the following code which formats the cell values in column A to 10 characters on entry:

Sub FormatToTenCharacters()
Dim Cell As Range, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In Range("A1:A" & LR)
If Len(Cell.Value) < 10 Then Cell.Value = "'" & Application.Rept("0", 10 - Len(Cell.Value)) & UCase(Cell.Value)
Next Cell
End Sub
For example:

Enter excel in A1 and it changes to '00000EXCEL
Enter 123456 in A2 and it changes to '0000123456
Enter abc456 in A3 and it changes to '0000ABC456

I want to format it as text so that it does not have the apostrophe (').

View Replies!   View Related
Find Ten Latest Entries
I have a sheet (attached, Excel 2003) that I need an assist on. What I want to do is search the 'All Games' worksheet for the ten latest games a specific player has played.

That by its self might not be so hard, but I want to take those 10 rows (once identified) and take the sum of column G and divide by the sum of column F for those rows only.

I already have a concatenate cell in the 'All Games' sheet for each row that I use to as a link of sorts to get data from column F and G onto the 'Spring 2009' sheet. The way I have the concatenate set up, I can manually sort by it and get the 'latest 10' values I need, but I need to get it all into a formula to place on the 'Spring 2009' sheet without the manual sort.

View Replies!   View Related
Summing Ten Largest Values
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 Replies!   View Related
Routine Taking Ten Minutes To Update

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Application.EnableEvents = False
If Intersect(Target, Range("E:E")) Is Nothing And _
Intersect(Target, Range("H:J")) Is Nothing Then
Exit Sub
End If

Is there a way to make this run a little slicker , as at the moment it is taking almost 10 mins to update RngDate is $L6:$FB1038

View Replies!   View Related
Balance Sheet With Ten Days Notice
I am creating a balance sheet for my company that seems pretty simple (date, transaction type, amount, and balance), except for one thing: 10 days before every bill is due (and before every purchase is made), expense amounts are supposed to show up in the balance sheet as a deduction from the current balance, so that the sheet projects what the balance will be in 10 days. Do I need a macro for that? If so, what would it look like? It needs to be blank if it's more than 10 days out, and then suddenly appear in the balance sheet once it's 10 days away from due date.

View Replies!   View Related
Input The Name And Have That Data Populate A Different Ten Cells
how to do some complex stuff in Excel but sometimes I don't know how to do some really easy stuff. After I have named a data set of say a certain ten cells in one column, is there a function or a way to input the name and have that data populate a different ten cells? For instance if I have a huge amount of data all coded and I want to take equal but different portions and run them through a template, how can I make it so I just have to enter the a code for any of the data I have coded and have it populate momentarily where ever I need it to go? I'm sure this is very easy to do unless I have explained it inadequately.

View Replies!   View Related
Calculate Median In A Filtered List
How can I find the median in a filtered list of numbers?

I don't see median as a function of subtotal.

View Replies!   View Related
Calculate Days Between Subtotaled List
In the attached workbook, I have subtotaled a list of parts. I want to add a formula beside the subtotals, under Column C, "Days BO", that subtracts earliest date in column R from the latest date in column R, for each part number.

I have added a simple formula to the attached example that does what I want, but the full sheet will be thousands of lines weekly, with varying part numbers, and different counts. I don't know how to take the count function into account so I can copy a single formula all the way down the sheet.

View Replies!   View Related
Highest 3 Year Average Over A Span Of Ten Years
I have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet).

I need to calculate the HIGHEST 3 consecutive year average salary for each (if they have less than 3 years with salary, then it should just average the years the do have, be it 1 or 2 years).

Here is the kicker: some people have breaks in service (for these years, there is a blank in that entry). These years should be ignored and skipped in calculating the avergaes.

So if someone had salary figures in years 1, 2, and 4, but a blank in year three, the average of years 1, 2, and 4 would constitute one three year average (whether or not it is the highest is a whole other matter...).

I have been round-and-round the best way of doing this. I was thinking of maybe creating a UDF that calculates a three average, then do it up to 8 times (one for each starting year) in 8 "helper columns", and taking the highest average.

View Replies!   View Related
Calculate Average Based On Item Chosen From List
My attached files contains stock returns for companies. Each sheet contains the returns over a 5 year period for a certain stock, with the ticker symbol of the stock used as the sheet name. I want to write a sub that presents the user with a user form. This user form should have an OK and Cancel buttons, and it should have a list box with a list of all stocks. The user should be allowed to choose only one stock in the list. The sub should then display a message box that reports the average monthly return for the selected stock.

View Replies!   View Related
Scrol Bars To Only Move The Form To The Edge Of The Sheet Instead Of Ten Or More Cell Beyond
I have forms and want the scrol bars to only move the form to the edge of the sheet instead of ten or mor cell beyond.

View Replies!   View Related
Formula To Calculate Average/wtd Avg On A Subset Of A List
I will do my best to explain but just in case I have attached a worksheet to make things easier. I have a list of a few thousand products with data on each product. When I run through a series of cuts, I get a subset list and want to be able to calculated a weighted average by somehow saying to do a weighted average (and/or count, and/or average, etc.) on the characteristics of only the products in the subset.

View Replies!   View Related
Execute A Data Download Every Ten Seconds Until There Is A Value In C11 And Then Send An Email And Close
All this macro is supposed to do is execute a data download every ten seconds until there is a value in C11 and then send an email and close.

What seems to be happening is that it downloads, emails, closes... but then in ten seconds the sheet reappears and does it all again.

View Replies!   View Related
Sum Every Nth Cell & Calculate Difference Between 2 Different Time Interval List
I have two sets of data, one is recorded every 5 minutes and the other is every 15 minutes. I am trying to add every 3 cells in the 5 minute column so I can compare it side by side with the 15 minute column. I have tried one of the responses in this forum with placing 0s in 2 cells and then the formula in the third however this does not allow me to compare the 2 sets side by side.

View Replies!   View Related
Get The Average Of Every Ten Cells Out Of 1300 Cells?
I have 1300 vertical cells.

Is there a quick way to get the average of every ten cells? i.e. to end up with 130 averages of successive groups of ten cells (cell 1-10, 11-20, 21-30 etc.)?

View Replies!   View Related
Top 5 Scores
Is it possible to make a top 5 scores section?

Ive been reading up on Arrays and how to use Arrays etc so i believe that it could be done with an array but i could be complete wrong.

what i have is players names in A4 and there averages in H4. i have 22 players listed but i want to take the top 5 scores from h4 and display them in a different section with there as so: NAME(A4): AVERAGE(h4) repeated 5 times and updates Automatically.

View Replies!   View Related
Top 10 From Two Criteria
I am trying to find the top ten students house points from each of five different houses.

I have no problem finding the top ten highest points value from each house, but I also need to return the name of each corresponding student in a seperate cell

I have played with some formulas (mix and match method) but the problem is when the same value for house points appears for different students in different houses.

In the attached example (the actual sheet is much bigger) the second return 225, gives the wrong name. I want to have a series of top ten result tables for each house.

Thought Id cracked it (F6), but now hit a brick wall. Are IF statements the wrong way to go?

View Replies!   View Related
Top 90 Percent
how to mimic SELECT TOP 90 PERCENT from Access in Excel?

I can't use the percentile function because it interpolates the value if you don't have the right multiple of values in your array.

View Replies!   View Related
Top 5 Generator
my spreadsheet logs call references and total times, as well as the date.

All the data is stored in the worksheet 'Data' while I want this report to be generated under the worksheet 'Admin' - I do not want it to be live either, I would want it so you press a button to generate the report so the data does not change (hopefully making this easier in the process).

There are 2 things I want to do with the report, first is to log all the calls by date to generate an average per day. Secondly, I want to generate a list of the 5 longest calls so these can be reviewed.

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
Aveerage Top 20%
Is there any way one formula will give me the average of the top 20% of numbers in a column. Thanks

View Replies!   View Related
Top 25 Worst 25
I just wanted a formula to give me the Top 25 product by income and the worst 25. A little more complicated then using the sort button. I'd be looking up the values on H9 as top 25 and have all info from c9 to j9

C9 = Item #
D9 = Model
E9 = Value
f9 = SKU
g9 = Days active
h9 = rev in
i9 = net gain
j9 = avg per day

View Replies!   View Related
Keep Columns On Top
I feel stupid, for the life of me I can't remember how to keep colums on top while scrolling below.

View Replies!   View Related
Top 10 Percentages
I am working with a list and would like to show the top 10 careers in %.

Out of 914 votes __ % are interested in Nursing, __ % are interested in ABC, ___ % DEF, etc.

View Replies!   View Related
Looking Up Top N Using Criteria
In workbook A, I have a unique list of customer IDs in column A:

Cust ID
1 000011
2 000012
3 000013
4 000014
5 000015

In workbook B, I have a bunch of product / revenue information related to each customer. Each customer may have multiple rows of data on this workbook, but each row would be a unique Cust & Prod ID combination: ...

View Replies!   View Related
Form Always On Top
I'm new to programming in excel and would like to know how I can make a form always visible in my workbook. I would like to be able to modify any cells or change sheet and still have the form open. I don't know very much about excel programming yet so you'll have to be simple.

View Replies!   View Related
Top X From Each Group
I have multiple names in each of 10 groups. Each name will have a number beside it. I wish to have a separate worksheet that will contain only the name with the highest number beside it, from each group.

View Replies!   View Related
VBA Selecting Top 3
I'm trying to make a macro that selects the 3 highest values and give these cells a blue backcolor. (The code should skip any blanc cells).

View Replies!   View Related
How To Create Signals For Top 20%?
As you can see from the chart or picture. I want to create a =IF function signal so that on the right column it will show which of the hours is the top 20%/top 5 hours

Is there anyway possible in excel to do this? To create a signal on the right showing which of the days hours, is the top 5 hours with the highest volumne?

View Replies!   View Related
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
Average Of Top 3 Numbers.
I m making a worksheet for our shooting results. I need the average of the
top 3 results from collum E6 to W6 and top 3 reults from Y6 to AJ6. Can


View Replies!   View Related
Finding The Top 3 Of Each Area
I have a list of tons of items that have lost orders against them. The items themselves are catagorized in 11 different areas and I'm trying to find the top 3 of each Area. These items are sorted descending by the number of lost orders and will constantly be changing every day. Here is an example of how my workbook is set-up:

A B C D E ....

View Replies!   View Related
Top 5 And Bottom 5 With Conditions
I have a huge spreadsheet that pulls 5 different types of data based on user statistics and I am trying to come up with a sheet that will rank the top five and bottom 5. I've learned a lot from seach this site and others but I am still running it to some formula problems, so I thought I would tackle a couple here.

The first is the top 5 and bottom 5 of a conversion rate. I use this formula to get the top 5.

=IF(COUNTIF(PersonList!$Z$7:$Z$61,">10"),LARGE(PersonList!$AA$7:$AA$61,1),0)

Column PersonList!$Z$7 is the number of opportunities
Column PersonList!$aa$aa is the conversion percent being ranked

It works, but not like I am intending it too. I'm trying to elimanate those with less than 10 opportunities, and the first 2 people on the list have less than 10.

And for the bottom 5, I am using

=IF(COUNTIF(PersonList!$Z$7:$Z$61,">10"),SMALL(PersonList!$AA$7:$AA$61,ROWS(K29:K$33)))

Column rows k29 to k33 is where the bottom five conversion percent sits, k33 being the lowest, most likely zero but could be negative

Again trying to eliminate those will less than 10 opportunities. Plus when this populates, if more than 1 person has the same score like 0.0, only the first person will be listed for the same multiple value of 0.0

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
Copy Top Row Down
I need a macro that will copy the first row of a worksheet, insert rows below and insert the data.

Sheet1 *ABCDEFGHI1*1234567*2*1201212****3200010010099.7699.76***20004*1201212121215*5202599.7610099.7699.7699.7699.76202520256*120121212***7202599.7610099.7699.7699.76**2025 Excel tables to the web >> Excel Jeanie HTML 4

To This:

Sheet1 *ABCDEFGHI11*1234567*12*1201212****13200010010099.7699.76***200014*1234****15*1201212121215*16202599.7610099.7699.7699.7699.762025202517*1234567*18*120121212***19202599.7610099.7699.7699.76**202520*12345*** Excel tables to the web >> Excel Jeanie HTML 4

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