MAX/MIN in range potantially including text

[Guest]

Howdy,

I'm creating a spreadsheet for use by a track team. What I have set up for
them is a separate sheet for each athlete for multiple events across many
meets, having the events (100m, 200m, 100m hurdles, etc) down the leftmost
column, and each meet across the page.

On the rightmost column, I have a function that is tracking the times the
athlete placed for each event, and picking out the best time/distance per
event, which is simplistic enough:

=IF(B12+D12+F12+H12+J12+L12+N12=0,"-",MIN(B12,D12,F12,H12,J12,L12,N12))

if (the range of events) is empty, a simple dash is shown to note that the
athlete did not compete in that event, while if there is an entry, the
lowest/largest number is picked out as their best time or best distance. (The
code skips every other column as beside each time is the place that they came
in their race aka 1st of 4, 5th of 10 etc)

I've run into a problem, however, in that there may be two other
possibilities. There may be times where the athlete did not qualify for the
event, or was disqualified, in which case the coach needs to enter DNQ or
DISQ or something similar to show what occurred.

This throws the function above out of whack, giving a #VALUE error.

I tried using an if function to determine if there were non-numerical values
in the range, but couldn't get the function to work. Is there a way for the
MIN or MAX functions to ignore these non-numeric entries without producing
the error?

I'm using excel 2003, as that may possibly matter.

Thanks

 

[T. Valko]

MIN and/or MAX ignore text entries so the error is coming from this portion:

=IF(B12+D12+F12+H12+J12+L12+N12=0

Use SUM or COUNT as they will also ignore text entries:

=IF(SUM(B12,D12,F12,H12,J12,L12,N12)=0,"-",MIN(B12,D12,F12,H12,J12,L12,N12))

=IF(COUNT(B12,D12,F12,H12,J12,L12,N12)=0,"-",MIN(B12,D12,F12,H12,J12,L12,N12))

Biff