Need a more elegant calculation than what's planned

[Rich S]

Howdy, howdy:

 

I have this calc on the 3DBB: 

 

Truncate (

 

    (

        AdaptsToNewSituations1__lxn + 

        ControlsEmotions1__lxn + 

        DemonstratesProblemSolvingSkills1__lxn + 

        DisplaysSelfConfidence1__lxn +

        SharesTakesTurnAndCooperatesWithPeers1__lxn + 

        TakesCareOfPersonalProperty1__lxn ) / 6 ; 1

     )

 

 

The calc works, but it has a serious flaw: it assumes that all six fields will have data in them before an average is calculated. But what if one of them is empty? I don't want to "hard code" the number, 6, in the calc because then the average will be thrown off if one or more fields are empty. Rats.

 

What's a more elegant/functional way of coding this? I thought of using/creating a summary field and working off that, but with over 100 fields it's going to get ugly really fast...unless, of course, that's the best way of getting the job done; ugly I can live with, but unwieldy, no.

 

TIA for your suggestions!

 

 

[doughemi]

The Count() function counts only non-empty fields in its field list, so

 

Let(

[divisor = count(AdaptsToNewSituations1__lxn; ControlsEmotions1__lxn; DemonstratesProblemSolvingSkills1__lxn ; DisplaysSelfConfidence1__lxn ; SharesTakesTurnAndCooperatesWithPeers1__lxn; TakesCareOfPersonalProperty1__lxn)];

Truncate(

        (AdaptsToNewSituations1__lxn + 

        ControlsEmotions1__lxn + 

        DemonstratesProblemSolvingSkills1__lxn + 

        DisplaysSelfConfidence1__lxn +

        SharesTakesTurnAndCooperatesWithPeers1__lxn + 

        TakesCareOfPersonalProperty1__lxn ) / divisor ; 1

     )

)

[LaRetta]

You might try this:

 

Let ( [
values = 
  List ( 
  AdaptsToNewSituations1__lxn ; 
  ControlsEmotions1__lxn ; 
  DemonstratesProblemSolvingSkills1__lxn ; 
  DisplaysSelfConfidence1__lxn ; 
  SharesTakesTurnAndCooperatesWithPeers1__lxn ; 
  TakesCareOfPersonalProperty1__lxn
   ) ;
total = Evaluate ( Substitute ( values ; ¶ ; "+" ) )
] ;
Truncate ( total / ValueCount ( values ) ; 1 )
)

[comment]

How about =

Truncate (
Average (
AdaptsToNewSituations1__lxn ;
ControlsEmotions1__lxn ;
DemonstratesProblemSolvingSkills1__lxn ;
DisplaysSelfConfidence1__lxn ;
SharesTakesTurnAndCooperatesWithPeers1__lxn ;
TakesCareOfPersonalProperty1__lxn
) ; 1 )

 

Not sure why you are using Truncate() and not Round(), for example - or why is it necessary to limit the calc's precision at all.

 

 

I would also question the structure, as it seems that the six parameters would be better represented by six records in a related table, with each record having a field for Type and Values. Then it becomes very simple to compute the average by =

Average ( Parameters::Value )

[Rich S]

Thank you all!

 

To answer your questions, Comment:

 

1) I suggested the same thing but was instructed to truncate instead of round. Since the calc will be used to calculate GPAs, there's a protocol in our school district that states that rounding isn't allowed. I truncated the number because of field character length limitations; repeating decimals could put a number's length into the ozone. (The result has to appear in its entirety within a box on a State form--need I say more?)

 

2) I'm being hamstrung by a superior who has trouble working with relational databases--he prefers flat as much as possible so he can see what's going on. *sigh* Some people just can't leave Excel behind. :S

[comment]

Since the calc will be used to calculate GPAs, there's a protocol in our school district that states that rounding isn't allowed.

 

Well, I don't know what your business rules are. I suspect that if it was my average that was being calculated, I would protest against any kind of rounding - and truncating is just another type of rounding.

 

 

I truncated the number because of field character length limitations; repeating decimals could put a number's length into the ozone. (The result has to appear in its entirety within a box on a State form--need I say more?)

 

Actually, Filemaker has a built-in precision limit of 16 decimals (which you can extend using the SetPrecision() function). And number fields can be formatted to display only a preset number of decimals.