Excel Function For Highest Number For Every 10 Numbers?
Dec 11, 2013
Suppose I have a column filled with numbers in fraction. And correspondingly I have a specific name for every 10 numbers. I want to find out which is the maximum among the 10. For this I could use the "MAX" function in excel. Similarly I have to find the maximum among the next 10 numbers. Since I have about 200 such numbers under 20 different names and eight such sheets (10 numbers per name), it becomes difficult to type the formula and select the range everytime. I would like to know if there is any shortcut for getting this highest number among the 10 numbers.
Eg.
Name Value Number
TU 489.662.272
TU 380.450.0379
TU 331.970.0475
TU 309.641.004
TU 300.890.1215
TU 288.840.0501
TU 280.090.022
TU 278.580.0115
TU 270.160.0397
[code]....
View 6 Replies
ADVERTISEMENT
May 10, 2014
I have a cell containing alpha and numeric. I would like to extract the highest number in the cell
the cell looks like this
the answer I would like is
====G99876554321000
9
T1111111100000000
1
I am using Excel 2010
View 3 Replies
View Related
Mar 23, 2009
I'm using the prob() function, and it works for no more than 61 random numbers. Does anyone know of a limit on the number of random numbers used in the function, and whether it's possible to use more numbers in the function? My formula is:
=PROB($C$11:$C$71,$L$11:$L$71,G11,H11)
View 11 Replies
View Related
Jul 22, 2012
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
View 2 Replies
View Related
Jul 22, 2012
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
View 1 Replies
View Related
Aug 6, 2012
I have a column A with following values below:
Column A
"VL50s"
"M50s"
"H50s"
"VL50s"
"H50s"
I would like to extract the numbers and run the following arithmetic function below into column B.
key:
x is a number
VLx --> (x) + 1
Mx -->(x) + 2
Hx --> (x) + 3
the output should look like the following using the key above:
column B
51
52
53
51
53
View 9 Replies
View Related
Jan 20, 2014
I'm trying to wrap up a spreadsheet and I'm trying to figure out how to have my formula use the highest of 2 numbers when making a calculation.
For example:
If:
Cell A1 = 5000
Cell B1 = 3828
I want the formula to use A1 in this case. However, if the total of B1 = 6000, I want the formula to use B1 instead of A1 in the calculation. (A1 = 5000 will always be a constant/base).
View 4 Replies
View Related
Jun 7, 2006
Is there a way to average only the X highest number of scores in a row?
What about if there is an additional condition, i.e., if the column
heading says "Quiz", for instance, instead of "Test"?
View 9 Replies
View Related
Dec 1, 2006
I have A bunch of numbers going up to 4.0 from 0.0 how can i get it so that it takes the number and the name beside it and buts it in a colume from 1 to ..... complete
ex)
David 1.5
Jon 3.5
Sally 4.0
Susan 3.24
Fred 2.99
View 9 Replies
View Related
Aug 1, 2009
I came up with a way to extract entire row corresponding to the highest number in L column and to post it in the different sheet.
I have 2 issues to solve though.
1. I need to extract only 13 ranges corresponding to the highest number in L column - ( A through M) - not entire row.
2. I need to do the same with the second and the third highest numbers of the same column L.
This is what I have so far:
Sub extract()
Dim first As Range
Set first = Range("L" & WorksheetFunction.Match(WorksheetFunction.Max(Columns("L")), Columns("L"), 0))
first.EntireRow.Select
Selection.Copy
Sheets("Result").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
View 9 Replies
View Related
Jul 27, 2007
I want to pick the top five numbers out of a random list of numbers and get its average. What is the best way to go about doing this?
View 2 Replies
View Related
Nov 22, 2007
I am looking for a formula that will add the 8 highest values in a range of up to 20 cells. The range may have fewer than 8 values in some cases in which case the sum of all those values will be returned.
Thus a row with these values will yield the following output (bold denotes highest 8 values):
8, 28, 45, 13, 17, 19, 21, 30, 47, 8, 15
47+45+30+28+21+19+17+15= 222
View 9 Replies
View Related
Apr 23, 2006
Im just setting up a spreadsheet for structural timber design calculations. One of the requirements for this spreadsheet is to look up a Zx value from a table. Once the Zx value is found it returns a size of timber from another column. I have managed to create the function so that it looks up the Zx value and returns the size. However, if the function cant find the exact Zx value, which will usually be the case. The next lowest Zx value is used. Is there a way i can edit this function so that it returns the next highest Zx value instead of the next lowest. At the moment this is the code i am using.
= LOOKUP(Q92, 'Zxx Table'!E5:E172,'Zxx Table'!C5:C172)
Where Q92 is the value to be looked up. E5:E172 is the column of values to be searched. C5:C172 is the column of values where the result comes from. In addition to this just to further complicate things, can it return say the next 5 highest values? If a lookup function cant be used could you tell me another way that i can achieve this.
View 7 Replies
View Related
Jul 24, 2014
I am looking for a way to find a highest (and/or) lowest value in a list of numbers. The list keeps getting longer and after a certain point I will need the 2 highest (or lowest), then the 3 highest (or lowest). I know MIN and MAX will find the high and low, but how do I find the 2nd highest, etc?
View 3 Replies
View Related
Nov 16, 2011
I am looking for a formula to extract numbers from a list in descending order. As the list may have more than one entry of the same number the formula i need should only show one of that particular number.
So in effect
Column A
40078
40025
40001
40003
39972
41224
40025
40078
40001
Hopefull end result
Column C
41224
40078
40025
40003
40001
39972
View 9 Replies
View Related
Jul 5, 2012
I have a row of upto 20 numerical entries, (1,1,2,2,10,10,3,3,4,5,6,7,8,12,1,1,7,8,9,11) what formula do I use to go about getting excel to return the two highest numbers?
In this example I'd want excel to return the numbers, 12 and 11
View 1 Replies
View Related
Jul 15, 2014
How do i take the three highest numbers from E4:E5 & E13:E14 and add them together in E26
View 3 Replies
View Related
Jun 23, 2007
I am trying to add the highest 4 or
View 9 Replies
View Related
May 20, 2014
I have a spreadsheet where columns I through V are date fields (stored as text as some have letters after the date). Is there a way that I could write a formula so that Excel returns the column letter of the last cell without a letter at the end of it? So it kind of looks like this:
I J K L M
05/01/2014 05/15/2014 05/30/2014(p) 06/20/2014(p) (null)
What I want in the column with the formula then would be just the letter J. Is this even do-able?
View 3 Replies
View Related
Feb 18, 2014
I have thousands of drawings listed in colums and each in the row has revisions P01 to P10 for Proposal and C01 to C10 For Construction. Is there a way of getting the highest revision of all listed in a row without turning them into numbers when P is older than C , but 1 is older than 10? MAX function doesnt count when value consists of both - number and letter.
Manualy selecting the highes revision will cost me weeks... and brain damage.. as I have thousands of drawings and more are coming...
View 14 Replies
View Related
Jan 29, 2007
I need to know how I can create a formula which will sum a selection of the highest numbers in a range of data. So for example, if I have a row of 10 cells each containing dfferent numbers, and I want to take the 7 highest values from there and get the sum of these values, how do I go about this?
I've tried using the LARGE command but can only get an individual figure.
View 9 Replies
View Related
Jul 17, 2009
I am trying to generate a list of the 5 highest and 5 lowest numbers from a list of scores that range from 1 to 10. I have found the highest and lowest absolute values (numbers over 7.5, and less than 5, out of 10), but I would also like to generate the 5 highest and lowest relative numbers, ie. if there are no scores over 7.5, then the 5 next highest numbers. I have used if/then formulas for the absolute highest and lowest numbers, and a rank/countif formula to rank them. I have no idea how to generate a list of the "relative strengths and weaknesses".
View 14 Replies
View Related
May 2, 2006
I must determine out of 52 radom numbers the average of the 48 highest even
if one or more of the lowest numbers is repeated 7 times without exempting
the 3 out of the 7 that must be included in the 48 to extract the average.
View 11 Replies
View Related
Sep 6, 2007
Assume Cell A2 + A3 as the fraction: 60/100. How can I get the smallest fraction (3/5 in cells B2+B3). I do not want to use the build in cell format for fractions)
There are a few samples of want I would like to get a s the result (Red Digits). I will appreciate 2 ways: via VBA Code and via Sheet Build-In Functions.
View 5 Replies
View Related
Feb 17, 2014
I can't find a way to sort my data. It looks similar to below. And I need to sort them based on the numbers from lowest to highest.
Tom Simmon (23.2)
Jason Paige (19.1)
Jeff Johns (14.1)
View 8 Replies
View Related
Jul 6, 2013
I have two sheets:
Sheet one has dates, my prices on each of those days, and the fields h1 h2 h3 h4 (h1 is the closest higher number, h2 is the next closet higher number, etc.) and l1 l2 l3 l4 (l1 is the closest lower number, etc. etc.)
Sheet two has certain dates and prices of a competitor's prices.
What I'm trying to do is two things (see attached):
1) find the four closest higher and four closest lower prices (if available)
2) use only the prices that are on or before the date in question.
I've tried combinations like LARGE and OFFSET and so far it is not getting me anywhere.
Can this be done???
View 4 Replies
View Related
Feb 17, 2014
Basically, I have a worksheet that shows test results for 6 separate tests. If an objective is met in a test then it is marked with an "X". If an objective is partially met then it is marked with a "/" and if it is not met then it is marked with a "." . I have now created a combined column where I want to collate whether an objective has been met at all across the 6 tests.
For e.g. if in the range H14:M14 there is an "X" then N14 should show "X". If there is no "X" but there is a "/" then N14 should show "/" and it there are neither values in the range then it will show "." .
I tried
[Code] ....
but it just came back with a formula error.
View 3 Replies
View Related
Feb 7, 2014
I have a list full of different IDs with different version numbers (This format: AKH123.1).
I'm trying to clean the list - removing all duplicate IDs but leaving only the largest version number.
I managed to remove the last digit using LEN/VLOOKUP and removing duplicates, but leaving only the largest version number is too tricky for me. I've thought about trying something with LARGE but can't wrap my mind around it.
My thought process went like this: I want to find all occurrences of "AKH123" and return the largest one.
The list contains about 8000 entries and this would save en enormous amount of work.
Here is a tiny portion of the list:
AKH450.1
AKH451.1
AKH451.3
AKH451.4
AKH451.5
AKH451.6
AKH451.7
AKH451.8
AKH452.1
AKH453.3
AKH454.1
AKH455.1
AKH455.2
View 3 Replies
View Related
May 30, 2008
I have imported and filtered a .csv. to specified sheet names. I have rows that have been sorted by a specific column's cell contents. i.e.
A B C C E F G H I
xxx xxxx xxx xxx xxx 1 xxxx xxx xxx
xxx xxxx xxx xxx xxx 1 xxxx xxx xxx
xxx xxxx xxx xxx xxx 2 xxxx xxx xxx
xxx xxxx xxx xxx xxx 2 xxxx xxx xxx
xxx xxxx xxx xxx xxx 2 xxxx xxx xxx
I need to be able to select all the rows or ranges that contain a common value 1's and then loop back and select the next group 2's of rows until the row or column contains "".
View 4 Replies
View Related
Feb 23, 2014
I have the following list
Column A Column B
Name of Parts Amount
A 4500
B 5500
C 8000
D 12000
E 4300
F 28000
G 1000
H 7000
I 10000
J 5000
K 11000
Total No. 11
I want the result as follow.
Slab No. of Counts
0 to 5000 4
5001 to 10000 4
10001 and Above 3
Total 11
View 3 Replies
View Related
