Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Formating a Spreadsheet for Dummies

  1. #1
    Join Date
    11th December 2005
    Posts
    3

    Formating a Spreadsheet for Dummies

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: Formating a Spreadsheet for Dummies

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,190

    Re: Formating a Spreadsheet for Dummies

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

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  4. #4
    Join Date
    11th December 2005
    Posts
    3

    Re: Formating a Spreadsheet for Dummies

    Quote Originally Posted by 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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,190

    Re: Formating a Spreadsheet for Dummies

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

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  6. #6
    Join Date
    7th December 2005
    Posts
    34

    Re: Formating a Spreadsheet for Dummies

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: Formating a Spreadsheet for Dummies

    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    11th December 2005
    Posts
    3

    Re: Formating a Spreadsheet for Dummies

    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?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Getting to Know Excel's SUMPRODUCT and SUMIF Functions - For Dummies ...
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 19:48
  2. Import Data for Dummies
    By Cuda in forum EXCEL HELP
    Replies: 5
    Last Post: March 8th, 2006, 11:01
  3. Replies: 2
    Last Post: January 13th, 2006, 10:53

Bookmarks

Posting Permissions

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