FMP 8, Mac OSX

I have 6 fields that are each results of a series of calculations.
Call them A, B,C, D, E, and F.

Any one or more may result in a product of 0 [ie: (a +b) * y * 0 = 0]
so IsEmpty isn't an option in the calculation.

I am a bit of a newbie at the more sophisticated functions such as
"Count" and "Get.." Without typing in 36 Case/Results for all the
possible configurations, is there an elegant way of calculating the
average of just the non-zero fields?

Thanks

In article <1177860435.785691.240210 (AT) o5g2000hsb (DOT) googlegroups.com>,
CRC123 <swanson (AT) windsongappraisals (DOT) com> wrote:

IsEmpty and Count aren't going to work since a value of 0 is not an
empty field. The Get functions are of no use at all - they're mostly
used to convert numbers to Text or vice-versa and obtain system stats
(eg. Get(CurrentRecordNum) to obtain the current record' internal ID
number).

You also shouldn't need "36 Case / Results" - since you've only got six
fields, at most you'll need six.

Assuming you actually want the Average of just the non-zero fields,
then something like this would work:

NonZeroAverage Calculation, Number Result, Unstored
= (A + B + C + D + E + F)
/ (6 - If (A = 0, 1, 0)
- If (B = 0, 1, 0)
- If (C = 0, 1, 0)
- If (D = 0, 1, 0)
- If (E = 0, 1, 0)
- If (F = 0, 1, 0)
)

You can use Case instead of If, if you really want to - it makes no
difference when there's only one test-result being performed.


Or the "more elegant" solution of:

NonZeroAverage Calculation, Number Result, Unstored
= (A + B + C + D + E + F)
/ (6 - (A = 0) - (B = 0) - (C = 0) - (D = 0) - (E = 0) - (F = 0))

This uses Boolean logic, where the tests "A = 0" are evaluated the same
way as the full If statements, ie 1 for true and 0 for false. When any
of the field contains 0 that field is subtracted from the total of 6
used to evaluate the Average.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Apr 29, 2:06 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
Nice calc Harry.

Here's another approach
define a custom function: sumlist L

if (valuescount(L) = 1;
L;
middlevalues(L,1,1) + sumlist (middlevalues(L, 2, valuecount(L)-1))

then you can compute non-zero average as follows:
let (nonzerocount = valuecount(filtervalues(L,"0P");
if ( nonzerocount = 0;
'divide by zero error';
sumlist(L) / nonzerocount))

where L is a list of values. (and "P" is the paragraph marker)
you can define the second part as a custom func, or not.

The main advantage is that it works on an arbitrary number of values,
instead of being limited to 5. The main disadvantages are of course,
you need FM Advanced to defien a custom function (which you need
because the summation is recursive), and of course, you have to get
the values into a list before computing the result, which means
concatenating them together separated by "P"s

Given how much FM requires you to build and disect value lists, and
the support it provides with the various "MiddleValues", "ValueCount",
etc functions, its pretty (scratch that REALLY) annoying they haven't
provided a "Values(v1, {v2...vn})" function to build them for us on
the fly without the ugly text concatenation syntax. (Don't tell me
they added it in 8.5...)

Note also that you can do some nifty stuff with value lists (ie the
ones you define for drop down lists/popup menues) to generate the
value lists you need, since you can access those value lists via
script steps. If you need long or complex lists, defined via
relationships, that can be neat trick to generate them.

Note also that sumlist is pretty inefficient; there are some tricks
you can do to make it much faster. (but only relevant if you are
potentially dealing with LONG lists, in the hundreds, or thousands.)

Overall, if you just need the 6 fields, I'd stick with Harry's
solution; its straightforward and more efficient; I just put this out
there to show how one might approach it if you needed to handle a more
generic / larger problem.\

-cheers,
Dave

In article <300420070906259959%helpful_harry (AT) nom (DOT) de.plume.com>,
Helpful Harry <helpful_harry (AT) nom (DOT) de.plume.com> wrote:

Doesn't
SUM(A1:A6)/COUNTIF(A1:A6,"<>0")
do the job?

--
Tom Stiller

PGP fingerprint = 5108 DDB2 9761 EDE5 E7E3
7BDA 71ED 6496 99C0 C7CF

In article <tomstiller-F6130E.18293529042007 (AT) comcast (DOT) dca.giganews.com>,
Tom Stiller <tomstiller (AT) comcast (DOT) net> wrote:

Oops, maybe I'd better pay more attention to the group I'm responding to.

--
Tom Stiller

PGP fingerprint = 5108 DDB2 9761 EDE5 E7E3
7BDA 71ED 6496 99C0 C7CF

On Apr 29, 2:06 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
Tres elegant indeed!
I shall whip it right up and keep you posted.
Many Thanks.

On Apr 29, 3:59 pm, CRC123 <swan... (AT) windsongappraisals (DOT) com> wrote:
Since my grandson isn't around to tell me. "GrannyDanny you sound
silly," May I say

PERFECTAMUNDO!
Thanks