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

1. ## 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. =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. Thank you this works perfectly.

5. Originally Posted by bryanfarley
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!

6. 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. Steve,

Right you are! I did fill and when I manually entered the values your formula worked like a charm!

8. 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. 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.

10. 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. 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...):
https://windowssecrets.com/forums/sho...l=1#post543100

Steve

12. SSgt Schulte,

I hear nothing...I see nothing... Sorry I couldn't resist. Welcome to the lounge.

13. 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. SSgt Schulte,

What you need is VLookup. See attached workbook.

15. 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. 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 Last