# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  RANK Function : excluding subtotals from the Range

## johnny13

Hey Guys,

This is my first post on the forum, after spending all day on here I am excited to learn from the community. 

I need to build a simple Rank function on Excel. The only issue is that I cannot get the function to work properly because there are subtotals amongst the range, and using Excel on Mac, I can't figure out how to get to make it work. 

It is the exact same problem that was posted in another thread, but I couldn't get the solution to work:

http://www.excelforum.com/excel-form...a-to-rank.html

Carrots	34
Celery	11
Peppers	28
Onions	15
Garlic	        20
Tomatoes	11
Lettuce	11

	        130

Chicken	62
Beef	        20
Shrimp	5

	        87

In Windows, they talk about the use of 'RankRng' function to solve this problem, but I don't have this formula on my Mac.

Any ideas?!

Thanx

----------


## dilipandey

HI Johnny,

welcome to the forum.
I don't have any idea of Mac, but if you know that rankrng function can help you, then I believe that should / or some equivalent function in mac, should work for you.  




Regards,
DILIPandey
<click on below *** if this helps>

----------


## JosephP

rankrng is not an excel function-it must be provided by an add-in or through vba in a workbook. where was it mentioned?

----------


## johnny13

Thanks for the responses guys.

Dilipandey, I would love to be able to have the equivalent of RankRng on the Mac but I have not been able to find anything.

JosephP, thanks for telling me this, I was wondering why I couldn't see the formula on either the Mac or PC Excel (I have access to the PC version as well, but I work on a Mac primarily).  I found this solution on another forum where someone posed the same question:

http://www.justanswer.com/computer-p...-5-bottom.html

It was also mentioned in a more recent post that goes into details about the Rank function:

http://www.tushar-mehta.com/excel/newsgroups/ranking/

If this needs to be installed as an add-in or through a vba do you know how I would get this (for either Mac or PC)?

----------


## etaf

Assuming the list of items in Column B and the numbers in C 

This will rank all items except a blank in cell B for thesub total- assuming the sub total is blank
=IF(B2="","",SUMPRODUCT(1-($B$2:$B$17=""),--(C2 < $C$2:$C$17))+1)

So what is actually in column for the subtotal ?

----------


## JosephP

the tushar mehta link shows that rankrng is actually a named range and demonstrates how to define it ;-)

----------


## johnny13

Sorry for my delayed response, I didn't receive an email when new answers were added and am just returning to this now.

etaf, your solution worked!!! Unbelievable thank you ...

=IF(B2="","",SUMPRODUCT(1-($B$2:$B$17=""),--(C2 < $C$2:$C$17))+1)

JosephP, I was still unable to get the formula to work properly after defining the range, thanks for helping though.

Do I need to add a 'SOLVED' to the forum post now?

----------


## johnny13

One quick addendum before this case is closed.

This solution worked perfectly for the given scenario. If, however, I change the numbers in Column C to percentages (to rank growth rates), the formula doesn't work. Any quick ideas why this would be?

----------

