Shortest way, no Case()

[jrRaid]

What is the shortest way to make following calc, but I do not want to use a giant Case()like

Case(

result >97 and result <100; A+;

result >93 and result <96; A;

etc.

Values are:

Letter Result (from - to)

A+ 97-100

A 93-96

A- 90-92

B+ 87-89

B 83-86

B- 80-82

C+ 77-79

C 73-76

C- 70-72

D+ 67-69

D 63-66

D- 60-62

F 0-59

TIA

[David Jondreau]

Will these ranges ever change? If so, I'd say a table with a record for each Letter Grade and fields for Start Number Grade, End Number Grade, and Letter Grade and a relationship with a lookup.

Otherwise, keep in mind that a Case() statement evaluates in order of the tests. So

Case(

result>=97; "A+";

result >= 93; "A";

result >=90; "A-";

etc

result >=60; "D-";

"F")

Could someone else come up with a shorter calc? Probably, but the calc would be much more confusing to read. I wouldn't consider this a "giant" Case() statement even if you had to state the upper limits, and its clarity is ensured.

[jrRaid]

Thanks DJ and no the ranges will not change.

I think I will go for an extra table.

I do not like to hardcode data in a formula, or at least try to avoid it if possible.

JR

[The Shadow]

This is pretty small, but it encodes the knowledge of the table within it:

Let( [ grd = Middle( "FFFFFDCBAA"; Div(n;10); 1 ); rem=If(n=100;9;Mod(n;10))];

  grd & If (grd<>"F"; Case(rem>=7; "+"; rem <= 2; "-"))

)

[jrRaid]

That is what I was looking for.

Next is find out the why and how.

Thanks

[jrRaid]

I was very happy with the solution offered above.

But....

Now they want the other way round...

The input will be a letter (with or without +/-) and the result should be the given range.

Wonder if that is possible without hardcoding everything in a Case()

A+ 97-100

A 93-96

A- 90-92

B+ 87-89

B 83-86

B- 80-82

C+ 77-79

C 73-76

C- 70-72

D+ 67-69

D 63-66

D- 60-62

F 0-59

To make it clear the number range is one value in a numberfield.

TIA

Edited April 23, 2008 by Guest

[mr_vodka]

You should go with an extra table as DJ said.

[comment]

Wonder if that is possible without hardcoding everything in a Case()

I wonder what your objection is. If you don't want to hardcode data into a calculation formula, then what does it matter if the calculation uses Case() or not?

The fact is that the steps given do not follow a logic that can be replicated by a simple calculation (if they did, then A+ would go only up to 99, for example). Such situations are best handled by the Case() function - IF handled by calculation at all.

To make it clear the number range is one value in a numberfield.

I don't think that's a good choice - "97-100" is definitely NOT a number.

[jrRaid]

I'm not used to develop in FileMaker.

My development environment is Sentences, based on the Associative Model of Data.

Someone in the ivory tower is thinking Sentences is database, FileMaker is database, should be the same, and they drop all kind of questions on my desk, being the only database developer in the department (gov).

My objection is more the fact that I'm not used to hard code 'data' in a formula.

Obviously it is a common practise in FileMaker.

There is a number (f.i. 98) in a numberfield.

It is between 97 and 100, so the return value in a (text)field should be A+.

And so on for the rest of the range.

That was the initial question and I got very good solutions for that. (is in toolbox now)

I found out that in FileMaker it is not possible to have a 'range' numberfield.

So if you say 97-100 is not a number, you're right, my bad.

Now I know that in FM, to solve this, I need to work with an additional table and go relational.

Again, I learned something today....

[The Shadow]

Now they want the other way round...

Wonder if that is possible without hardcoding everything in a Case().

Of course its possible, but why? The above calculation I provided encodes the table (which isn't great), but would be difficult to modify if the ranges were to change.

If you used Case() instead, both calculations would be hard-coded, but they would encode the table in an obvious way. What is your objection to using Case()?

[jrRaid]

What is your objection to using Case()?

See my answer to Comment's question.

It is not a real objections.

Call it a personal habit to try to avoid hardcoding as much as possible.

[comment]

My objection is more the fact that I'm not used to hard code 'data' in a formula.

Obviously it is a common practise in FileMaker.

I don't know about common, but it is just as bad practice in FileMaker as it is anywhere else.

However, the point is that a calculation like the one provided by Shawn hard-codes the data too. So the best solution is to lookup from a table, the next best solution would be a calculation using Case().