I had some help putting together this formula, but it has been giving me some
grief for some reason after it seemed to work ok to start with.....

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,FALSE)),$C3,Qualified),MEDIAN(IF(INDIRE CT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,FALSE ))=$C3,Qualified)),"-")

The data it is evaluating can have text, blanks, or numbers in the range,
"Qualified." In this case, it only has numbers and blanks. MEDIAN shouldn't
have an issue delaing with blanks/deleted cells and the formula above works
fine in other cases where there are blanks. Any suggestions?

Hi,

If you are saying that it works fine for blank cells but doesn't work when
you clear the cells, then maybe the cells are not being cleared using the
Edit, Clear or the Delete key, maybe they are being cleared with spacebar.
You can't see it but it might cause a problem.

If you are asking something else show us a little data and tell us what you
are getting and what you should be getting.
--
Cheers,
Shane Devenshire


"Brian" wrote:

I had some help putting together this formula, but it has been giving me some
grief for some reason after it seemed to work ok to start with.....

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,FALSE)),$C3,Qualified),MEDIAN(IF(INDIRE CT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,FALSE ))=$C3,Qualified)),"-")

The data it is evaluating can have text, blanks, or numbers in the range,
"Qualified." In this case, it only has numbers and blanks. MEDIAN shouldn't
have an issue delaing with blanks/deleted cells and the formula above works
fine in other cases where there are blanks. Any suggestions?

Did you see my last reply to your other post? I couldn't open the link.

Anyhow...

MEDIAN shouldn't have an issue delaing with blanks/deleted
cells and the formula above works fine in other cases where
there are blanks.

If:

IF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRA Y,3,FALSE))=$C3

=TRUE and the corresponding cell in Qualified is empty that corresponding
cell will be included in the calculation as a 0.

Like this:

x...1
x....
x...5

MEDIAN(IF(rng1="x",rng2))

The result is 1.. The empty cell is being evaluated as 0:

MEDIAN({1;0;5})

To account for that:

MEDIAN(IF((rng1="x")*(rng2<""),rng2))

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
I had some help putting together this formula, but it has been giving me
some
grief for some reason after it seemed to work ok to start with.....

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,FALSE)),$C3,Qualified),MEDIAN(IF(INDIRE CT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,FALSE ))=$C3,Qualified)),"-")

The data it is evaluating can have text, blanks, or numbers in the range,
"Qualified." In this case, it only has numbers and blanks. MEDIAN
shouldn't
have an issue delaing with blanks/deleted cells and the formula above
works
fine in other cases where there are blanks. Any suggestions?

I missed it Biff.........but I have fixed those areas - or at least thought I
did. I will host the file at this site:

http://www.oatas.net/personal/Online...Statistics.xls

and delete it when we are finished (5 MB). It's almost completed except
instructions.....

Shane, I thought that was a possibility as well and so I copied other blank
cells which were being calculated correctly (ie not calc'd at all) to the
offending league entries. Specifically, FSB Pro Cup Season 13 in the Stats
Sheet in cell Y11. (auto calc is turned off - just click the button I made
that is on the sheet to re-calc it.

BTW, thanks to all of you who have helped - this thing is a beast that I
couldn't have done without the helpful information on these forums.

For some reason the 'by Season' selection dropdown is giving funky
statistics for totals across row A as compared to all of the other
selections. I don't quite understand that one :(

"T. Valko" wrote:

Did you see my last reply to your other post? I couldn't open the link.

Anyhow...

MEDIAN shouldn't have an issue delaing with blanks/deleted
cells and the formula above works fine in other cases where
there are blanks.

If:

IF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRA Y,3,FALSE))=$C3

=TRUE and the corresponding cell in Qualified is empty that corresponding
cell will be included in the calculation as a 0.

Like this:

x...1
x....
x...5

MEDIAN(IF(rng1="x",rng2))

The result is 1.. The empty cell is being evaluated as 0:

MEDIAN({1;0;5})

To account for that:

MEDIAN(IF((rng1="x")*(rng2<""),rng2))

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
I had some help putting together this formula, but it has been giving me
some
grief for some reason after it seemed to work ok to start with.....

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,FALSE)),$C3,Qualified),MEDIAN(IF(INDIRE CT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,FALSE ))=$C3,Qualified)),"-")

The data it is evaluating can have text, blanks, or numbers in the range,
"Qualified." In this case, it only has numbers and blanks. MEDIAN
shouldn't
have an issue delaing with blanks/deleted cells and the formula above
works
fine in other cases where there are blanks. Any suggestions?

I was able to fix everything else except the Averages & Medians along with
the incorrect best finish of 0.00 for FSB Pro Cup Season 13 (Stats Sheet -
selecting by Season). The Stats sheet is protected but can be unprotected by
leaving the password blank. I uploaded the latest version to the same place:

http://www.oatas.net/personal/Online...Statistics.xls


The Averages & Medians are reporting the wrong values in some case - FSB Pro
Cup Season 13 is a good example.....

Avg Qual it's reporting: 2.90 Avg Qual (what it should be): 7.25
Median Qual it's reporting: 0.00 Median Qual (what it should be): 2.50

Avg Fin it's reporting: 3.45 Avg Fin (what it should be): 8.63
Median Fin it's reporting: 0.00 Median Fin (what it should be): 4.50

I imagine virtually every other Med & Avg are not working correctly where
blanks exist?

"T. Valko" wrote:

Did you see my last reply to your other post? I couldn't open the link.

Anyhow...

MEDIAN shouldn't have an issue delaing with blanks/deleted
cells and the formula above works fine in other cases where
there are blanks.

If:

IF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRA Y,3,FALSE))=$C3

=TRUE and the corresponding cell in Qualified is empty that corresponding
cell will be included in the calculation as a 0.

Like this:

x...1
x....
x...5

MEDIAN(IF(rng1="x",rng2))

The result is 1.. The empty cell is being evaluated as 0:

MEDIAN({1;0;5})

To account for that:

MEDIAN(IF((rng1="x")*(rng2<""),rng2))

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
I had some help putting together this formula, but it has been giving me
some
grief for some reason after it seemed to work ok to start with.....

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,FALSE)),$C3,Qualified),MEDIAN(IF(INDIRE CT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,FALSE ))=$C3,Qualified)),"-")

The data it is evaluating can have text, blanks, or numbers in the range,
"Qualified." In this case, it only has numbers and blanks. MEDIAN
shouldn't
have an issue delaing with blanks/deleted cells and the formula above
works
fine in other cases where there are blanks. Any suggestions?

I imagine virtually every other Med & Avg are not
working correctly where blanks exist?

Yes.

All those 1000's of formulas using INDIRECT are *killing you*! That's why
calculation takes so long.

All those formulas that are returning incorrect results is due to what I
explained in my other reply:

Empty cells where a criteria is met are being included in the calculation
and are being evaluated as 0.

So, you need to add a test in those formulas that makes sure the cells are
not empty. With the current formulas this will only make things worse (even
slower calculation).

If you want to improve the preformance of this file *you need to get rid of
all those INDIRECT functions.*

Not being critical, but this is a classic case of where using *defined names
for everything* just leads to unforeseen problems. Personally, I don't like
using defined names and use them only on rare occasions in really long
complex formulas or when "forced" to by Excel. None of the formulas I've
seen in this file would I consider really complex. But, that's just me, my
preference.

Here is an excellent source for efficient methods and performance
improvements:

http://www.decisionmodels.com/

Another tip: When you have that many formulas, especially volatile formulas,
it might be better to just let them return errors instead of trapping the
errors within the formula. You can use conditional formatting to hide the
errors. However, this will lead to having to account for these errors in any
downstream calculations. So, there's a lot to consider when designing a good
efficient spreadsheet that crunches a huge amount of data.


--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
I was able to fix everything else except the Averages & Medians along with
the incorrect best finish of 0.00 for FSB Pro Cup Season 13 (Stats Sheet -
selecting by Season). The Stats sheet is protected but can be unprotected
by
leaving the password blank. I uploaded the latest version to the same
place:

http://www.oatas.net/personal/Online...Statistics.xls


The Averages & Medians are reporting the wrong values in some case - FSB
Pro
Cup Season 13 is a good example.....

Avg Qual it's reporting: 2.90 Avg Qual (what it should be): 7.25
Median Qual it's reporting: 0.00 Median Qual (what it should be): 2.50

Avg Fin it's reporting: 3.45 Avg Fin (what it should be): 8.63
Median Fin it's reporting: 0.00 Median Fin (what it should be): 4.50

I imagine virtually every other Med & Avg are not working correctly where
blanks exist?

"T. Valko" wrote:

Did you see my last reply to your other post? I couldn't open the link.

Anyhow...

MEDIAN shouldn't have an issue delaing with blanks/deleted
cells and the formula above works fine in other cases where
there are blanks.

If:

IF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRA Y,3,FALSE))=$C3

=TRUE and the corresponding cell in Qualified is empty that corresponding
cell will be included in the calculation as a 0.

Like this:

x...1
x....
x...5

MEDIAN(IF(rng1="x",rng2))

The result is 1.. The empty cell is being evaluated as 0:

MEDIAN({1;0;5})

To account for that:

MEDIAN(IF((rng1="x")*(rng2<""),rng2))

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
I had some help putting together this formula, but it has been giving me
some
grief for some reason after it seemed to work ok to start with.....

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,FALSE)),$C3,Qualified),MEDIAN(IF(INDIRE CT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,FALSE ))=$C3,Qualified)),"-")

The data it is evaluating can have text, blanks, or numbers in the
range,
"Qualified." In this case, it only has numbers and blanks. MEDIAN
shouldn't
have an issue delaing with blanks/deleted cells and the formula above
works
fine in other cases where there are blanks. Any suggestions?

Thanks - I'll see what I can figure out.

"T. Valko" wrote:

I imagine virtually every other Med & Avg are not
working correctly where blanks exist?

Yes.

All those 1000's of formulas using INDIRECT are *killing you*! That's why
calculation takes so long.

All those formulas that are returning incorrect results is due to what I
explained in my other reply:

Empty cells where a criteria is met are being included in the calculation
and are being evaluated as 0.

So, you need to add a test in those formulas that makes sure the cells are
not empty. With the current formulas this will only make things worse (even
slower calculation).

If you want to improve the preformance of this file *you need to get rid of
all those INDIRECT functions.*

Not being critical, but this is a classic case of where using *defined names
for everything* just leads to unforeseen problems. Personally, I don't like
using defined names and use them only on rare occasions in really long
complex formulas or when "forced" to by Excel. None of the formulas I've
seen in this file would I consider really complex. But, that's just me, my
preference.

Here is an excellent source for efficient methods and performance
improvements:

http://www.decisionmodels.com/

Another tip: When you have that many formulas, especially volatile formulas,
it might be better to just let them return errors instead of trapping the
errors within the formula. You can use conditional formatting to hide the
errors. However, this will lead to having to account for these errors in any
downstream calculations. So, there's a lot to consider when designing a good
efficient spreadsheet that crunches a huge amount of data.


--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
I was able to fix everything else except the Averages & Medians along with
the incorrect best finish of 0.00 for FSB Pro Cup Season 13 (Stats Sheet -
selecting by Season). The Stats sheet is protected but can be unprotected
by
leaving the password blank. I uploaded the latest version to the same
place:

http://www.oatas.net/personal/Online...Statistics.xls


The Averages & Medians are reporting the wrong values in some case - FSB
Pro
Cup Season 13 is a good example.....

Avg Qual it's reporting: 2.90 Avg Qual (what it should be): 7.25
Median Qual it's reporting: 0.00 Median Qual (what it should be): 2.50

Avg Fin it's reporting: 3.45 Avg Fin (what it should be): 8.63
Median Fin it's reporting: 0.00 Median Fin (what it should be): 4.50

I imagine virtually every other Med & Avg are not working correctly where
blanks exist?

"T. Valko" wrote:

Did you see my last reply to your other post? I couldn't open the link.

Anyhow...

MEDIAN shouldn't have an issue delaing with blanks/deleted
cells and the formula above works fine in other cases where
there are blanks.

If:

IF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRA Y,3,FALSE))=$C3

=TRUE and the corresponding cell in Qualified is empty that corresponding
cell will be included in the calculation as a 0.

Like this:

x...1
x....
x...5

MEDIAN(IF(rng1="x",rng2))

The result is 1.. The empty cell is being evaluated as 0:

MEDIAN({1;0;5})

To account for that:

MEDIAN(IF((rng1="x")*(rng2<""),rng2))

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
I had some help putting together this formula, but it has been giving me
some
grief for some reason after it seemed to work ok to start with.....

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,FALSE)),$C3,Qualified),MEDIAN(IF(INDIRE CT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,FALSE ))=$C3,Qualified)),"-")

The data it is evaluating can have text, blanks, or numbers in the
range,
"Qualified." In this case, it only has numbers and blanks. MEDIAN
shouldn't
have an issue delaing with blanks/deleted cells and the formula above
works
fine in other cases where there are blanks. Any suggestions?

For right now I am going to stick with the nasty INDIRECTs until I can get a
handle on this headache! I have tried several options with no luck...where
exactly do you add the extra condition that it must be non-blank and non-zero?

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,FALSE)),$B3,Qualified),AVERAGE(IF(INDIR ECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,FALS E))=$B3,Qualified)),"-")

This is going to be really ugly!

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,0)),$B3,Qualified),AVERAGE(IF(INDIRECT( VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,0))=$B3) *(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY ,3,0))<""),Qualified)),"-")--BiffMicrosoft Excel MVP"Brian" wrote in ... For right now I am going to stick with the nasty INDIRECTs until I can geta handle on this headache! I have tried several options with noluck...where exactly do you add the extra condition that it must be non-blank andnon-zero?=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,S tats_Type_ARRAY,3,FALSE)),$B3,Qualified),AVERAGE(I F(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY ,3,FALSE))=$B3,Qualified)),"-")

LOL - this sheet is pretty ugly but it does do as intended for better or
worse. The formula you provided does have an error in it somewhere - I'll
take a look and see if it's a missing paratheses, but I don't think is the
issue.

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,0)),$B3,Qualified),AVERAGE(IF(INDIRECT( VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,0))=$B3) *(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY ,3,0))<""),Qualified)),"-")

"T. Valko" wrote:

This is going to be really ugly!

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,0)),$B3,Qualified),AVERAGE(IF(INDIRECT( VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,0))=$B3) *(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY ,3,0))<""),Qualified)),"-")--BiffMicrosoft Excel MVP

The formula you provided does have an error in it somewhere

Yeah, my fault. I used the wrong range!

Try this one (array entered):

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,0)),$B3,Qualified),AVERAGE(IF((INDIRECT (VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,0))=$B3 )*(Qualified<""),Qualified)),"-")


--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
LOL - this sheet is pretty ugly but it does do as intended for better or
worse. The formula you provided does have an error in it somewhere - I'll
take a look and see if it's a missing paratheses, but I don't think is the
issue.

=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,0)),$B3,Qualified),AVERAGE(IF(INDIRECT( VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,0))=$B3) *(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY ,3,0))<""),Qualified)),"-")

"T. Valko" wrote:

This is going to be really ugly!


=IF(SUMIF(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Ty pe_ARRAY,3,0)),$B3,Qualified),AVERAGE(IF(INDIRECT( VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY,3,0))=$B3) *(INDIRECT(VLOOKUP(Stats_Dropdown,Stats_Type_ARRAY ,3,0))<""),Qualified)),"-")--BiffMicrosoft
Excel MVP