Fixing some bugs

D

daddioja

Another user helped me with some of the functions in this file, and the
problem is that I have one bug I can't fix. There are four sheets. The
first two sheets contain different types of scores.
The fourth sheet ranks each of the different types of scores on both
first sheets. The third sheet reports out on the bottom five scores in
each category. If one of the scores is missing, the whole thing gets
screwed up.

I have attached the file and removed a some of the scores to
illustrate. If anyone is willing to take a look and recommend a fix, I
would be so appreciative.


+-------------------------------------------------------------------+
|Filename: RSDSS v7.xls.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4900 |
+-------------------------------------------------------------------+
 
B

Biff

Hi!

On the Ranking sheet, cell C2, modify this portion of the formula:

SUM(IF('Math CST'!E2>'Math CST'!E$2:E$36

Change to:

SUM(IF(('Math CST'!E2>'Math CST'!E$2:E$36)*('Math CST'!E$2:E$36<>"")

Make sure you re-enter as an array!

Copy across F2

Biff
 
D

daddioja

Biff, I think I already indicated you are the man--you have proved it
once again. I appreciate your help beyond words.
 
D

daddioja

I am in the home stretch on this, but I have one more thing (I think).
If someone--hopefully Biff--could take a look at the sheet called Math
RC2. I am having the same type of problem in a different part of the
workbook. I want the sheets to order the data based on the ranking
sheet. It works except when there are empty fields. Any advice will be
greatly appreciated. Thanks for the help. Jason


+-------------------------------------------------------------------+
|Filename: RSDSS Janice v1.xls.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4904 |
+-------------------------------------------------------------------+
 
B

Biff

In sheet Math RC2, cell A2, change the formula to:

=IF(ROWS($1:1)<=COUNT(Ranking!C$2:C$36),INDEX('Math
CST'!$A$2:$A$36,MATCH(LARGE(Ranking!C$2:C$36,ROWS($1:1)),Ranking!C$2:C$36,0)),"")

Copy down.

That'll correct the errors in the other columns as well.

I also see that you need to do the same thing on a few other sheets.

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top