Results 1 to 15 of 15
Thread: Formula Calculation (Excel2000)

20040207, 01:57 #1
 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

20040207, 02:10 #2
 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.

20040207, 18:55 #3
 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 CtrlShiftEnter:
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 flexibilityJohn ... I float in liquid gardens
UTC 7ąDS

20040209, 04:01 #4
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,069
 Thanks
 2
 Thanked 420 Times in 349 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.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20041207, 15:31 #5
 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?

20041207, 15:44 #6
 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.

20041208, 13:26 #7
 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 15 to show up. What is the % of students in grade 1 in the Above ave, healthy zone or below ave. etc.

20041208, 13:50 #8
 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).

20041208, 17:28 #9
 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.

20041208, 17:51 #10
 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?

20041208, 18:07 #11
 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>

20041213, 20:54 #12
 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.

20041213, 20:58 #13
 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.

20041215, 13:22 #14
 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

20041215, 13:39 #15
 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.