Coverting a score on a summary sheet

[lesterd]

I have a score field for 800 people. I take that score and average it on a summary sheet. I need to show the average score and convert that score to another field score on the summary sheet. For example let's say the average of all 800 scores is 227.521 and when I get that number it has to be converted to 56. If the average was 278.220 the number to convert to is 57.

I tried using a case statement but the average comes in with a number and decimal number. Is there a way to write a statement or statements that will cover a various number of scores such as:

227 result 56

228 result 57

229 result 59 etc.

The problem arises with the decimal. Can I use greater than and less than? I think I have a problem when I write New score=case(field_score="227","56") etc. Filemaker looks for the exact score 227 and I might get 227.445 as an average score.

I have to convert numbers that will result in an possible answer 1 through 99. I showed you example of 56, 57, and 58 above.

Hope this is clear

Lester

[Wendy T]

hi Lester,

the Int function will give you only the whole (integer) part of a number. So from your example,

Int(227.521) will result in 227

Int(278.220) will result in 278

This is, in effect, rounding down. If you want proper rounding (ie fractions over .5 rounding up), use the Round function instead.

cheers,

Wendy

[lesterd]

Thank you for your prompt reply.

I am still a little unsure about the solution. Do I write the calculation as:

New score=case(rnd(field_score)="227","56",rnd(field_score)="228","57"rnd(field_score)="229","59") etc.

Filemaker looks for the rounded score 227 and replaces it to 56?

I have to convert numbers that will result in an possible answer 1 through 99. I showed you example of 56, 57, and 58 above.

[Wendy T]

lesterd said:

I am still a little unsure about the solution. Do I write the calculation as:

New score=case(rnd(field_score)="227","56",rnd(field_score)="228","57"rnd(field_score)="229","59") etc.

Filemaker looks for the rounded score 227 and replaces it to 56?

Hi Lester

Yes, the partial calculation you've written will be what you need. It will, however, be a very long calculation. Is there further logic to how the number is converted from the raw score to the new score? From the examples you've given, it looks like there is a formula behind the conversion. A simple example -- if your raw score is out of 385 and you are converting to a percentage, use new_score=100*field_score/385. No rounding or case function is needed.

cheers,

Wendy