10 Replies Latest reply on May 27, 2009 5:56 AM by Sendnot

# Help with calculation

### Title

Help with calculation

### Post

I've been trying to write a fairly simple calculation, except that it doesn't work as I'd expect. What I'm trying to do is to calculate a "mid-point" grade; that is, if an average grade is a 1.35, then the "mid-point" grade is a 1.70, a 1.70 becomes a 1.85, the 1.85 becomes a 2.00, etc... on up to 4.01.

Below is what I've written:

Case(Final Average_ALPHA ≥ 1.35 and Final Average_ALPHA ≤ 1.7;"1.70";Final Average_ALPHA ≥ 1.70 and Final Average_ALPHA ≤ 1.85 ;"1.85";Final Average_ALPHA ≥1.85 and Final Average_ALPHA ≤ 2;"2.00" ;Final Average_ALPHA ≥2.15 and Final Average_ALPHA ≤ 2.3;"2.30";Final Average_ALPHA ≥2.30 and Final Average_ALPHA ≤ 2.5;"2.50";Final Average_ALPHA ≥2.50 and Final Average_ALPHA ≤ 2.7;"2.70";Final Average_ALPHA ≥2.70 and Final Average_ALPHA ≤ 2.85;"2.85";Final Average_ALPHA ≥2.85 and Final Average_ALPHA ≤ 3;"3.00";Final Average_ALPHA ≥3.00 and Final Average_ALPHA ≤ 3.15;"3.15";Final Average_ALPHA ≥ 3.15 and Final Average_ALPHA  ≤  3.3;"3.30"; Final Average_ALPHA ≥ 3.3 and Final Average_ALPHA  ≤  3.5; "3.50"; Final Average_ALPHA  ≥ 3.5 and Final Average_ALPHA  ≤ 3.7; "3.70"; Final Average_ALPHA  ≥ 3.85 and Final Average_ALPHA  ≤ 3.98; "4.00"; Final Average_ALPHA  ≥ 3.99; "4.01")

From my point of view, it should work, but a 1.70 produces a 1.70 when I'd like to see 2.0 instead. Please help!

Thanks-:smileyindifferent:

• ###### 1. Re: Help with calculation

You are using less than or equal to and then you are using greater than and equal to.

Final Average_ALPHA ≤ 1.7;"1.70";Final Average_ALPHA ≥ 1.70

Therefore, when its 1.7, it will display 1.7, since it meets the criteria of the first check that you have.

Second, I am not sure what you are talkign about in a mid-point grade. Your calc can be simplified a lot, but I dont know how to help you because I dont understand what you are trying to get. You said that when the avg grade is 1.7, you expect a result of 2, yet you check for it to be 1.7 and 1.85.

Last, you dont need quotes for numbers.

• ###### 2. Re: Help with calculation

I'm afraid that your calculation does not translate to what you say you want. Example ...  1.70 should change to 2.00 but your calculation says:

Final Average_ALPHA ≥ 1.35 and Final Average_ALPHA ≤ 1.7;"1.70";

Final Average_ALPHA ≥ 1.70 and Final Average_ALPHA ≤ 1.85 ;"1.85";

Final Average_ALPHA ≥1.85 and Final Average_ALPHA ≤ 2;"2.00"

According to your calculation, 1.70 would fit into the first category AND the second and, since Case() stops evaluating at the first true, it will also produce 1.70.

Can you instead give us examples of the number and the translated amount you want, such as if Final Average_ALPHA is:

27 ... it should be blank

1.35 ... it should be 1.70

1.70 ... it should be ... etc

HI JOHN!!!! :smileyvery-happy:

• ###### 3. Re: Help with calculation

LaRetta wrote:

Better yet: give us the rules.

• ###### 4. Re: Help with calculation

First, thanks for all the response! Calculations have always been a problem for me, so I'm happy when someone helps me 'see the light', so to speak.

A mid-point grade (as I understand it) is a number that is 'rounded up' from a "Quality Point Designation"; essentially, if a student gets a certain Quality Point Designation, then a 'Mid-Point' grade is awarded. The scale is as follows:

1.00 to 1.34 = 1.35

1.35 to 1.69 = 1.70

1.70 to 1.84 = 2.00

2.00 to 2.14 = 2.15

2.15 to 2.29 = 2.30

2.30 to 2.49 = 2.50

2.50 to 2.69 = 2.70

2.70 to 2.84 = 2.85

2.85 to 2.99 = 3.00

3.00 to 3.14 = 3.15

3.15 to 3.29 = 3.30

3.30 to 3.49 = 3.50

3.50 to 3.69 = 3.70

3.70 to 3.84 = 3.85

3.85 to 3.99 = 4.00

4.00 and above = 4.01

• ###### 5. Re: Help with calculation

And you realize you have a gap?

1.70 to 1.84 = 2.00

2.00 to 2.14 = 2.15

What do we do with 1.85 through 1.99?

• ###### 6. Re: Help with calculation

Try something like:

Case (
Grade ≥ 4 ; 4.01 ;

Grade ≥ 3.85 ; 4 ;
Grade ≥ 3.7 ; 3.85 ;
Grade ≥ 3.5 ; 3.7 ;
Grade ≥ 3.3 ; 3.5 ;
Grade ≥ 3.15 ; 3.3 ;
Grade ≥ 3 ; 3.15 ;

Grade ≥ 2.85 ; 3 ;
Grade ≥ 2.7 ; 2.85 ;
Grade ≥ 2.5 ; 2.7 ;
Grade ≥ 2.3 ; 2.5 ;
Grade ≥ 2.15 ; 2.3 ;
Grade ≥ 2 ; 2.15 ;

Grade ≥ 1.7 ; 2 ;
Grade ≥ 1.35 ; 1.7 ;
Grade ≥ 1 ; 1.35 ;
)

Note:

I have taken into account your lower boundaries only. As LaRetta pointed out, you have a gap.I have also assumed that grades below 1 are unchanged.

• ###### 7. Re: Help with calculation

LaRetta,

Doh! You are SO right! Thanks for catching that... I guess that's the problem with multi-tasking...

Here's how it should be:

1.00 to 1.34 = 1.35

1.35 to 1.69 = 1.70

1.70 to 1.84 = 1.85

1.85 to 1.99 = 2.00

2.00 to 2.14 = 2.15

2.15 to 2.29 = 2.30

2.30 to 2.49 = 2.50

2.50 to 2.69 = 2.70

2.70 to 2.84 = 2.85

2.85 to 2.99 = 3.00

3.00 to 3.14 = 3.15

3.15 to 3.29 = 3.30

3.30 to 3.49 = 3.50

3.50 to 3.69 = 3.70

3.70 to 3.84 = 3.85

3.85 to 3.99 = 4.00

4.00 and above = 4.01

That looks better

• ###### 8. Re: Help with calculation

You are spot-on, Comment, but I wrote it this way (and I wonder if it isn't clearer in the meaning this way):

Let ( field = final AVERAGE_ALPHA ;

Case (
field < 1 ; field ;
field < 1.35  ; 1.35 ;
field <  1.7 ; 170 ;
field <  2 ; 2 ;
field <  2.15 ; 2.15 ;
field <  2.3 ; 2.3 ;
field <  2.5 ; 2.5 ;
field <  2.7 ; 2.7 ;
field < 2.85 ; 2.85 ;
field < 3 ; 3 ;
field < 3.15 ; 3.15 ;
field < 3.3 ; 3.3 ;
field < 3.5 ; 3.5 ;
field < 3.7 ; 3.7 ;
field < 3.85 ; 3.85 ;
field < 4 ; 4 ;
4.01
) // end case
) // end let

Changed the final default result.

• ###### 9. Re: Help with calculation
Never mind ... my calc may SEEM easier to understand and read (which maybe is just because *I* created it, ha ha) but it is NOT the more logical in this instance because the request "is a number that is 'rounded up' from a "Quality Point Designation"; essentially..." and your calculation clearly shows the 'round up' effect.
• ###### 10. Re: Help with calculation
Thanks to all that helped me. Mostly the problem was being rushed and losing attention to detail. No wonder it wouldn't work! Thanks again for pointing out the flaws in my formulae- it's due to the great people here that I was able to finish this project.