LOOKUP, FREQUENCY etc - what's the best way

[Brian]

(moved from general) I am trying to take the data from the Races sheet and
have certain parts counted, summed, or averaged by track into the Track Stats
sheet. I'm really not sure how to go about starting this as it will need to
look through each line and determine if the results are from that track.
This is too complicated to explain thoroughly so I posted a link to the sheet
is below.

Link: http://www.zshare.net/download/1725482392a76ca7
It contains a couple of macros for determining sheet names etc...

For one explainable example, I am trying to have the Track Stats sheet look
through up to 5000 entries on the Races sheet and count the number of starts
for a given track based on the criteria that the range does not equal
0,"","B", or "DNS". I'm thinking it would be framed similar to this but it
isn't working for the whole range...

=IF(OR(COUNT(Races!$AG$2:$AG$5000)<>0,COUNT(Races!$AG$2:$AG$5000)<>"",COUNT(Races!$AG$2:$AG$5000)<>"B",COUNT(Races!$AG$2:$AG$5000)<>0)),"-",COUNT(Races!$AG$2:$AG$5000)),0)

Atlanta would be a good test case on the Track Stats sheets as it is the
first in the list that should pop up numbers. Any help or direction is
appreciated.

 

[T. Valko]

I'm looking at your file...

It's not real clear what you want to do based on your posted formula:

=IF(OR(COUNT(Races!$AG$2:$AG$5000)<>0,COUNT(Races!$AG$2:$AG$5000)<>"",COUNT(Races!$AG$2:$AG$5000)<>"B",COUNT(Races!$AG$2:$AG$5000)<>0)),"-",COUNT(Races!$AG$2:$AG$5000)),0)

My best guess is that you want to count only cells that are numbers and
greater than 0 but there are no zeros in that range.

So, try this:

=SUMPRODUCT(--(Tracks="Atlanta"),--(ISNUMBER(Finished)))

 

[Brian]

First, thanks for the help......

Yes, I am wanting to calculate, for each individual track, the relevant
stats. Your formula seemed to work pretty good to count the number of starts
at a given track. I copied the formula as

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

to each cell in column E but I am a bit perplexed that when I summed it at
the top of the Track Stats sheet with

=SUM(E$3:E$1002)

.....it added up 492 starts. The problem is that I know there is 517. Even
manually counted them. Not sure if 25 of the track names were entered
incorrectly or with spaces? Is there a way to make it so that you can only
choose a track on the Races sheet (column W) that is included in the list box?

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

=SUMPRODUCT(--(Tracks=$C9),--(ISNUMBER(Finished)))

Is that correct?

 

[T. Valko]

=SUMPRODUCT(--(Tracks=$C3),--(ISNUMBER(Finished)))

That formula is *counting*.

=SUM(E$3:E$1002)

That formula is *summing*.

Those are entirely different formulas and can not be used to compare
results.

Also I used SHFT+CTRL+ENTER as I think it has to be done with arrays?

Not needed in this case.

 

[Brian]

Correct, at the top of the Track Stats sheet I need to SUM to the COUNT.
(E1) is a sum of the individual counts.

 

[Brian]

I think you are misunderstanding what I was doing to quality control the
results. I know COUNT and SUM are two different things. However, I used sum
to add up the COUNTS from you formula, and then compared them to the number I
counted manually. I counted 517 manual, and the SUM of the COUNTS totalled
492. Hence I am trying to figure out where the discrepancy is and if I can
control for it. That was the reason I asked this:

Is there a way to make it so that you can only choose a track on the Races
sheet (column W) that is included in the list box?

 

[T. Valko]

Ok, I see what you mean.

There are probably track names that don't exactly match on the Races sheet
and the Track Stats sheet.. You'd have to make sure the track names on both
(all) sheets match exactly. You have too much data for me to look for the
mismatches. That'll be your job. <g>

--
Biff
Microsoft Excel MVP

Correct, at the top of the Track Stats sheet I need to SUM to the COUNT.
(E1) is a sum of the individual counts.

 

[Brian]

Another question relating to this as I have been trying to learn
SUMPRODUCT.....
For Top 5s I tired =SUMPRODUCT(--(Tracks=$C23),--(Finished<=5)) and that did
not work as the blank cells on the race sheet are all showing 4,394 even
though I was thinking they would show zero. Any ideas on how to formulate
this.

BTW, I found the issue with the Tracks having extra spaces after the name -
it now has 517 starts as it should.

New sheet at:
http://www.zshare.net/download/173758079330c392/

Thanks for all of your help!

 

[T. Valko]

For Top 5s I tired =SUMPRODUCT(--(Tracks=$C23),--(Finished<=5))

Just add another test:

=SUMPRODUCT(--(Tracks=$C23),--(Finished>=1),--(Finished<=5))

 

[Brian]

Ah, I should have been able to figure that one out. This one may be a bit
more difficult. Is it possible to calculate a Median for a Track with
SUMPRODUCT (as in column AC)

 

[T. Valko]

Try this array formula** :

=MEDIAN(IF(Tracks=C3,Finished))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Brian]

Hmmm, I tried this and I get a #NUM! error - do you get the same? BTW, I did
use CTRL,SHIFT,ENTER. Then tried without it and it just gave me a zero.

 

[Peo Sjoblom]

You would get that is you have text in the equivalent of the range Biff
called Finished

--


Regards,


Peo Sjoblom

 

[T. Valko]

MEDIAN ignores text.

 

[T. Valko]

If there are no cells that meet the criteria then you'll get that error.

In your updated file, I enter the array formula** in AC3 and copy down to
AC15. Out of all those cells only 2 return a number.

Atlanta = 2
Bathurst = 8.5

All the others return #NUM! because there is no data for those tracks.

 

[Peo Sjoblom]

Not if all the values are text

--


Regards,


Peo Sjoblom

MEDIAN ignores text.

 

[T. Valko]

Clarification:

MEDIAN ignores text.

However, if the range contains nothing but text then you'll get the #NUM!
error.

But that is not the case in this situation. There are both numbers and text
in the range and in this case the text is ignored.

--
Biff
Microsoft Excel MVP

MEDIAN ignores text.

 

[T. Valko]

I just posted a clarification at the same time you posted this.

 

[Brian]

Yes, Peo, some cells have text, but Biff is right - MEDIAN ignores text. I
guess the next logical question is how best to frame the formula so that
tracks without data get a "-".

 

[T. Valko]

Something to consider...

Your file is already "kind of big", >5mb, and it's already exhibiting slow
calculation times. I see all those other columns where you intend to do
calculations and it looks like most of them are going to end up being array
formulas. So, you need to start making things as efficient as possible. With
that in mind:

=IF(SUMIF(Tracks,C3,Finished),MEDIAN(IF(Tracks=C3,Finished)),"-")

array entered