sjcwhittle1
New Member
- Joined
- Oct 3, 2006
- Messages
- 7
I am trying to find a method for determining the lowest & highest three values in a set of data whilst ignoring blank cells.
Column A will be text and columns C,E,F and G will be numbers (these numbers will be unique for the most part)
I need to be able to identify the lowest and then highest three by the identifer in Column A
The only formula I have found is to identify the lowest:
But this does not work if I am looking in mutiple rows.
=INDEX(A2:A42,MATCH(LARGE(IF(A2:A42<>0,B2:B42,0),1),B2:B42,0))
(Change the 1 to a 2 to get the second smallest number)
Does anyone have an idea?
Column A will be text and columns C,E,F and G will be numbers (these numbers will be unique for the most part)
I need to be able to identify the lowest and then highest three by the identifer in Column A
The only formula I have found is to identify the lowest:
But this does not work if I am looking in mutiple rows.
=INDEX(A2:A42,MATCH(LARGE(IF(A2:A42<>0,B2:B42,0),1),B2:B42,0))
(Change the 1 to a 2 to get the second smallest number)
Does anyone have an idea?