[Solved] Using MIN to find lowest value of time!
-
- Posts: 8
- Joined: Mon Apr 27, 2020 4:01 am
[Solved] Using MIN to find lowest value of time!
Greetings to all,
So I have run into an issue of using Min or Max to return a value of time! Example: Driver A ran his fastest lap at '2:50.345 and Driver B had his fastest lap at '2:53.066 and Driver C had a fastest lap at '2:58.553 yet Drivers D and E were disqualified before the race for tech issues. (there are many more drivers but for example purposes I will be using the above only), So, I want to return in a single cell with the lowest lap time during the race. I have tried using many different formulas like MIN, MINIFS, VLOOKUP(MIN()) etc. What are your thoughts?
On the sheet (Race Results Template) Column K is the fastest lap times. In K3 I would like to reflect the Lowest time from the array K5:K99
Thanks in advance,
RevengerXXL
So I have run into an issue of using Min or Max to return a value of time! Example: Driver A ran his fastest lap at '2:50.345 and Driver B had his fastest lap at '2:53.066 and Driver C had a fastest lap at '2:58.553 yet Drivers D and E were disqualified before the race for tech issues. (there are many more drivers but for example purposes I will be using the above only), So, I want to return in a single cell with the lowest lap time during the race. I have tried using many different formulas like MIN, MINIFS, VLOOKUP(MIN()) etc. What are your thoughts?
On the sheet (Race Results Template) Column K is the fastest lap times. In K3 I would like to reflect the Lowest time from the array K5:K99
Thanks in advance,
RevengerXXL
- Attachments
-
- Revenger Cup Test Points System.csv
- (1.04 KiB) Downloaded 116 times
Last edited by Hagar Delest on Mon Apr 27, 2020 7:14 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
OpenOffice 4.1.3
MS Windows 10
MS Windows 10
Re: Using MIN to find lowest value of time!
Why you use the .csv file format when you want to use the formulas MIN, MINIFS, VLOOKUP(MIN()) etc.?
Always use the native, international standard ODF file formats (.ods for spreadsheets.)
Always use the native, international standard ODF file formats (.ods for spreadsheets.)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 8
- Joined: Mon Apr 27, 2020 4:01 am
Re: Using MIN to find lowest value of time!
I just kept the original formatting from the Downloaded csv file of the race results! Easy enough to convert to odf if that would make a difference!
OpenOffice 4.1.3
MS Windows 10
MS Windows 10
Re: Using MIN to find lowest value of time!
Your laptime-like values are strings (textual contents) really, but they are not numerical values.
I just inported the .csv file into a spreadsheet document with the default settings of the import filter, and the laptimes appeared as strings.
I just inported the .csv file into a spreadsheet document with the default settings of the import filter, and the laptimes appeared as strings.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Using MIN to find lowest value of time!
Check the type of the laptimes in your .ods document, and please upload it in .ods format.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 8
- Joined: Mon Apr 27, 2020 4:01 am
Re: Using MIN to find lowest value of time!
Also when it is in the ods format it only returns a 0 as lowest time and would an if statement reflect the lowest time for drivers who did return a time and not a 0 for the driver who were disconnected or disqualified?
Last edited by RevengerXXL on Mon Apr 27, 2020 5:57 am, edited 1 time in total.
OpenOffice 4.1.3
MS Windows 10
MS Windows 10
-
- Posts: 8
- Joined: Mon Apr 27, 2020 4:01 am
Re: Using MIN to find lowest value of time!
ods file as requested
- Attachments
-
- Revenger Cup Test Points System.ods
- (14.2 KiB) Downloaded 110 times
OpenOffice 4.1.3
MS Windows 10
MS Windows 10
-
- Posts: 8
- Joined: Mon Apr 27, 2020 4:01 am
Re: Using MIN to find lowest value of time!
BTW thanks for your help! How would I convert the time format to something that would work? I am fairly new to this and I have very basic knowledge of spreadsheets
OpenOffice 4.1.3
MS Windows 10
MS Windows 10
Re: Using MIN to find lowest value of time!
Because the 0 is the lowest time value in the table. You must use a helper column with conditional formulas =IF(...), what eliminate the 0 values (it substitutes the with empty strings).Also when it is in the odf format it only returns a 0 as lowest time.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Using MIN to find lowest value of time!
When the import filter is appeared at the opening the .csv file, you must adjust the filter properties. Just select the US English (or other locale setting) for the laptime columns - instead of the Standard option. Then the values will be imported as numeric values. The unit of the appeared numeric values are DAYS. You must format them or convert them into Minutes unit.How would I convert the time format to something that would work?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Using MIN to find lowest value of time!
Adjusting of the Import filter
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 8
- Joined: Mon Apr 27, 2020 4:01 am
Re: Using MIN to find lowest value of time!
Okay Perfect! on the formating! Now to figure out if I need to nest a IF statement in the MIN statement or vice versa! I will get back to you with the results! Thanks again!
OpenOffice 4.1.3
MS Windows 10
MS Windows 10
-
- Posts: 8
- Joined: Mon Apr 27, 2020 4:01 am
Re: Using MIN to find lowest value of time!
Thank you so much Zizi64! Your Amazing! Here is what I did to fix it! I was able to maintain the minute second format through the ods format. I also created a hidden column to with and if statement that gave a lap time of 59:59.59 to the no timed drivers then just used that column to use the MIN statement here is a copy for you to see it!!!
- Attachments
-
- Revenger Cup Test Points System.ods
- (16.36 KiB) Downloaded 120 times
OpenOffice 4.1.3
MS Windows 10
MS Windows 10