Counting the number of rows between identical data.

[Art MacNeil]

Hello,

I have a spreadsheet that lists the number of F1 races and the drivers who
won those races.

I'm trying to count the races that elapsed between wins.

Column B contains the race #: (from 1 through 754)

Column L has the race winners.

In Column M, I want to count how many races it's been since a driver last
won a race.

For example: Giancarlo Fisichella won his 2nd race at the 2005 Australian
G. P. (Race # 732)

He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had
elapsed.

Is there a formula I can use in Column M that will display 20?

Thank you for your help,

Art.

P.S. - I tried using "Match", but that only counts the number of races
between the 1st win and the most recent. This works when the most recent win
was the 2nd career win, but it won't work when a driver has had more than 2
wins.

 

[Biff]

Hi!

Try this: (I guess you want a progressive total):

Joe 1
xx
xx
xx
Joe 4
xx
xx
Joe 3

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))

Copy down as needed.

Biff

 

[Art MacNeil]

Hi!

Try this: (I guess you want a progressive total):

Joe 1
xx
xx
xx
Joe 4
xx
xx
Joe 3

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(COUNTIF(L$1:L1,L1)=1,1,LARGE(IF(L$1:L1=L1,B$1:B1),1)-LARGE(IF(L$1:L1=L1,B$1:B1),2))

Copy down as needed.

Biff

Thanks Biff.

I tried it and it seems like it should work but it didn't. When I dragged
the formula down, every row displayed a 1 as a result. I changed your "L1"
and "B1" references to "L2" and "B2" because my 1st row is used for column
titles. The weird thing is, when I look at the function, the TRUE answer is
displayed as 1, and the FALSE answer is correct, but is doesn't appear in
the cell, a "1" does.. So the formula works, but it always displays a 1 when
the FALSE condition applies. Any ideas?

Thank you,

Art.

 

[Biff]

The formula is an array formula. For it to work properly you MUST enter it
using the key combination of CTRL,SHIFT,ENTER.

Select the first formula cell in column M.

Double click that cell to be in Edit mode.
Hold down both the CTRL key and the SHIFT key then hit ENTER.
When done properly Excel will enclose the formula in squiggly braces { }.
You cannot just type the braces in, you MUST use the key combo. Also, if you
edit an array formula it MUST be re-entered as an array using the key combo.

Then copy the array formula down as needed.

The first result has to be 1!

Biff

 

[Domenic]

Here's a slight variation of Biff's solution...

First, define the following name...

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

M2, copied down:

=LOOKUP(BigNum,CHOOSE({1,2},0,SUM(LARGE(IF($L$2:L2=L2,$B$2:B2),{1,2})*{1,
-1})))

Hope this helps!

 

[Art MacNeil]

Right you are. It works now, thank you.

The formula is an array formula. For it to work properly you MUST enter it
using the key combination of CTRL,SHIFT,ENTER.

Select the first formula cell in column M.

Double click that cell to be in Edit mode.
Hold down both the CTRL key and the SHIFT key then hit ENTER.
When done properly Excel will enclose the formula in squiggly braces { }.
You cannot just type the braces in, you MUST use the key combo. Also, if
you edit an array formula it MUST be re-entered as an array using the key
combo.

Then copy the array formula down as needed.

The first result has to be 1!

Biff

 

[Art MacNeil]

That is an interesting idea. I may not use it for this problem but
hopefully I can use it to solve another one. Thank you for your time.

 

[Biff]

You're welcome!

Biff

Right you are. It works now, thank you.