Rank lowest to highest with duplicate values

I have a spreadsheet of 10 employees (column A) with a data validation list of products (column b). I created two different formulas in columns C & D to extract all of the digits from the product names in column A in attempt to setup a rank formula from lowest to highest $ amount. The problem is employees can have two different products or the same, but the formulas view them the same (ie $500-$20 & $500-$40 - formula views them as 500). I am searching for a rank formula solution that can differentiate products that have the same dollar amounts.

Please Login or Register  to view this content.

Desired Result:

Please Login or Register  to view this content.

Does this do what you want?

Formula:

Please Login or Register  to view this content.

Try

Formula:

Please Login or Register  to view this content.

Would you include the expected results also and an explanation of the logic behind the "why" those are the results.

Additionally ... as a courtesy ... please don't edit posts after there have already been replies. It 'messes' with the thought flow of the thread and becomes difficult to know how responses relate to the thread.

C is ahelper column
In C2 then drag down.

=MID($B2,FIND("$",$B2)+1,FIND("-",$B2)-FIND("$",$B2)-1)+SUBSTITUTE(RIGHT($B2,LEN($B2)-FIND("-",$B2)),"$","")*10^-3

In F2 then drag down.

=IF(C2="","",SUMPRODUCT(($C$2:$C$9<C2)/COUNTIF($C$2:$C$9,$C$2:$C$9))+1)

Originally Posted by Stuepef

I edited my OP to hopefully provide more detail of the issues I am running into with ranking the products.

Please don't do this again, as mentioned in post #6: instead, add extra or changed detail to a new post in the thread. There are about 15 edits to the opening post!!!

Or in your attachment just change formula.

D2

Formula:

Please Login or Register  to view this content.