Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX/MIN in range potantially including text
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MAX/MIN in range potantially including text
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 "TrackStats" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a month to a quarter ...... | New Users to Excel | |||
Sumproduct issues | Excel Worksheet Functions | |||
conditional formula: sum a range if text present in another range | Excel Discussion (Misc queries) | |||
Using text for the range in AVERAGE function | Excel Worksheet Functions | |||
Counting Occurrence of Text within Text in Cells in Range. | Excel Worksheet Functions |