Rank Question

[Kevin Neufeld]

In column D I am needing a formula to give the rank of Column C as it relates to Column B. The Rank formula will reset when the Lane ID # switches.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Lane ID #[/TD]
[TD]Base Rate ($)[/TD]
[TD]Quote ($)[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10
[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]35[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ajamess]

I'm not sure what you mean of "as it relates to column B"

If you mean the rank of just the same Lane ID# in d2 type the formula:

= rank(c2, offset($c$1, match(a2,a:a,0)-1,0,countif(a:a,a2),1)

 

[Kevin Neufeld]

"as it relates to column b" means that column b is the base rate for each lane ID #. the quotes in column see I want to rank against each lane id's base rate. will this formula work for that?

 

[ajamess]

Do you mean you want to rank how close the quotes are to the Base Rate?

If so, how close they are absolute difference? (doesn't matter on which side, postive or negative)

This is my understanding, then to do this I would create a helper column (you can hide it after if needed), say column E, so in e2 type "=abs(c2-b2)" and fill down.

Then in d2 type "= rank(e2, offset($e$1, match(a2,a:a,0)-1,0,countif(a:a,a2),1)"

 

[Kevin Neufeld]

thank you

 

[ajamess]

Glad it worked! Sorry for the confusion on me understanding what you wanted to do at first!