1. ## 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. ## 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. ## 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

4. ## 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

5. ## Re: Formula Calculation (Excel2000)

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

6. ## 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. ## 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. ## 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. ## 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. ## Re: Formula Calculation (Excel2000)

Does the attached do what you want?

11. ## 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. ## 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. ## 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. ## Re: Formula Calculation (Excel2000)

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

Chris

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