Finding an Average using a formula

[agreen10]

I have a set of data
eng mat fren sci totalScore avescore
1 0 3.2 2.7 1.9
2 1.2 3.6 2.8 0

the total score is all the grades added for the subjects. however, the average score takes the grades for those subjects that had a grade and finds the average. We do not include the subject with no grade. What is the best way for me to create a formula to calculate the average score elimating those subjects without a grade?

Thanks for any assistance I can receive.

Ali

 

[synapsevampire]

Looks like you'll have to hardcode:

Depends upon your field layout, post specifics.

This is sort of what you might do if it's allo in different fields:

whileprintingrecords;
numbervar Subjects;
numbervar Scores;
Numbervar X;
for x:= 1 to 4 do(
if {table.eng} <> 0 then
(
Subjects := Subjects+1;
if {table.mat} <> 0 then
Subjects := Subjects+1;
if {table.fren} <> 0 then
Subjects := Subjects+1;
if {table.sci} <> 0 then
Subjects := Subjects+1;
etc...

But I hate to go down the path without you taking the time to post proper requirements. Of course if you add in the version of your software, which should be included with any post in any forum, and the database being used, we may be able to go a different route.

So in general successful posts include example data and expected output, not an example of something in a report that doesn't work.

-k

 

[agreen10]

the version I am using is XI and that is a sample of the data that came out.

thanks.

 

[synapsevampire]

Ahhh, OK, then my theory should hold fine.

That allows for counting the scores that aren't zero, then you create another set to add the scores, then divide them.

-k

 

[agreen10]

Can you please let me know if I did this right? i think I am missing something with the syntax.

whileprintingrecords;
numbervar subjects;
numbervar scores
numbervar X;

for X :=1 to 10
DO(
if {bssp_grades.sem1_eng_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem1_foreign_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem1_math_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem1_sci_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem1_social_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem2_eng_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem2_foreign_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem2_math_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem2_sci_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem2_social_score} <> 0 then subjects := subjects+1);

 

[rosemaryl]

Dear agreen10,

SV's solution will work well, however another option would be the following:

numbervar div := 0;

//accumulate divisor create a div line for each subject!
div := if (isnull({Table.Course1}) or {Table.Course1} = 0)
then div + 0 else div + 1;

div := if (isnull({Table.Course2}) or {Table.Course2} = 0)
then div + 0 else div + 1;

div :=if (isnull({Table.Course3}) or {Table.Course3} = 0)
then div + 0 else div + 1;

div :=if (isnull({Table.Course4}) or {Table.Course4} = 0)
then div + 0 else div + 1;

//div now has the value needed to divide by the elements that do not have 0

// let's do the calc

If div = 0
then 0
else
(
{Table.Course1}
+ {Table.Course2}
+ {Table.Course3}
+ {Table.Course4}
)
/
div

I added checking for null values because I don't know your data. If you are a novice, this formula might be easier to understand then the for loop that SV suggested, which once again will also work nicely.

The end result is the same, you initialize a varriable and increment it by one each time you have a score greater than 0. At the end you make sure that the divisor is not equal to 0 (can't divide by that or you will get a divide by 0 error) and then divide the sum of the score by the divisor.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[synapsevampire]

What's the difference between semi1 and semi2?

Your theory may be fine, however as we can see now, the data dumped does NOT match that in the database.

whileprintingrecords;
numbervar subjects;
numbervar scores
numbervar X;
for X :=1 to 10
DO(
if {bssp_grades.sem1_eng_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem1_foreign_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem1_math_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem1_sci_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem1_social_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem2_eng_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem2_foreign_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem2_math_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem2_sci_score} <> 0 then subjects := subjects+1;
if {bssp_grades.sem2_social_score} <> 0 then subjects := subjects+1);
);
Subjects

-k

 

[agreen10]

Thanks Rosemaryl for your suggestion and I am still missing a tweak. Here is what I have thus far:

numbervar div := 0;

div := if (isnull({bssp_grades.sem1_eng_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_foreign_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_math_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_sci_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_social_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_eng_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_foreign_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_math_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_sci_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_social_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

if div = 0 then 0 else
({bssp_grades.sem1_eng_score}+
{bssp_grades.sem1_foreign_score}+
{bssp_grades.sem1_math_score}+
{bssp_grades.sem1_sci_score}+
{bssp_grades.sem1_social_score}+
{bssp_grades.sem2_eng_score}+
{bssp_grades.sem2_foreign_score}+
{bssp_grades.sem2_math_score}+
{bssp_grades.sem2_sci_score}+
{bssp_grades.sem2_social_score})/div

it is still dividing by all 10 although some course have a score of 0.

here is a sample of the data


sem1_en sem1_ sem1 sem1_ sem1_fsem2_ sem2 sem2_ sem2_ sem2_fTota GPA1

Allison Michael 4.00 4.00 3.70 0.00 4.00 3.30 3.30 3.70 0.00 4.00 30. 3.00

Clouse M. Violet 3.00 1.70 2.30 2.00 0.00 3.00 2.00 2.70 2.00 0.00 18. 1.87

Contreras Ana 3.00 2.70 0.00 0.00 0.00 3.30 2.70 0.00 0.00 0.00 11. 1.17

 

[rosemaryl]

Dear Agreen,

I didn't mean to step on sv's post ...

It is hard to help, because in one post you showed it grouped by Semester and then in the next it is not.

Here is a suggestion:

I tested with my data and div correctly reflects the number of records to divide by.

So, let's get an example of what div is returning.

In your current formula, comment out everything in the formula after the last set of div and then add to the end of the formula div so that it looks like this:

numbervar div := 0;

div := if (isnull({bssp_grades.sem1_eng_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_foreign_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_math_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_sci_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_social_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_eng_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_foreign_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_math_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_sci_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_social_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

//if div = 0 then 0 else
//({bssp_grades.sem1_eng_score}+
//{bssp_grades.sem1_foreign_score}+
//{bssp_grades.sem1_math_score}+
//{bssp_grades.sem1_sci_score}+
//{bssp_grades.sem1_social_score}+
//{bssp_grades.sem2_eng_score}+
//{bssp_grades.sem2_foreign_score}+
//{bssp_grades.sem2_math_score}+
//{bssp_grades.sem2_sci_score}+
//{bssp_grades.sem2_social_score})/div
//
div

What does div return for say the first five records and why do you think the calculation is wrong.

regards,

ro



numbervar div := 0;

div := if (isnull({bssp_grades.sem1_eng_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_foreign_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_math_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_sci_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_social_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_eng_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_foreign_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_math_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_sci_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_social_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div

What does div return for a variety of lines.

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[agreen10]

Div returns 10.

 

[rosemaryl]

Dear agreen10,

That doesn't make sense to me as I have tested this repeatedly and I get results that count the number of items that are non-zero.

You don't by chance have the exact same code in multiple formulas do you? If so, please make sure to change numbervar div to local numbervar div and see if that makes a difference.

Please, post the design of your report and where you are placing the fields and sample output with the formula.

Thank you,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[agreen10]

Here is a sample of the data in no special format. I hope you can follow it:

sem1_en sem1_ sem1 sem1_ sem1_fsem2_ sem2 sem2_ sem2_ sem2_fTota DIV

Allison Michael 4.00 4.00 3.70 0.00 4.00 3.30 3.30 3.70 0.00 4.00 30. 10.00

Clouse M. Violet 3.00 1.70 2.30 2.00 0.00 3.00 2.00 2.70 2.00 0.00 18. 10.00

Contreras Ana 3.00 2.70 0.00 0.00 0.00 3.30 2.70 0.00 0.00 0.00 11. 10.00

Cooper Ryan 1.00 0.10 0.00 0.00 0.00 1.30 0.10 0.00 0.10 0.00 2.60 10.00

Day Todd Max 4.00 2.00 3.70 3.00 0.00 3.00 1.00 3.00 2.00 0.00 21. 10.00

Faaumu Alexis 1.30 2.70 1.70 0.00 2.70 1.70 0.10 1.00 0.00 2.00 13. 10.00

Gonzalez Dora 2.70 1.00 2.20 0.00 0.00 3.00 1.30 0.00 0.00 0.00 10. 10.00

Jones Devon 2.30 0.00 0.00 3.70 0.00 2.30 0.00 0.00 0.00 0.00 8.30 10.00

Ko Dasha 4.00 2.70 3.30 3.00 3.30 4.00 2.70 3.30 0.00 3.30 29. 10.00

Lackey Joseph 1.30 0.00 1.70 1.30 0.00 2.00 0.00 1.00 2.00 0.00 9.30 10.00

Ojanen J. Carin 3.70 0.00 0.00 1.30 0.00 3.30 0.00 0.00 1.00 0.00 9.30 10.00

Perez William Jace 1.00 0.00 1.00 0.10 0.00 1.00 0.00 0.10 0.10 0.00 3.30 10.00

Pleier Erica 3.40 3.40 2.40 0.00 3.20 0.00 0.00 0.00 0.00 0.00 12. 10.00

Reyes Michelle 4.00 4.00 4.00 3.70 0.00 4.00 4.00 4.00 0.00 0.00 27. 10.00

Romanello R. 0.00 0.00 0.00 0.00 0.00 2.00 0.00 2.00 0.00 0.00 4.00 0.00

Salazar Eduardo 1.70 0.00 0.00 1.30 0.00 1.00 0.00 0.00 0.00 0.00 4.00 10.00

Simmons Renee 2.00 2.00 2.00 1.30 0.00 3.00 0.00 2.70 0.00 0.00 13. 10.00


The formula is exactly as you sent above and is not used anywhere else.

 

[rosemaryl]

Dear agreen10,

Please copy and paste the formula in exactly. I notice that for Romanello R that 0 is reported when what should be shown is 3 so there is a variance here. I think you may have referenced the same field multiple times and that is why the numbers are incorrect.

Ah, yes, I just looked at your formula and notice the second field referenced (after the or) on each line is exactly the same: {bssp_grades.sem1_eng_score}.

There must be field agreement on the first field checked and the second field checked.

Correct your formula and the results will be correct.

regards,

ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[agreen10]

here is my formula:


numbervar div := 0;

div := if (isnull({bssp_grades.sem1_eng_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_foreign_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_math_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_sci_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem1_social_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_eng_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_foreign_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_math_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_sci_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;

div := if (isnull({bssp_grades.sem2_social_score}) or {bssp_grades.sem1_eng_score}= 0)
then div + 0 else div +1;


if div = 0 then 0 else
({bssp_grades.sem1_eng_score}+{bssp_grades.sem1_foreign_score}+{bssp_grades.sem1_math_score}+{bssp_grades.sem1_sci_score}+{bssp_grades.sem1_social_score}+{bssp_grades.sem2_eng_score}+{bssp_grades.sem2_foreign_score}+{bssp_grades.sem2_math_score}+{bssp_grades.sem2_sci_score}+{bssp_grades.sem2_social_score})
/ div

 

[rosemaryl]

Dear agreen10,

As previously stated, your formula contains an error. Please go back and reread my prior post so that you can correct your formula. If you carefully look at your formula you will see that it is wrong.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[agreen10]

I got it. Silly me. Thank you very much.