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

It's possible to get Excel to "Rank" numbers uniquely, so two numbers the same will rank differently. This may be what you need to do.
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.

cheers spencer. I only have Open office on the home comp and don't think I can upload from this work comp. Nothing confidential in the files just some team stats and plenty of pretty charts .

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

I've sent you a PM with an email address in case that helps you send an example file to me.

We will come back to the forum with the results in order to help anyone else out with this issue in the future.

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