+ Reply to Thread
Results 1 to 12 of 12

Calculating times ranges

  1. 05-30-2011, 01:12 PM #1
    troublewithexcel is offline
    Registered User
    Join Date
    05-30-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Calculating times ranges

    I have attached a document where I am trying to work out two issues.

    One is the time it takes for a person (race number) to complete the event- time elapse from start time to Run Finish time. When I do a basic formula of sum=(i19-c19) it gives me the Values error message. I have checked they are all the same date formula.

    second i need to work out which is the fastest and slowest in each category. Does anyone know how to do this without it taking FOREVER?!

    The above is for a triathlon.

    Any help anyone can give is much appreciated.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-30-2011, 01:24 PM #2
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,352

    Re: Calculating times ranges

    Hi and welcome to the forum,

    It looks like you have typed in text strings instead of time numbers. Try this formula in J19 and pull it down for all rows.
    Please Login or Register  to view this content.
    See attached for second part of the question. I've made non finishers times equal to 10 so they don't show as zero or fastest times.

    Formatting the cells at the correct time format is important also.
    Attached Files Attached Files
    Last edited by MarvinP; 05-30-2011 at 01:40 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 05-30-2011, 03:30 PM #3
    troublewithexcel is offline
    Registered User
    Join Date
    05-30-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating times ranges

    Marvin, I cant thank you enough. I have been looking at this for hours!!! and you have sorted it in seconds.

    Thank you.
    Reply With QuoteRegister To Reply
  4. 05-30-2011, 03:58 PM #4
    troublewithexcel is offline
    Registered User
    Join Date
    05-30-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating times ranges

    Thank you so much for your help on my last question....

    My next question is that I have a lot of information and not many, any findings as such. just lots and lots of numbers!

    Column B is the wave (Triathlon) but I need to know not only who is the fasted in each category, swim, bike and run, and overall but who is the slowest, what I am finding difficult is that the slowest usually is quite an abnomily,so I need to find something like the 90% slowest!

    Its just so much information, I want to be able to see fastest and slowest of each wave/category but I dont know how to get that information easily -is there any way????

    Thank you, any help would be super...

    Annie
    Reply With QuoteRegister To Reply
  5. 05-30-2011, 06:06 PM #5
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,352

    Re: Calculating times ranges

    Hi,
    You need to look at conditional formatting. You can fill the cells of all who are in the top or bottom number or percentage using it.

    See http://www.homeandlearn.co.uk/excel2...l2007s6p2.html for some help.

    See attaached for the examples. I'm not sure how to deal with non-finishers.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 05-31-2011, 01:27 PM #6
    troublewithexcel is offline
    Registered User
    Join Date
    05-30-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating times ranges

    Thanks Marvin, I have learnt about Conditional Formatting, so much to learn!!! Thank you for the tip.

    I have attached a new sheet - final question I promise-

    I have added the totals and ranks to sheet one and two (I have 35 sheets so this will time some serious time).

    I have to complete sheet 1 - what are your thoughts to putting the information on this sheet through formula?!

    If you have any thoughts that would be magnificent.

    Annie
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 05-31-2011, 02:21 PM #7
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,352

    Re: Calculating times ranges

    Hi Annie,

    I think Pivot Tables may be a better method for you.
    In Col A would be the Category the person is in. You will need to have different names for each (you implied you have 35 of them). What I mean is that you would have Male Sprint 1530 and another category of Male Sprint 1600. Make sure all categories have have different names.

    Then accumulate all 35 sheets onto a single sheet. After this you can use the power of Excel Tables. You can create an autofilter table to only show any single category or multiple select categories to view.

    If you have a big single table the problem of doing formulas on 35 different sheets goes away.

    Now, I'd create a Pivot Table and have the category as a filter. Pivots can show rank and other summation values.

    A single table instead of 35 tables on different sheets is suggestion. I think you will be supprised on how easy the problem becomes if you us Auto Filters and Pivot Tables.

    Find the attached with an example of what I'm thinking.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 05-31-2011, 04:51 PM #8
    troublewithexcel is offline
    Registered User
    Join Date
    05-30-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating times ranges

    Marvin, this works, thank you. There is so much to learn. Pivot Tables look to DEFINITELY be the right solution. The only problem is when I do it, all values read '1' .... everything!!

    I have attached the document! can you help - I think we are so nearly there!!!!

    arhhh!!! thank you, Annie
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 05-31-2011, 06:20 PM #9
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,352

    Re: Calculating times ranges

    You need to change the "Value Field Settings" from Count to Sum to get rid of the 1 and show the times. You will also need to display them as times.

    Keep looking at my last file and see how I did filters and row filters too. If there are some numbers and some blanks in a column of numbers, Pivot Tables defaults to Count instead of Sum.

    You can also show values as RANK (at least in 2010 Pivot Tables.

    See the attached where I've added the totals a second time and Show As Rank smallest to largest. I think you will really like this.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 06-01-2011, 07:17 AM #10
    troublewithexcel is offline
    Registered User
    Join Date
    05-30-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating times ranges

    Marvin. I don't know how to thank you. I think I have sorted it. Its a slow process as I am going through and deleting the #VALUE! cells to make the ranks work but it does work so Im happy.

    Thank you so much - I have asked and asked people for help and you have solved the problems in less time than I thought possible.

    Many thanks
    Reply With QuoteRegister To Reply
  11. 06-02-2011, 09:21 AM #11
    troublewithexcel is offline
    Registered User
    Join Date
    05-30-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating times ranges

    Marvin, last one - this time I promise.

    I need to work out the rank after each stage! So after swim they maybe in first place but after the bike they are in 3rd and then after the run at the finish they could be 1st again! have you any idea how to do this?!
    Reply With QuoteRegister To Reply
  12. 06-02-2011, 09:22 AM #12
    troublewithexcel is offline
    Registered User
    Join Date
    05-30-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Calculating times ranges

    Marvin, last one - this time I promise.

    I need to work out the rank after each stage! So after swim they maybe in first place but after the bike they are in 3rd and then after the run at the finish they could be 1st again! have you any idea how to do this?!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1