Formating a Spreadsheet for Dummies

[tomyhead]

I 2 quick ?'s

1. What would be the best way to input physical fitness scores for a test on a spreadsheet. Let me explain....100 crunches=100 points, 20 pull ups=100points and under a 17 minute run=100points for a total of 300 points for a perfect score.....can I do a formula to breakdown everyones score by just inputing the criteria? Or maybe importing it from an outside source?

2. I have running scores for various activities, but I want them to be reflected on the first spreadsheet as an overview.....how can I link the totals of certain cells on other sheets to the overview sheet??

Any help is appreciated!!

[rbrhodes]

Hi and welcome!

I think your questions need a little more information, e.g. how will the proposed sheet be laid out?

I suggest you mock up a sheet showing what input would look like and the results required. Post it here and you'll get an answer for sure!

PS To post a sheet look in the box below where you're typing in your reply. Click on " Manage attachments" and follow the prompts.

Cheers,

dr

[royUK]

Check these free examples, look at the Summary.xls example

[tomyhead]

I 2 quick ?'s

1. What would be the best way to input physical fitness scores for a test on a spreadsheet. Let me explain....100 crunches=100 points, 20 pull ups=100points and under a 17 minute run=100points for a total of 300 points for a perfect score.....can I do a formula to breakdown everyones score by just inputing the criteria? Or maybe importing it from an outside source?

2. I have running scores for various activities, but I want them to be reflected on the first spreadsheet as an overview.....how can I link the totals of certain cells on other sheets to the overview sheet??

Any help is appreciated!!

HERE YOU GO! THANKS

[royUK]

Your exmple doesn't show how you want to set uo a summary sheet

[Hoberz]

It would be a lot easier for us to understand if you explained the how the point system worked. You said "100 crunches=100 points, 20 pull ups=100points and under a 17 minute run=100points for a total of 300 points for a perfect score" Would one crunch = 1 point? Would 1 pull up = 5 points? Would would happen if someone ran past 17 mins, how many points do they lose for each second, 10 seconds, minutes or whatever you want it to be. Giving us the infomation will be good enough to compile the formulas you're looking for.

[rbrhodes]

Hi tomyhead,

Like Jack said, not quite enough explanation with your example for me to deduce what it needs to do.

Hoberz comment also valid (although in the attached example I just made stuff up!) we need your point system.

Your original question, however, says how to link cells on other sheets to the "summary" sheet. If the sheets are in the same workbook, simply type an equals sign in the summary sheet cell, click the tab of the other sheet, click the cell you want and press enter. That will link the cell. There are two cells in my example which are linked with that method.

As for a formula to express scores I assume you mean to calculate the scores of the crunches + pullups + the run. I'm putting crunches as one = 1 point, pullups are times five (because 20 = 100 points).

The run however must have a scale of times/points to be calculated, ie: less than/equal to 17 = 100 points, greater than 17 & less than 18 = 80 points, greater than 18 & less than 19 = 50 points, greater than 19 & less than 20 =10 points, over 20 mins = 0 points. I simply subtracted 20, then 30 then 40 points from each previous value.

Once these values are decided on you could use an "If" statement, or if it is more than maximum 7 conditions an Hlookup or Vlookup for results.

Here's an example I played with to see if we're on the right track... Sheet "Phys. Test" uses "time" formatting for the run, "Phys. Test (2)" uses numbers as minutes and seconds (seconds are converted to decimal equiv.) for the run scores.

Both use "If" statements for their calculations <gr>

Cheers,

dr

[tomyhead]

Thanks guys, it was helpful!! My point system for the entire sheet, meaning everything is 100 points (I have 6 categories). So, the physical test will be 25%, and so on until it gets to be 100%.
Also, each squads total points can only equal 25 (max) since that is the total for that particular category.

Sorry for the missing info....does that help?