How to sum a formula field

I am trying to sum a formula field.  I have a formula called I/D and it uses an if statement to to see if a pre-test grade is higher than the post test.  If it is higher, it gives it a 1 else 0.  The sum is having problems with the I/D field saying 'A number field or currency amount field is required here.'  Any idea's would help very much.
a23m2000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

frodomanCommented:
Sounds like a problem with your formula.  It should look like this:

If {Pre-Test} > {Post-Test} Then
   1
Else
   0

And both pre-test and post-test must be numeric values, of course.  If there are null values in either pre or post that will cause you a problem also.

If this doesn't help, please post your I/D formula.
0
frodomanCommented:
By the way, if you do have null values you'd address it this way:

If isnull({Pre-Test}) or isnull({Post-Test}) Then
   0
Else if {Pre-Test} > {Post-Test} Then
   1
Else
   0
0
a23m2000Author Commented:
There isn't a problem with the I/D field in itself it spits out the number 1 or 0 based upon the >< of the grades...where I am running into problems is when I am trying to create another field to sum the I/D field....example...

                Pretest         Posttest        I/D
Student 1       50              45              0
Student 2       50              95              1
Student 3       50              45              0

Class Average:  50              62



# of participants increasing score              2                <------------------ This is where I am having the problem.
# of participants decreasing score      1                       <------------------ Here as well.


So the I/D field is good, however when I try and create a formula for the # of participants increasing and decreasing I have the problem.  Hopefully this helps clarify the problem.

Sum ({@I/D}) is what I'm doing for the # of participants increasing score.  However I get the error 'A number field or currency amount field is required here.'
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

frodomanCommented:
I understand what you're saying but I suspect that the number you're seeing in the I/D column isn't really a number...  

If I create a summary formula as: Sum ({@I/D}) I get the correct result.  You're being told that a number is required here which means that your formula I/D does not always return a number.  Most likely it is either returning a text "1" or "0" or is sometimes returning a null value.

Please post your I/D formula and I'll take a look.
0
a23m2000Author Commented:
If {COURSE_HISTORIES.rt_ch_grade} > {COURSE_HISTORIES.rt_ch_test} then '1' else '0'

I see what you are saying about the text part, I am looking at it now.
0
frodomanCommented:
Just take the single-quotes off your formula & you should be fine:

   If {COURSE_HISTORIES.rt_ch_grade} > {COURSE_HISTORIES.rt_ch_test} then 1 else 0

0
a23m2000Author Commented:
great that does it...one more question...on the I/D field there are 1's and 0's and can I use a formula or summary to find the number of particiants decreasing score, or the number of 0's on the I/D field....thanks...I will award you the points after this question...

thanks again for you help
0
frodomanCommented:
Easiest answer based on what you have is another formula:

count({@I/D}) - sum({@I/D})

This just counts the entries and subtracts the sum (which is the same as subtracting the number of '1's).

If you want to get more adventurous, you can look into using 'count' with conditions.  

Another option would be to have another version of your I/D field with the reversed criteria (so the 1's & 0's are reversed) and just create another sum on those.

I'd go with the formula above in this case, but remember the other options for another time when you have more than 2 possible outcomes...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
a23m2000Author Commented:
Hey I really appriciate your help, I will award your points, thanks again buddy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.