Home |
Search |
Today's Posts |
#1
|
|||
|
|||
using an IF or AND function to get next largest value in list
Hi .
I have a list of numbers and a corresponding list of text values Team Errors Query Category 7 Query sub cat 8 MSN 1 Exchange date 17 Meter Type 1 Reads 0 Flows 0 MAP ID 3 SSC 3 Mtr type 2 Ecoes date 8 I have been using the below formula to get the top three values and return the corresponding text string in a cell on another part of the worksheet. =INDEX(W8:X18,MATCH(LARGE(X8:X18,2),X8:X18,0),1) But as you can see the 2nd largest value has two results. How do I get excel to print one of them and the next time to test wether that string has already been printed and go for the 'other' joint 2nd largest number ? When I change the LARGE array argument from 2nd largest to 3rd largest number it returns the same string i.e 'Query sub cat' - how can I get it to test wether that string has already been printed and print the string 'Ecoes date' ? I assume I want to add an AND operator into the above formula something like AND<= the text in cell F30. So look at cell F30 and see what text string is there . If it matches the result from the INDEX formula then print the other string instance of the number / value thanks for any and all help |
#2
|
|||
|
|||
Quote:
Simple to do if you're able to use a "helper column". Might be possible to combine into one formula but would need to see an example workbook (with no confidential information) to be certain of that. If you're able to post one, either I or one of the other clever people on here will be happy to help. S. |
#3
|
|||
|
|||
Quote:
I am happy to use a helper column if you can explain how to uniquely rank them. I'll have to upload them from home but should be able to save as xls format. I'm using version 2003 in work by the way cheers, Dj |
#4
|
|||
|
|||
Quote:
We will come back to the forum with the results in order to help anyone else out with this issue in the future. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using an IF or AND function to get next largest value in list
Hi,
Am Mon, 11 Jun 2012 14:13:11 +0000 schrieb djferrick: Team Errors Query Category 7 Query sub cat 8 MSN 1 Exchange date 17 Meter Type 1 Reads 0 Flows 0 MAP ID 3 SSC 3 Mtr type 2 Ecoes date 8 But as you can see the 2nd largest value has two results. How do I get excel to print one of them and the next time to test wether that string has already been printed and go for the 'other' joint 2nd largest number ? try: =INDEX($W$8:$W$18,MATCH(LARGE(X$8:X$18-ROW($1:$11)/9^9,ROW(A1)),X$8:X$18-ROW($1:$11)/9^9,0)) and enter the formula with CTRL+Shift+Enter Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
An eqation to add the largest 6 (or N) numbers from a list | Excel Discussion (Misc queries) | |||
Finding largest number from list | Excel Discussion (Misc queries) | |||
Highlighting the 5 Largest Numbers in a list | Excel Discussion (Misc queries) | |||
Finding the 3rd largest number in a list | Excel Worksheet Functions | |||
finding the second largest number in a list | Excel Discussion (Misc queries) |