Calc which field has the highest number

[ibiubu]

If I have 6 fields that contains a number from 1 to 10. Is there a calc or summary that will look at the current record and report back what the highest number was that was entered in those fields? In other words, five of the fields have the number 5 in them, one has the number 6. Then the returned number would be 6.

And inversely, is there one that will show what the lowest number that was used?

LR

[Razumovsky]

You could definitely do this with a 6 line case statement:

Case(field1>field2 and field1>field3 and field1>field4 and field1>field5 and field1>field6, field1,

field2>field1 and field2>field3 and field2>field4 and field2>field5 and field2>field6, field2...)

might be a more elegant way of accomplishing the same results, but the above should work.

-Raz

[ibiubu]

This one seemed to work:

Max(Q1A, Q1B, Q1C, Q1D, Q1E, Q1F)

And just the opposite for the lowest number:

Min(Q1A, Q1B, Q1C, Q1D, Q1E, Q1F)

LR

[Razumovsky]

That would be the more elegant way I was speaking of...

-Raz

[Ugo DI LUCA]

Very elegant...

Thanks for this, I've learned something more today too...

[kennedy]

Ah, glad to see I wasn't the only one who, based on the not-so-good FMP documentation, didn't think the "summary functions" could be used in the more normal way on just normal fields. In fact, all the summary functions (Min, Max, Count, Sum, Average, StDev, and StDevP) can be used as summary functions, or as calculations over a set of related records, or as calculations over a set of fields in a single record! And they do the extremely useful behavior of ignoring blank fields (rather than the much less useful behavior of treating blanks as zeroes). See the attached file for these non-summary forms in action.

SummaryFunctions.zip