Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Calculation (Excel2000)

    Hi All got great help last time here is another one for you. I have in a column of cells (D22222) my students grade and class (example 4M,. standing for grade 4 and the teachers last initial, I have two classes of each grade level so I have 4M and a 4W). In another column of cells say (G2:G2222) I have the students results of a test, either, Above Ave, Healthy Zone, or Below Ave. I am trying to calculated a % for each of the results (Above, Health, and Below), for each grade level. I was able to figure out the whole school, just not broken out by grade. Any idea's? Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Calculation (Excel2000)

    A pivot table can probably do what you want. Insert a column to extract the grade level from column D, then create a pivot table based on your data. Drag the grade level to the row area, the result to the column area, and any field to the data area; change the summary function to Count if necessary. Double click the field in the data area, and click Options>>. You can specify how the count is displayed.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula Calculation (Excel2000)

    If you separate the grades out into column E, and the Health codes in column G are A, H, and B, you could also use an array formula like this to return the results for all students. Do not enter the {} curly braces, array formulas are entered using Ctrl-Shift-Enter:

    Above Average {=SUM(($G$2:$G$2222="A")*$E$2:$E$2222)/COUNTIF($G$2:$G$2222,"A")}
    Healthy {=SUM(($G$2:$G$2222="H")*$E$2:$E$2222)/COUNTIF($G$2:$G$2222,"H")}
    Below Average {=SUM(($G$2:$G$2222="B")*$E$2:$E$2222)/COUNTIF($G$2:$G$2222,"B")}

    but a pivot table will give you more flexibility
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula Calculation (Excel2000)

    Something like the attached, perhaps?

    Grade scores are calculated in Columns K&L, whilst class scores are calculated in Columns N&O & Q&R. The Grade score is set to carry over to the claases, so only the class suffixes (might) need to be updated.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Calculation (Excel2000)

    What if my data is mixed with other grades and on a different worksheet?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Calculation (Excel2000)

    Can you give us an idea of what you are working with, for example attach a small demo workbook (with dummy data), the way macropod did in his reply.

  7. #7
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Calculation (Excel2000)

    Here is an example of what I'm trying to do. I have five different test each on a worksheet. I have give just one of the five tests (BMI). On another sheet I have a results table where I would like the % of students in each grade level 1-5 to show up. What is the % of students in grade 1 in the Above ave, healthy zone or below ave. etc.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Calculation (Excel2000)

    It's rather difficult to work with the attachment since it relies heavily on links to other workbooks that are not available to us. I haven't attempted to work out the percentages, but here is a formula to calculate the number of below average BMI in grade 1:

    =SUMPRODUCT((LEFT(BMI!$D$2:$D$24,1)=LEFT(Results!C $1,1))*(N(BMI!$I$2:$I$24="Below Ave")))

    and the total below average BMI:

    =SUMPRODUCT(N(BMI!$I$2:$I$24="Below Ave"))

    You can probably replace "Below Ave" by some kind of lookup, but that would involve external links again.

    This could also be done with SUM instead of SUMPRODUCT, the formulas become array formulas (confirm with Ctrl+Shift+Enter).

  9. #9
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Calculation (Excel2000)

    Sorry Hans,
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> Didn't mean to get you bad data. Try this example sheet.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Calculation (Excel2000)

    Does the attached do what you want?

  11. #11
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Calculation (Excel2000)

    YES!!! THank YOU Thank YOU!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  12. #12
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Calculation (Excel2000)

    Hanz,
    I've tried to use the formula that you created in the results table on mine and I'm getting a 0% in for a result. I have data in but something is not working. I'd attach my sheet that I'm using but even zip it is over 110k. The problem I think is that on my sheet, I have referenced other worksheets in the file. Example, I have one worksheet that has all my student information IE Name, Age, Homeroom, Birthdate, etc. I then referenece that on the other worksheets, IE BMI. Would that cause the problem?? Thanks for a look ahead of time and all of your help in my matters. The help is truely appriecated.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Calculation (Excel2000)

    Try to remove everything not essential to this problem from a copy of the workbook. You need to leave only one or two records that display 0% where they shouldn't. That should get the zipped file below 100 KB.

  14. #14
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Calculation (Excel2000)

    Hans,
    Let's try this. See what can be done. And thanks again.

    Chris

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Calculation (Excel2000)

    There are two problems. In the first place, your formulas include rows without data, for which the fitness zone is an error value #DIV/0. You must either clear column I in the BMI sheet for rows without data, or exclude those rows from the formulas on the Results sheet (use D266 and I2:I66 instead of D269 and I2:I69)

    In the second place, as noted in <post#=434125>post 434125</post#>, if you use SUM, the formulas must be array formulas. Double click cell C2, then confirm with Ctrl+Shift+Enter to change the formula into an array formula. You can now fill down to C4, then fill right to G2:G4.

Posting Permissions

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