E

#### exceluser

function with positive and negative numbers "WITHOUT" using an array

entered formula ?

I'm trying to rank each product (most, second most, etc.) by weight.

Data worksheet

A B C

1 Product Type Pounds

2 Orange Fruit -200

3 Tomato Vegetable 500

4 Apple Fruit 700

5 Potato Vegetable 1,000

Using the LARGE function, the goal is to get the following result on

another worksheet:

Order worksheet

A B C

1 Product 1 2

2 Fruit Apple Orange

3 Vegetable Potato Tomato

Currently, I'm using the following forumula:

=IFERROR(INDEX(Data!$A$1:$C$5,MATCH(LARGE(INDEX((Data!$B$2:$B$5=

$A2)*Data!$C$2:$C$5,),B$2),Data!$C$1:$C$5,0)),"")

NOTE:

Unfortunately, this formula only works if the sorted numbers are

positive due to the INDEX function's array being populated with 0's

from the FALSE values.

{TRUE,FALSE,TRUE,FALSE} * {-200,500,700,1000} = {-200,0,700,0}

The problem is that the LARGE function doesn't ignore the 0's created

from the FALSE values in the INDEX array.

As a result, TRUE values in the INDEX array that are negative or zero

may not get properly included in the sorted output.

Basically, I want the INDEX function array output to be {-200,,700,}

which will sort properly.

I do not want to use an array entered formula as the number of cells

(15,000+) that would contain the formula would cause approximately 90

minutes of calculation every time related data is changed.

Any ideas as to how to sort the data with positive and negative

numbers and then use the LARGE function to rank the results ?