Hi there!

In our school we do all of our GPA calculations for transcripts within FileMaker. I imported all of the marks from Excel. The calculation is something like:

If(GetAsNumber(Marks S1) < 40; "0"; If(GetAsNumber(Marks S1) >= 40 and GetAsNumber(Marks S1) <= 53; "0.25"; If(GetAsNumber(Marks S1) >= 54 and GetAsNumber(Marks S1) <= 56; "1.00"; If(GetAsNumber(Marks S1) >= 57 ...

etc.

I've noticed however that it is not calculating properly and the only way it does calculate properly is if there aren't any decimal places.

Why would having decimal places effect the calculation?

24.3 is less than 50 whether it be 24 or 24.3, but when it is 24.3 it doesn't recognize it at all!

Any help would be greatly appreciated!

Figured it out!

24.5 = 25

Although it shows as 25 when rounded, it is not rounding and therefore calculating it as less than 25.

Is there a way to automatically round numbers in FileMaker?

I'm not sure I'm following you, I think you meant [color:red].50 in your first post?

Try using Round(yourfiled, 2)

you can choose and number instead of 2 places, but that should get you started.

If I'm not understanding, please try and clarify.

HTH

Lee

I am not following this too closely either, but I do see a problem with your formula.

It provides no result for the case of Marks S1 = 53.5 (for example).

Try another approach, that is also much simpler:

Let (

n = GetAsNumber ( Marks S1 )

;

Case (

n < 40 ; "0.00" ;

n < 54 ; "0.25" ;

n < 57 ; "1.00" ;

...

)

)

Yes, that is what the problem is. The GPA calculation wasn't accurate because 73.3 is technically = to 73 but the calculation views it as greater than 73.

This is why I would like to have the numbers rounded so there are no decimals points and use the calculation without decimals.

I will try the round function above, and also, thank you for the great calculation that you offered. I will be using that as well!

Thank you all for your help!

Ok. I've tried this calculation and it still doesn't seem to work...

Let (n = (GetAsNumber(Round(ACOU Marks S1; 0)));

Case(

n < 40; "0";

n >= 40 and n <= 53; "0.25";

n >= 54 and n <= 56; "1.00";

n >= 57 and n <= 59; "1.33";

n >= 60 and n <= 62; "1.67";

n >= 63 and n <= 66; "2.00";

n >= 67 and n <= 69; "2.33";

n >= 70 and n <= 72; "2.67";

n >= 73 and n <= 76; "3.00";

n >= 77 and n <= 79; "3.33";

n >= 80 and n <= 84; "3.67";

n >= 85 and n <= 89; "4.00";

n >= 90 and n <= 100; "4.33"))

Ideas?

You don't need rounding. You need to set a SINGLE upper boundary for each result, so that anything BELOW the boundary will return the result. That's all you need to specify: the Case() function looks for the first condition that evaluates as true, and stops there.

Hi

you have to make the calc exactly as comment said you:

Let (n = (GetAsNumber(Acou marks s1));

Case(

n < 40; "0";

n <= 53; "0.25";

n <= 56; "1.00";

n <= 59; "1.33";

n <= 62; "1.67";

n <= 66; "2.00";

n <= 69; "2.33";

n <= 72; "2.67";

n <= 76; "3.00";

n <= 79; "3.33";

n <= 84; "3.67";

n <= 89; "4.00";

n <= 100; "4.33";

"")

)

"exactly as comment said" would look more like this:

Let (

n = GetAsNumber ( ACOU Marks S1 )

;

Case (

n < 40 ; "0.00" ;

n < 54 ; "0.25" ;

n < 57 ; "1.00" ;

n < 60 ; "1.33" ;

n < 63 ; "1.67" ;

n < 67 ; "2.00" ;

n < 70 ; "2.33" ;

n < 73 ; "2.67" ;

n < 77 ; "3.00" ;

n < 80 ; "3.33" ;

n < 85 ; "3.67" ;

n < 90 ; "4.00" ;

"4.33" )

)

Actually that formula won't work because for example, let's say the mark is 66.9.

It will run through the calculation and sees that 66.9 is < 67 therefore it will assign the value 2.00... however, 66.9 rounded is 70 and should be assigned 2.33...

So the final cal should be:

Let (n = (GetAsNumber(Round(Acou marks s1; 0)));

Case(

n < 40; "0.00";

n <= 53; "0.25";

n <= 56; "1.00";

n <= 59; "1.33";

n <= 62; "1.67";

n <= 66; "2.00";

n <= 69; "2.33";

n <= 72; "2.67";

n <= 76; "3.00";

n <= 79; "3.33";

n <= 84; "3.67";

n <= 89; "4.00";

"4.33"

)

)

If you want to use Rounding then you should round after the GetAsNumber. (why do you need GetAsNUmber?) So

n = Round(GetAsNumber(ACOU Marks S1); 0)

will produce an integer value for the Case testing.

66.9 rounded is 70 and should be assigned 2.33

I presume you mean 69.9? Well, you COULD round the input, but the way I see it, you're simply saying that the boundary between 2.00 and 2.33 is 69.5 instead of 70.

If you want to use Rounding then you should round after the GetAsNumber. (why do you need GetAsNUmber?)

Yes, SlimJim is right !

If "Acou marks s1" is a number, you can round it directly, so:

n = Round(Acou marks s1; 0)

I don't know why, but it still isn't working...

Using values with decimal points gives me a GPA of 1.13

Using the same values without decimals points gives me a GPA 2.12

??

Edited December 16, 200520 yr by Guest

Can you give us real data ?

... And what is the correct GPA ?

Sure: I've typed these numbers into my database:

68, 75, 85, 52, 97, 47, 89, 87, 92, 93, 85, 81, 94

the resulting GPA is 3.30

if I type the numbers as:

68.0013, 75, 84.89, 52.36, 97.1, 47, 88.6, 87, 92.2, 92.8, 85, 81.03, 94

the resulting GPA is: 3.12

Utilizing the equation provided by the users above the GPAs sometimes differ extremely, for example: 1.13 when it should be 2.12.

There is another equation that follows after in order to get the proper weighting and decipher the GPA however, which is all of the resulting GPA calculations added and divided by 22.

Edited December 16, 200520 yr by Guest

Nevermind! I've figured it out. The weightings weren't working properly and now they are!

I apologize for all of the trouble!

I appreciate your help so much, thank you!