Creating a USMC PFT Spreadsheet in excel. Need help with VLookup formula.

Topic

New Reply

I have 3 different work sheets.

worksheet 1 is pft score (where you type the persons info ie…. gender, age, run time, pull ups, and crunches)

worksheet 2 is the male score card

work sheet 3 is the female score card

So far i am able to compute a score from each worksheet with two seperate cells in worksheet 1. How do I get it to read in just one cell. I am running into the problem with the genders. Here is my formula thus far…..

=IF(OR(C17=””,C1745,8,ROUNDUP((C17-16)/5+1,0)),TRUE))

how do i tell it, if B17=Male/Female look on the male/Female scorecard.

Reply | Quote

Replies

Assuming same layout for each card:

=IF(OR(C17=””,C1745,8,ROUNDUP((C17-16)/5+1,0)),TRUE))

Reply | Quote

It seems to be working great. Thanks a lot. I just couldn’t figure out where to put it at! I am just getting the hang of using excel on a higher level and you have helped out greatly. I do have one more question. Hopefully you can help me out. I used a template formula from a different sheet and in the formula I provided I do not quite understand the back ebd of it. I am hoping you can shed some light on it. I can’t figure out what ((C17-16)/5+1,0)),TRUE)) is doing in the formula. I know it makes it work but I would like to understand it better. C17 is where the age is put in. Any assistance would be great. You have already been a great help. Thanks again!

Reply | Quote

Looking at the original formula, and ignoring the initial IF test for simplicity, we have:
VLOOKUP(F17,’FEMALE TABLES’!A3:H193,IF(C17>45,8,ROUNDUP((C17-16)/5+1,0)),TRUE)

where:
F17 – lookup value
’FEMALE TABLES’!A3:H193 – lookup table
IF(C17>45,8,ROUNDUP((C17-16)/5+1,0)) – column number to return the value from
TRUE – means we are not looking for an exact match, and the lookup table data is sorted in ascending order (on the first column).

Now let’s examine that third part:
IF(C17>45,8,ROUNDUP((C17-16)/5+1,0))
so if the age is greater than 45, we want column 8 in the lookup table.
If not, subtract 16 (presumably your age ranges start at 17?), divide that by 5 (bands of 5 years in your table) then add 1 and round up to the nearest whole number.
So, let’s say the age is 17:
17-16 = 1
1/5 + 1 = 1.2
round up to next whole number = 2 so we return a value from the second column of the table.

Make sense?

Reply | Quote

It does when you say it like that! Well put. I feel accomplished today! Thanks a bunch for the help. I may be back if I get stuck again, but I am very greatful for the help.

Reply | Quote

Glad to help. 🙂

Reply | Quote

can you possibly email me this excel document so that I am able to get an idea of how to build it. I am in the same boat as you are. Thanks. brandonpaulroy@gmail.com

Reply | Quote