[Solved] Using MIN to find lowest value of time!

Discuss the spreadsheet application

[Solved] Using MIN to find lowest value of time!

Postby RevengerXXL » Mon Apr 27, 2020 4:17 am

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
Attachments
Revenger Cup Test Points System.csv
(1.04 KiB) Downloaded 15 times
Last edited by Hagar Delest on Mon Apr 27, 2020 7:14 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.3
MS Windows 10
RevengerXXL
 
Posts: 8
Joined: Mon Apr 27, 2020 4:01 am

Re: Using MIN to find lowest value of time!

Postby Zizi64 » Mon Apr 27, 2020 5:47 am

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.)
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using MIN to find lowest value of time!

Postby RevengerXXL » Mon Apr 27, 2020 5:50 am

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
RevengerXXL
 
Posts: 8
Joined: Mon Apr 27, 2020 4:01 am

Re: Using MIN to find lowest value of time!

Postby Zizi64 » Mon Apr 27, 2020 5:51 am

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.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using MIN to find lowest value of time!

Postby Zizi64 » Mon Apr 27, 2020 5:53 am

Check the type of the laptimes in your .ods document, and please upload it in .ods format.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using MIN to find lowest value of time!

Postby RevengerXXL » Mon Apr 27, 2020 5:54 am

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
RevengerXXL
 
Posts: 8
Joined: Mon Apr 27, 2020 4:01 am

Re: Using MIN to find lowest value of time!

Postby RevengerXXL » Mon Apr 27, 2020 5:56 am

ods file as requested
Attachments
Revenger Cup Test Points System.ods
(14.2 KiB) Downloaded 9 times
OpenOffice 4.1.3
MS Windows 10
RevengerXXL
 
Posts: 8
Joined: Mon Apr 27, 2020 4:01 am

Re: Using MIN to find lowest value of time!

Postby RevengerXXL » Mon Apr 27, 2020 5:59 am

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
RevengerXXL
 
Posts: 8
Joined: Mon Apr 27, 2020 4:01 am

Re: Using MIN to find lowest value of time!

Postby Zizi64 » Mon Apr 27, 2020 6:02 am

Also when it is in the odf format it only returns a 0 as lowest 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).
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using MIN to find lowest value of time!

Postby Zizi64 » Mon Apr 27, 2020 6:09 am

How would I convert the time format to something that would work?


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.

laptime.png
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using MIN to find lowest value of time!

Postby Zizi64 » Mon Apr 27, 2020 6:12 am

Adjusting of the Import filter

importfilter.png
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using MIN to find lowest value of time!

Postby RevengerXXL » Mon Apr 27, 2020 6:17 am

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
RevengerXXL
 
Posts: 8
Joined: Mon Apr 27, 2020 4:01 am

Re: Using MIN to find lowest value of time!

Postby RevengerXXL » Mon Apr 27, 2020 7:05 am

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 11 times
OpenOffice 4.1.3
MS Windows 10
RevengerXXL
 
Posts: 8
Joined: Mon Apr 27, 2020 4:01 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests