Rank Question

[Kevin Neufeld]

Hello,
I have a data set similar to below. Each lane ID has a base price that I am paying now. I have had suppliers return bids and I have ranked them compared to the base. Column D has the formula =countifs(a:a,a2,C:C,"<"&c2)+1 this allows the formula to be filled down column D but when the lane id changes the ranking resets and only compares by the lane id.

However the issue is when suppliers have the same rate the ranking is skewed. For example I would like Lane ID #1 rankings to show up like this: 1,2,3,4,5. There are 7 rates returned but due to same rates, I should only have 5 rankings. I am missing rankings with my formula.

Thanks

	A	B	C	D
1	Lane ID	Supplier	Rate	Rank
2	1	Base Price	846	1
3	1	A	990	2
4	1	B	990	2
5	1	C	1000	4
6	1	D	1200	5
7	1	E	1200	5
8	1	F	1300	7
9	2	A	500	1
10	2	B	500	1
11	2	C	750	3
12	2	BASE PRICE	1000	4
13	2	D	1000	4
14	2	E	1200	6
15	3	A	200	1
16	3	B	250	2
17	3	BASE PRICE	500	3
18	3	D	500	3
19	3	E	750	5
20	3	F	750	5

<tbody>
</tbody>

 

[DRSteele]

I think I understood correctly. Try this. I included a second way to calculate a 'Rank with Conditions' (using function SUMPRODUCT) in case you need to mess round with arrays, which function COUNTIFS doesn't chew very well.

The column 'Duplicate Continuous Rank' is what I think you're after. [Source: http://www.tushar-mehta.com/excel/newsgroups/ranking/index.html]

Copy D2 to F2 down as needed.

	A	B	C	D	E	F
1	Lane ID	Supplier	Rate	Rank1	Rank2	Duplicate Continuous Rank
2	1	Base Price	846	1	1	1
3	1	A	990	2	2	2
4	1	B	990	2	2	2
5	1	C	1000	4	4	3
6	1	D	1200	5	5	4
7	1	E	1200	5	5	4
8	1	F	1300	7	7	5
9	2	A	500	1	1	1
10	2	B	500	1	1	1
11	2	C	750	3	3	2
12	2	BASE PRICE	1000	4	4	3
13	2	D	1000	4	4	3
14	2	E	1200	6	6	4
15	3	A	200	1	1	1
16	3	B	250	2	2	2
17	3	BASE PRICE	500	3	3	3
18	3	D	500	3	3	3
19	3	E	750	5	5	4
20	3	F	750	5	5	4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet38​

Worksheet Formulas Cell Formula D2 =COUNTIFS(A:A,A2,C:C,"<"&C2)+1 E2 =SUMPRODUCT(--($A$2:$A$20=A2),--(C2>$C$2:$C$20))+1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F2 {=SUM(IFERROR(1/COUNTIFS($A$2:$A$20,A2,$D$2:$D$20,IF(($D$2:$D$20<D2)*($A$2:$A$20=A2),$D$2:$D$20)),0))+1} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

 

[Kevin Neufeld]

I recreated the table the exact same has you have below. In column F from Row 2 to row 8 i get 6 in every cell. from row 9 to 20 i get a 5 in every column .

I think I understood correctly. Try this. I included a second way to calculate a 'Rank with Conditions' (using function SUMPRODUCT) in case you need to mess round with arrays, which function COUNTIFS doesn't chew very well.

The column 'Duplicate Continuous Rank' is what I think you're after. [Source: http://www.tushar-mehta.com/excel/newsgroups/ranking/index.html]

Copy D2 to F2 down as needed.

	A	B	C	D	E	F
1	Lane ID	Supplier	Rate	Rank1	Rank2	Duplicate Continuous Rank
2	1	Base Price	846	1	1	1
3	1	A	990	2	2	2
4	1	B	990	2	2	2
5	1	C	1000	4	4	3
6	1	D	1200	5	5	4
7	1	E	1200	5	5	4
8	1	F	1300	7	7	5
9	2	A	500	1	1	1
10	2	B	500	1	1	1
11	2	C	750	3	3	2
12	2	BASE PRICE	1000	4	4	3
13	2	D	1000	4	4	3
14	2	E	1200	6	6	4
15	3	A	200	1	1	1
16	3	B	250	2	2	2
17	3	BASE PRICE	500	3	3	3
18	3	D	500	3	3	3
19	3	E	750	5	5	4
20	3	F	750	5	5	4

<tbody>
</tbody>

Sheet38​

Worksheet Formulas Cell Formula D2 =COUNTIFS(A:A,A2,C:C,"<"&C2)+1 E2 =SUMPRODUCT(--($A$2:$A$20=A2),--(C2>$C$2:$C$20))+1 <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F2 {=SUM(IFERROR(1/COUNTIFS($A$2:$A$20,A2,$D$2:$D$20,IF(($D$2:$D$20<d2< font="">)*($A$2:$A$20=A2),$D$2:$D$20</d2<>)),0))+1} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

 

[DRSteele]

You must have failed to properly enter the formula. The Array formula requires entry with

Ctrl+Shift+Enter.​