Announcement

Collapse
No announcement yet.

Formating a Spreadsheet for Dummies

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http://www.ExcelVBA.joellerabu.com

    Comment


    • #3
      Re: Formating a Spreadsheet for Dummies

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

      Roy

      New users should read the Forum Rules before posting

      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.

      Comment


      • #4
        Re: Formating a Spreadsheet for Dummies

        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

        Comment


        • #5
          Re: Formating a Spreadsheet for Dummies

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

          Roy

          New users should read the Forum Rules before posting

          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.

          Comment


          • #6
            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.

            Comment


            • #7
              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
              Cheers,

              dr

              "Questions, help and advice for free, small projects by donation. large projects by quote"

              http://www.ExcelVBA.joellerabu.com

              Comment


              • #8


                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?

                Comment

                Working...
                X