Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    New Lounger
    Join Date
    May 2011
    Location
    California
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Marine Corps PFT spreadsheet (need help with run time calculations)

    I am needing help with a calculation in excel 2007. Lets say if cell N5 is equal to or less than 18:00 min then cell 05 equals 100, and if N5 is 18:01 thru 18:10 then O5 will equal 99, and so on subtracting 1 from O5 for every 10 second interval in N5. If someone could assisst me with this I would be very grateful.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =100-MAX(0,INT((N5-TIME(0,18,1))*24*60*6)+1)

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    bryanfarley (2011-05-12)

  4. #3
    New Lounger
    Join Date
    May 2011
    Location
    California
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you this works perfectly.

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by bryanfarley View Post
    Thank you this works perfectly.
    Aflak! Not Exactly!

    Steve,

    This piqued my interest but I didn't get it worked out before I saw you're post. So I gave your formula a shot in Excel 2003 with some interesting results. See below. It seems your original formula Col-C doesn't give consistent results. I didn't understand why you had the second in the Time() function. So I tried it out w/o the second and it seems to work consistently EXCEPT for the 1st value! Logically one of these two variations should work in all cases but it's not so. I experimented with moving the +1 around and also using the Floor function to no avail. I guess this is one of those cases where binary just can't do base 10 fractions!
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I can't test in XL2003 at all. At the moment I can test in XL2010 and next week I could test (when I return home) in XL2000. The extra second is to shift is to offset the time since it is not the conventional 0-9 sec which are together, bu the 1-10. I needed to make the 0,10, 20, etc part of the earlier group.

    Your formula (without the 1) in your examples does not show (except for the 18:00) any of the 10's (18:10, 18:20, 18:30, 18:40, 18:50, 19:00) all of which will give the wrong value. I suspect the reason that the 19:01 you have is off with my formula is rounding error. I suspect you used a fill and though it displays at 19:01 it is probably between 19:00.0 and 19:00.99 and not quite 19:01. I can't test this since you only put a screen shot in. Manually enter in 0:19:01 and see if you still get 94...

    Steve

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    Right you are! I did fill and when I manually entered the values your formula worked like a charm!
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    New Lounger
    Join Date
    May 2011
    Location
    California
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    To both of you reading your conversation between one another shows me exactly how far I have to go in a better understanding of my tools. I honestly got it working when I said I did by using the h:mm:ss formating on the time section (honestly took me a bit to figure out thats how the gentelman set it). I will have further questions soon seeing as I also have a differant test to make a sheet on but it is nowhere as straight forward as this one was and I don't even begin on knowing what to ask.

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Bryan,

    That's what's great about this forum. There are many people here with very varied experiences using Excel and other products. Some with Financial backgrounds, some with math or engineering. But all with a desire to learn and help others. The back and forth is just how Socrates {the Socratic Method} taught...some things never change! Just keep asking questions and experimenting and you'll get to your destination with a little help from your friends in the Lounge.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    New Lounger
    Join Date
    Jun 2011
    Location
    Camp Lejeune
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I’m a novice at excel but have been able to learn some formulas that help in making lists and counting how many time a person is put on duty and so forth. Where do I get the more advanced teachings to expand my knowledge in this program? I hope to be a 1ST SGT in the Marines and think this will help me with the ungodly amount of lists that are to come if I should get to that rank. Thanks in advance! By the way, this was really helpful!!

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Where do I get the more advanced teachings to expand my knowledge in this program?
    In addition to asking questions here, check out some of the sites I recommended at (some of the sites may no longer be valid, but you googling may find them...):
    http://windowssecrets.com/forums/sho...l=1#post543100

    Steve

  12. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    SSgt Schulte,

    I hear nothing...I see nothing... Sorry I couldn't resist. Welcome to the lounge.
    Here's a link to an earlier thread you might find useful.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #12
    New Lounger
    Join Date
    Jun 2011
    Location
    Camp Lejeune
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How do I make rifle scores 240-250 represent 5.0, 235-239=4.9, 230-234=4.8, 225-229=4.7, 220-224=4.6, 215-219=4.4, 210-214=4.2, 205-209=3.8, 200-204=3.6, 195-199=3.4, 190-194=3.0, 000-189=0.0 in excel? I have been racking my brains reading all the formulas but I’m still pretty new at this and I would love to learn.
    I have tried the MAX formula and just having the cell equal what was put into the cell.

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    SSgt Schulte,

    What you need is VLookup. See attached workbook.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    New Lounger
    Join Date
    Jun 2011
    Location
    Camp Lejeune
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OMG! This is the most amazing program! I didn't get to "V" yet when I was looking up and reading about formulas, was at “C”. Amazing, simply amazing, this program...and you for that matter. Thank you very much for you help so far. You have inspired me to continue my studies in the Excel program. Semper Fi!

  16. #15
    New Lounger
    Join Date
    Aug 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the formula, is there a way I don't need to input (0) each time with the colons for the run time? ie: 18:05, instead of typing 0:18:05 i could just type 1805 and be done with it.

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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