Tricky array formula

[Carl Lindmark]

Hello,
When recording the results from my practice (cross country running), I have:
Column A: The date when the course was run.
Column B: The name of the course I ran (there are about 10 different courses
that I usually run).
Column C: The time it took to run the course in one column.

Example:
1 Feb 2005; Short Forrest Course; 35:20
4 Feb 2005; Long Hill Course; 42:15
7 Feb 2005; Short Hill Course; 37:40
9 Feb 2005; Long Hill Course; 41:45

Now, I am trying to add a new column, column D, where I rank the results.
That is, I want to rank the various results I have for the respective
courses. For the example list above this would mean:
1 Feb 2005; Short Forrest Course; 35:20; 1;
4 Feb 2005; Long Hill Course; 42:15; 2;
7 Feb 2005; Short Hill Course; 37:40; 1;
9 Feb 2005; Long Hill Course; 41:45; 1;

I am pretty sure one would have to use an array formula, but I have not been
able to get it right...

I know this formula does not work, but maybe the solution is something along
these lines:
{=RANK(C2,IF($B$2:$B$1000=B2,$C$2:$C$1000,""))}
?

Any help would be greatly appreciated!

Sincerely,
Carl Lindmark

 

[Harlan Grove]

Carl Lindmark wrote...
....

Example:
1 Feb 2005; Short Forrest Course; 35:20
4 Feb 2005; Long Hill Course; 42:15
7 Feb 2005; Short Hill Course; 37:40
9 Feb 2005; Long Hill Course; 41:45

Now, I am trying to add a new column, column D, where I rank the results.
That is, I want to rank the various results I have for the respective
courses. For the example list above this would mean:

1 Feb 2005; Short Forrest Course; 35:20; 1;
4 Feb 2005; Long Hill Course; 42:15; 2;
7 Feb 2005; Short Hill Course; 37:40; 1;
9 Feb 2005; Long Hill Course; 41:45; 1;

....

If the original records begin in cell A2 (assuming column headings in
row 1), then enter the following formula in D2.

D2:
=SUMPRODUCT(--(B$2:B$5=B2),--(C$2:C$5<C2))+1

Fill D2 down into D35.

 

[Carl Lindmark]

Carl Lindmark wrote...
...
...

If the original records begin in cell A2 (assuming column headings in
row 1), then enter the following formula in D2.

D2:
=SUMPRODUCT(--(B$2:B$5=B2),--(C$2:C$5<C2))+1

Fill D2 down into D35.

Aaah! Thank you very much, Harlan!
That was certainly a different approach than what I had thought of! I am so
glad you shared that solution with me, because after looking for days I can
say with great certainty that I would NOT have thought of that myself!

I will have to look into that technique tomorrow to make sure that I fully
understand it, but for now I am just thrilled to see that it works!

Sincerely,
Carl

 

[Bob Phillips]

Carl,

Format suggestion.

As it may get difficult top pick them all out when you have many (lots of 1s
and 2s etc.), put the names of the courses in row, that is D1, E1, F1, etc.
and then use this modification to Harlan's formula, starting in D2, and copy
down and across

=IF($B2<>D$1,"",SUMPRODUCT(--($B$2:$B$100=D$1),--($C$2:$C$100<$C2))+1)

This way, you will get the ranks only applying to that course.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Lynn Kurtz]

If the original records begin in cell A2 (assuming column headings in
row 1), then enter the following formula in D2.

D2:
=SUMPRODUCT(--(B$2:B$5=B2),--(C$2:C$5<C2))+1

Fill D2 down into D35.

Hello, I just happened upon this while browsing the newsgroup. I pick
up all sorts of tidbits I didn't know. I am trying to figure out what
the -- symbols do in that formula. Where do I look to read about it?

--Lynn

 

[Harlan Grove]

Hello, I just happened upon this while browsing the newsgroup. I pick
up all sorts of tidbits I didn't know. I am trying to figure out what
the -- symbols do in that formula. Where do I look to read about it?

Not in the documentation Microsoft provides for Excel. The Google Groups
archive has several articles explaining what -- does.

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

--() is the dual application of the unary negation operator. It
coerces the boolean values inside the parenthesis into numbers. Some
folks prefer it to the alternatives even knowing the cost is hours of
if not perpetual confusion amongst people.

The same could be accomplished with 0+ or N() as in SUMPRODUCT(N(),N())
or SUMPRODUCT(0+(),0+()) or even the array formula SUM(()*())

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Lynn Kurtz]

Hello, I just happened upon this while browsing the newsgroup. I pick
up all sorts of tidbits I didn't know. I am trying to figure out what
the -- symbols do in that formula. Where do I look to read about it?

--Lynn

Thanks to all for the replies. I learn something every time I visit
this newsgroup.

--Lynn

 

[Harlan Grove]

--() is the dual application of the unary negation operator. It
coerces the boolean values inside the parenthesis into numbers. Some
folks prefer it to the alternatives even knowing the cost is hours of
if not perpetual confusion amongst people.

Confusion only on the order of the NPV function calculating 'present' values
at the end rather than beginning of the first period (though that's due to
compatibility with 123 for a Lotus-originated screw-up).

The same could be accomplished with 0+ or N() as in SUMPRODUCT(N(),N())
or SUMPRODUCT(0+(),0+()) or even the array formula SUM(()*())

Adding zero or multiplying by one would be equivalents at all times. Also
better to use the nonarray SUMPRODUCT(()*()) than array SUM(()*()) since the
former does recalc faster. Finally, just try using N() on arguments that are
simple range references.

Of all these alternatives, N() is unambiguously the worst because it wastes
a nested function call level and has the temerity to work as documented when
fed simple range arguments.

 

[Bob Phillips]

--() is the dual application of the unary negation operator. It
coerces the boolean values inside the parenthesis into numbers. Some
folks prefer it to the alternatives even knowing the cost is hours of
if not perpetual confusion amongst people.

The same could be accomplished with 0+ or N() as in SUMPRODUCT(N(),N())
or SUMPRODUCT(0+(),0+()) or even the array formula SUM(()*())

Which of course is so much more obvious

 

[Bob Phillips]

On Tue, 29 Mar 2005 17:41:10 GMT, Lynn Kurtz

Thanks to all for the replies. I learn something every time I visit
this newsgroup.

Like how to light the touch paper, stand back, and watch the fireworks

 

[Harlan Grove]

Like how to light the touch paper, stand back, and watch the fireworks

It's the minor stuff that leads to, er, animated discussion.

 

[Tushar Mehta]

Which of course is so much more obvious

You should be able to answer that question yourself.

How many people have asked for an explanation of 0+?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Harlan Grove]

How many people have asked for an explanation of 0+?

None ask what it does, but back when 0+ or 1* were more commonly used some
(hard to tell whether more or less than now ask about --) asked why it was
needed. Answer: because SUMPRODUCT skips boolean values rather than treating
TRUE as 1 and FALSE as 0, which (just for fun 'cause it obviously ain't for
consistency) Excel does do in, e.g., AVERAGEA(C1:C4) where C1:C4 evaluates
to {TRUE;FALSE;TRUE;TRUE} and this AVERAGEA call returns 0.75 (though,
adding to the fun, AVERAGEA({TRUE;FALSE;TRUE;TRUE}) returns #DIV/0!).

If one must use Excel, one must get used to so much semantic inconsistency,
incomplete and occasionally inaccurate documentation, and syntactic oddities
(like unary - having higher operator precedence than exponentiation) that --
is rather a minor matter.

 

[Bob Phillips]

You should be able to answer that question yourself.

How many people have asked for an explanation of 0+?

Very few, because it is never used, we only see * or --.

Questions get asked about -- because it is not familiar (as * is), but I bet
the majority of people who receive solutions here that utilise * in
SUMPRODUCT or SUM(IF have no more idea about what is happening or why than
the people who ask about --.

How many people ask about complex array formulae, and how they work? Do you
really suppose that that means that they all understand the solutions?

 

[Bob Phillips]

It's the minor stuff that leads to, er, animated discussion.

You're not wrong there

 

[Carl Lindmark]

Carl,

Format suggestion.

As it may get difficult top pick them all out when you have many (lots of 1s
and 2s etc.), put the names of the courses in row, that is D1, E1, F1, etc.
and then use this modification to Harlan's formula, starting in D2, and copy
down and across

=IF($B2<>D$1,"",SUMPRODUCT(--($B$2:$B$100=D$1),--($C$2:$C$100<$C2))+1)

This way, you will get the ranks only applying to that course.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Thank you very much for the format suggestion, Bob! However, I have thought
of another way of displaying the results by course that I think would be the
best. I have not been able to do it myself, but maybe someone will be able
to help me?

I already have two out of three sheets just the way I want them:
Sheet 1:
A dynamic range with the names of the different courses
(Currently, I have no automatic sorting on the names, but I try to enter
them in alphabetical order in the range)

Sheet 2:
The results sorted by date like so:
1 Feb 2005; Short Forrest Course; 35:20; 1 / 1;
4 Feb 2005; Long Hill Course; 42:15; 2 / 2;
7 Feb 2005; Short Hill Course; 37:40; 1 / 1;
9 Feb 2005; Long Hill Course; 41:45; 1 / 2;
(As you see, I chose to display the rank as "1 / 2" if the result in
question was the best result out of two runs for a particular course)

Now, for the last sheet, I would like to display the data on Sheet 2 in a
different way:
Sheet 3:
Long Hill Course:
1 / 2; 41:45; 9 Feb 2005
2 / 2; 42:15; 4 Feb 2005

Short Forrest Course
1 / 1; 35:20; 1 Feb 2005

Short Hill Course
1 / 1; 37:40; 7 Feb 2005

That is, I would like the results listed by course and by rank.
Is this possible to achieve? It seems almost impossible to list the results
this way (with the different courses and their respective results in the
same column(s)) and have it be dynamic for when new results are entered into
Sheet 2.

*Perhaps one would have to move the name of the course out to a separate
column (maybe the name has to be included on all rows and not just as a
header for the results for that particular course)? Or perhaps all courses
and their respective results would need to be in separate columns?*

If it were possible, though, presenting the data on "Sheet 3" according to
the layout above would be amazing!

Any help would be greatly appreciated!

Sincerely,
Carl Lindmark

 

[Carl Lindmark]

of


Thank you very much for the format suggestion, Bob! However, I have thought
of another way of displaying the results by course that I think would be the
best. I have not been able to do it myself, but maybe someone will be able
to help me?

I already have two out of three sheets just the way I want them:
Sheet 1:
A dynamic range with the names of the different courses
(Currently, I have no automatic sorting on the names, but I try to enter
them in alphabetical order in the range)

Sheet 2:
The results sorted by date like so:
1 Feb 2005; Short Forrest Course; 35:20; 1 / 1;
4 Feb 2005; Long Hill Course; 42:15; 2 / 2;
7 Feb 2005; Short Hill Course; 37:40; 1 / 1;
9 Feb 2005; Long Hill Course; 41:45; 1 / 2;
(As you see, I chose to display the rank as "1 / 2" if the result in
question was the best result out of two runs for a particular course)

Now, for the last sheet, I would like to display the data on Sheet 2 in a
different way:
Sheet 3:
Long Hill Course:
1 / 2; 41:45; 9 Feb 2005
2 / 2; 42:15; 4 Feb 2005

Short Forrest Course
1 / 1; 35:20; 1 Feb 2005

Short Hill Course
1 / 1; 37:40; 7 Feb 2005

That is, I would like the results listed by course and by rank.
Is this possible to achieve? It seems almost impossible to list the results
this way (with the different courses and their respective results in the
same column(s)) and have it be dynamic for when new results are entered into
Sheet 2.

*Perhaps one would have to move the name of the course out to a separate
column (maybe the name has to be included on all rows and not just as a
header for the results for that particular course)? Or perhaps all courses
and their respective results would need to be in separate columns?*

If it were possible, though, presenting the data on "Sheet 3" according to
the layout above would be amazing!

Any help would be greatly appreciated!

Sincerely,
Carl Lindmark

Actually, the rank could be displayed by a simple number for Sheet 3, like
so:
Long Hill Course:
1; 41:45; 9 Feb 2005
2; 42:15; 4 Feb 2005
3; 44:10; 2 Mar 2005

Short Forrest Course
1; 35:20; 1 Feb 2005

Short Hill Course
1; 37:40; 7 Feb 2005
2; 38:10; 6 Mar 2005

*hmmm... this was tricky...*

/Carl

 

[Bob Phillips]

Carl,

This is probably easiest and best with a macro, which you run on demand.
Here is a macro that does it

Sub RunTimeData()
Dim iLastRow As Long
Dim iRow As Long
Dim i As Long, j As Long
Dim iStartRow As Long
Dim oWs2 As Worksheet
Dim oWs3 As Worksheet

Set oWs2 = Worksheets("Sheet2")
Set oWs3 = Worksheets("Sheet3")
oWs3.Cells.ClearContents

With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
iRow = iRow + 1
oWs3.Cells(iRow, "A").Value = .Cells(i, "A").Value
iStartRow = iRow + 1
For j = 1 To oWs2.Cells(Rows.Count, "A").End(xlUp).Row
If oWs2.Cells(j, "B").Value = .Cells(i, "A").Value Then
iRow = iRow + 1
With oWs3.Cells(iRow, "A")
.NumberFormat = "@"
.Value = oWs2.Cells(j, "D").Value
End With
With oWs3.Cells(iRow, "B")
.NumberFormat = "mm:ss"
.Value = oWs2.Cells(j, "C").Value
End With
With oWs3.Cells(iRow, "C")
.NumberFormat = "d mmm yyyy"
.Value = oWs2.Cells(j, "A").Value
End With
End If
Next j
If iStartRow < j Then
oWs3.Range("A" & iStartRow & ":A" & j).Sort _
key1:=oWs3.Range("A" & iStartRow), _
header:=xlNo
End If
iRow = iRow + 1
Next i
End With

oWs3.Activate

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)