a23m2000
asked on
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.
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
If isnull({Pre-Test}) or isnull({Post-Test}) Then
0
Else if {Pre-Test} > {Post-Test} Then
1
Else
0
ASKER
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.'
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.'
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.
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.
ASKER
If {COURSE_HISTORIES.rt_ch_gr ade} > {COURSE_HISTORIES.rt_ch_te st} then '1' else '0'
I see what you are saying about the text part, I am looking at it now.
I see what you are saying about the text part, I am looking at it now.
Just take the single-quotes off your formula & you should be fine:
If {COURSE_HISTORIES.rt_ch_gr ade} > {COURSE_HISTORIES.rt_ch_te st} then 1 else 0
If {COURSE_HISTORIES.rt_ch_gr
ASKER
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
thanks again for you help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey I really appriciate your help, I will award your points, thanks again buddy
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.