Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rank for range with two criterion - hard
Hi,
I'd like show my problem with rank by two criterion in table below: Name Feature Data Rank_of_Name_and_Feature A 1 2.5 3 A 1 3.5 2 A 0 6.3 2 A 1 6.5 1 A 0 6.8 1 A 1 1.2 4 etc... How create rank (diminishing count Data) for the same Name (col1) and Feature (col2)? My data can't be sorted!! Any help in VBA or function (excel 2k) will be appreciated Regards Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rank for range with two criterion - hard
Hi
try: =SUMPRODUCT(--($B$2:$B$7=B2),--($C$2:$C$7=C2)) Note: may not give you the expected results if you have ties in your data "Mark" wrote: Hi, I'd like show my problem with rank by two criterion in table below: Name Feature Data Rank_of_Name_and_Feature A 1 2.5 3 A 1 3.5 2 A 0 6.3 2 A 1 6.5 1 A 0 6.8 1 A 1 1.2 4 etc... How create rank (diminishing count Data) for the same Name (col1) and Feature (col2)? My data can't be sorted!! Any help in VBA or function (excel 2k) will be appreciated Regards Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rank for range with two criterion - hard
Hi Frank,
What target do you want achieve by function sumproduct? I'd like calculate position in column Rank_of_Name_and_Feature count column Data for e.g. Name A and Feature d and separately for Name A and Feature u. I a bit changed my table.: Name Feature Data Rank_of_Name_and_Feature A d 2.5 3 (what function ?) A d 3.5 2 (what function ?) A u 6.3 2 (what function ?) A d 6.5 1 (what function ?) A u 6.8 1 (what function ?) A d 1.2 4 (what function ?) etc... Function RANK operate only for range, not for range take into account Name and Feature. Is there any solution for VBA or function? Best Regards Mark -----Original Message----- Hi try: =SUMPRODUCT(--($B$2:$B$7=B2),--($C$2:$C$7=C2)) Note: may not give you the expected results if you have ties in your data "Mark" wrote: Hi, I'd like show my problem with rank by two criterion in table below: Name Feature Data Rank_of_Name_and_Feature A 1 2.5 3 A 1 3.5 2 A 0 6.3 2 A 1 6.5 1 A 0 6.8 1 A 1 1.2 4 etc... How create rank (diminishing count Data) for the same Name (col1) and Feature (col2)? My data can't be sorted!! Any help in VBA or function (excel 2k) will be appreciated Regards Mark . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rank for range with two criterion - hard
It's not a perfect solution as it involves hard-coding a maximum value for
your data, but assuming the below are headerless columns A to C, the answer in column D could be {=MATCH(C1,SMALL(IF(A$1:A$6=A1,IF(B$1:B$6=B1,C$1:C $6,99)),ROW(A$1:A$6)),FALSE)} This basically sorts the numbers manually, after stripping out any that weren't matching the Name or Feature of that particular row. The ROW() part is just to get a (1,2,3...} array that we can pass to SMALL() to get a sorted list. You need a separate entry of the array formula for each line. It's a little on the calculation-intensive side, but at least doesn't involve any VBA. Hope that helps, Chris "Mark" wrote in message ... Hi Frank, What target do you want achieve by function sumproduct? I'd like calculate position in column Rank_of_Name_and_Feature count column Data for e.g. Name A and Feature d and separately for Name A and Feature u. I a bit changed my table.: Name Feature Data Rank_of_Name_and_Feature A d 2.5 3 (what function ?) A d 3.5 2 (what function ?) A u 6.3 2 (what function ?) A d 6.5 1 (what function ?) A u 6.8 1 (what function ?) A d 1.2 4 (what function ?) etc... Function RANK operate only for range, not for range take into account Name and Feature. Is there any solution for VBA or function? Best Regards Mark -----Original Message----- Hi try: =SUMPRODUCT(--($B$2:$B$7=B2),--($C$2:$C$7=C2)) Note: may not give you the expected results if you have ties in your data "Mark" wrote: Hi, I'd like show my problem with rank by two criterion in table below: Name Feature Data Rank_of_Name_and_Feature A 1 2.5 3 A 1 3.5 2 A 0 6.3 2 A 1 6.5 1 A 0 6.8 1 A 1 1.2 4 etc... How create rank (diminishing count Data) for the same Name (col1) and Feature (col2)? My data can't be sorted!! Any help in VBA or function (excel 2k) will be appreciated Regards Mark . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hlp! Named range Advanced Filter Criterion | Excel Discussion (Misc queries) | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
Select value from a range given a rank | Excel Discussion (Misc queries) | |||
Sum a range based on a criterion | Excel Worksheet Functions | |||
Multiple criterion including wildcards to sum in a range | Excel Worksheet Functions |