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

Topic

New Reply

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.

Reply | Quote

Replies

=100-MAX(0,INT((N5-TIME(0,18,1))*24*60*6)+1)

Steve

Reply | Quote

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.

Reply | Quote

I retired from active duty in 1992, we had apps and spreadsheets way back then to do these calcs. Why the heck are we re-inventing the wheel? I was always taught, train your people, teach them what you know, then they can spend time learning new things not wasting time re-learning old stuff. Someone has fallen down on their job.

Reply | Quote

=100-MAX(0,INT((N5-TIME(0,18,1))*24*60*6)+1)

Steve
this one or the other one that was posted by RG
can’t really provide an example. but imagine a column of times and next to it a column of points. the time needs to be converted to points.

Reply | Quote

Thank you this works perfectly.

Reply | Quote

Thank you this works perfectly.

Aflak! Not Exactly!:rolleyes:

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!:mad:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Steve,

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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.:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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!!

Reply | Quote

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/showthread//94949-Select-Cells-that-are-Locked-(2003-sr2)?p=543100&viewfull=1#post543100

Steve

Reply | Quote

SSgt Schulte,

I hear nothing…I see nothing… Sorry I couldn’t resist. Welcome to the lounge.:cheers:
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!
Computer Specs

Reply | Quote

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.

Reply | Quote

SSgt Schulte,

What you need is VLookup. See attached workbook.:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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!

Reply | Quote

NOt directly. It would take some code in the background. See Chip Pearson’s site at http://www.cpearson.com/excel/DateTimeEntry.htm for how to set it up in the workbook [WARNING: background code will eliminate the ability to UNDO. Macro code resets the undo stack]

Steve

Reply | Quote

i have been trying to figure out this formula and it isnt calculating anything what am i doing wrong?

Reply | Quote

Which formula? Please post the formula here?

cheers, Paul

Reply | Quote

here is the roster that i have, i am trying to calculate the run time, and i have put the formula that was provided above, but it kept giving me zeros? what am i doing wrong? (FYI) these kids arent running the full pft just a partial so they are running just 1 mile.

Reply | Quote

Which formula in the posted workbook is giving you issues? Which cell in worksheet BA?

Steve

Reply | Quote

I enter either Stevie’s or RG’s formula in excel and i get either a wierd number in the thousands or ########################
can someone tell me a formula to use and how to format the cell (maybe im not formatting it right, thank you)
Im using excel 07

Reply | Quote

Russianog,

Welcome to the lounge as a new poster. :cheers:

Could you please specify which formula as several have been discussed in this thread. An example workbook would be a great help if you could post one. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote