CountIf for multiple ranges problem.
Posted by Sherwood Botsford on January 23, 2002 10:18 AM
I'm writing a spreadsheet to score an orienteering
meet. I'm new to excel
Each team records the time that it reaches a
particular location, called a control.
They are awarded points on the basis of the order
they reach a control.
So the problem I have is to rank them in the time
they reached that control, if they reached it at all.
For readability reasons, all of a teams data is
kept together, so the time columns are spread out throughout the sheet.
What I'd like to do is: for each team:
=countif($ref1,$ref2,$ref3,$ref4...,"<ref1") - countblanks($ref1,$ref2,$ref3,$ref4...)
refN are references to each team's time column.
They are NOT adjacent.
That is, count how many times are faster that
this team's time. (The count blanks is ncessary
because blank evaluates as midnight when comparing
times.)
Right now I'm faced with a terrible kluge looking
something like this: (X is row number)
=(if($Ref1X < Ref1X),0,1)+(if($Ref2X < Ref1X),0,1) ... - (isblank($Ref1X) + isblank($Ref2X)...
(Note: The worksheet is at home. I've probably mucked up the syntax above.)
This is tolerable for the present 4 teams, but now
I have to change all the formulas because there are
6 teams this weekend. There must be a more elegant
way to do this, but it's not clear to me what it is.
Posted by Dan on January 23, 2002 10:43 AM
I think your biggest problem is the way that you have your spreadsheet setup. It may be easier to read having the teams and times set up in different places on your spreadsheet, but it makes it much harder to use the spreadsheet functions to analyze the data, and I think you are finding that out.
If possible, I would set up your spreadsheet differently. I would make a table that has the teams on different rows (i.e. label Teams 1-4 in cells A2:A5), and each control in a different column (i.e. Control 1 through Control n in cells B1:n1). Then you would enter the times based on the team and control. You could then use the RANK worksheet function to see how each team stacks up with each other.
If you have any questions or if this doesn't work for you, post back or email me if you want.
