This topic is 5761 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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!

##### Share on other sites

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?

##### Share on other sites

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

##### Share on other sites

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" ;

...

)

)

##### Share on other sites

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!

##### Share on other sites

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?

##### Share on other sites

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.

##### Share on other sites

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";

"")

)

##### Share on other sites

"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" )

)

##### Share on other sites

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...

##### Share on other sites

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"

)

)

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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)

##### Share on other sites

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 by Guest
##### Share on other sites

Can you give us real data ?

... And what is the correct GPA ?

##### Share on other sites

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 by Guest
##### Share on other sites

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!

##### Share on other sites

This topic is 5761 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account