RANK Function : excluding subtotals from the Range

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

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)?

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 ?

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?

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?