Solved

How to sum a formula field

Posted on 2003-11-12
9
1,878 Views
Last Modified: 2011-04-14
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.
0
Comment
Question by:a23m2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 9733157
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9733175
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
 

Author Comment

by:a23m2000
ID: 9733264
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 42

Expert Comment

by:frodoman
ID: 9733389
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
 

Author Comment

by:a23m2000
ID: 9733447
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
 
LVL 42

Expert Comment

by:frodoman
ID: 9733489
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
 

Author Comment

by:a23m2000
ID: 9733552
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
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 9733622
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
 

Author Comment

by:a23m2000
ID: 9733660
Hey I really appriciate your help, I will award your points, thanks again buddy
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question