Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the value - 18 Dec?
Does anyone have any suggestions on how to determine the value?
There is a list of value under column A 36,36,42,42,42,48,48,48,47,47,25,25,25,25 I would like to determine the largest value under the lists, which is 48 in cell B1 I would like to determine the second largest value under the lists, which is not equal to 48. It should return 47 in cell B2 I would like to determine the thrid largest value under the lists, which is not equal to largest and second largest. It should return 42 in cell B3 Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the value - 18 Dec?
Source numbers in A1 down
In B1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",A1)) In C1: =IF(ROW()COUNT(B:B),"",LARGE(B:B,ROW())) Copy B1:C1 down to cover the max expected extent of source data. Minimize col B. Col C will return exactly what you seek. P/s: Don't you think its high time you changed the subject line to better reflect each of your queries. As-is its becoming a bit of a monotone. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "Eric" wrote: Does anyone have any suggestions on how to determine the value? There is a list of value under column A 36,36,42,42,42,48,48,48,47,47,25,25,25,25 I would like to determine the largest value under the lists, which is 48 in cell B1 I would like to determine the second largest value under the lists, which is not equal to 48. It should return 47 in cell B2 I would like to determine the thrid largest value under the lists, which is not equal to largest and second largest. It should return 42 in cell B3 Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the value - 18 Dec?
You can simply use the following function for highest number =LARGE(A:A,1) for second highest =LARGE(A:A,2) and so on LARGE Worksheet Function To return the n th largest value in a set, use worksheet function LARGE to return 1st highest, 2nd, 3rd, ... and so on. Syntax LARGE(address of array,n ) n= the position (from the largest) in the array or cell range of data to return. I hope this helps. Selva V Pasupathy For more on Excel, VBA, & other resources Please visit http://socko.wordpress.com/ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the value - 18 Dec?
You can simply use the following function
for highest number =LARGE(A:A,1) for second highest =LARGE(A:A,2) I doubt the above applies here. Re-read the OP's post carefully. What's key here are the multiple ties in the various source nums, and OP wants to extract only the uniques in descending order. The simple use of LARGE as you suggest simply fails. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the value - 18 Dec?
On Wed, 17 Dec 2008 22:26:00 -0800, Eric
wrote: Does anyone have any suggestions on how to determine the value? There is a list of value under column A 36,36,42,42,42,48,48,48,47,47,25,25,25,25 I would like to determine the largest value under the lists, which is 48 in cell B1 I would like to determine the second largest value under the lists, which is not equal to 48. It should return 47 in cell B2 I would like to determine the thrid largest value under the lists, which is not equal to largest and second largest. It should return 42 in cell B3 Does anyone have any suggestions? Thanks in advance for any suggestions Eric Enter this formula in B1, and fill down as far as required. Vals is a defined name with this formula: =OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A)) (or it could be an absolute reference to the range that contains values) =LARGE(IF(FREQUENCY(Vals,Vals)0,Vals),ROWS($1:1)) So as not to return errors, you could use one of these formulas: Excel 2007: =IFERROR(LARGE(IF(FREQUENCY(Vals,Vals)0,Vals),ROW S($1:1)),"") Prior versions: =IF(ISERR(LARGE(IF(FREQUENCY(Vals,Vals)0,Vals),RO WS($1:1))),"", LARGE(IF(FREQUENCY(Vals,Vals)0,Vals),ROWS($1:1))) --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the value - 18 Dec?
Try these:
Enter this formula in B1: =MAX(A1:A14) Enter this array formula** in B2 and copy down until you get blanks: =IF(OR(B1=MIN(A$1:A$14),B1=""),"",MAX(IF(A$1:A$14< B1,A$1:A$14))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Eric" wrote in message ... Does anyone have any suggestions on how to determine the value? There is a list of value under column A 36,36,42,42,42,48,48,48,47,47,25,25,25,25 I would like to determine the largest value under the lists, which is 48 in cell B1 I would like to determine the second largest value under the lists, which is not equal to 48. It should return 47 in cell B2 I would like to determine the thrid largest value under the lists, which is not equal to largest and second largest. It should return 42 in cell B3 Does anyone have any suggestions? Thanks in advance for any suggestions Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine the value? | Excel Discussion (Misc queries) | |||
How to determine the value? | Excel Discussion (Misc queries) | |||
How to determine the value? | Excel Discussion (Misc queries) | |||
How to determine the value? | Excel Discussion (Misc queries) | |||
How to Determine 1st, 2nd & 3rd for a PWD | Excel Discussion (Misc queries) |