CODEWITHSUNDEEP
excelvbaexcel-formula
SantaSecrets
SantaSecrets

Reputation: 37

Can I use Rank with a non-continuous reference?

The rank formula needs the following variables: number, ref, and [order]

Does the ref have to be continuous?

Example: My ref is in cells A1-C1, however I have another number in another workbook that I want to add to that ref. I have a formula right now.

Here's the background:
We test for various chemicals and depending on the ppm value we take no action, note but no action, minor action, and major action.

Example:
- less than 3ppm then no action
- less than 6 but more than 3 no action but take notes
- less than 10 but more than 6 is minor action etc.

I need to know where my value lies in these tiers. The values in the tiers vary from .0001 to 100000.

Formulas are preferred, VBA will work too but no one else knows VBA so I think a formula will be easier for them and me to troubleshoot

Upvotes: 1

Views: 209

Answers (1)

Rajesh Sinha
Rajesh Sinha

Reputation: 197

Yes it is possible to Rank records which are not in single sheet. Through this example I would like to show how to achieve it. Suppose you have 3 worksheet. First you need to Name them like, ={"Class1!$E$2:$E$5","Class2!$B$2:$B$5","Class3!$B$8:$B$11"} and then after use this formula, =IF(SUMPRODUCT(COUNTIF(INDIRECT(Score),E2))=0,"N/A",SUMPRODUCT(COUNTIF(INDIRECT(Score),">"&E2))+1).

NB : Score is the name given to the Data Ranges above.

Upvotes: 1

Related Questions