Average function returns blank from related number fields

[sonflare]

I have a related number field, which sometimes contains "<" on front of the number, due to it being a scientific test value. ie. They can't test for the existence of something below a certain threshold so it gets called "<0.1" instead of "0".

I am trying to get an average of this field via a batch relationship. Only trouble is that the "<" seems to be upsetting the Average function.

I've tried this:

Average (

If ( Left ( RelatedTable::RelatedField ; 1 ) ="<" ; 0 ; RelatedTable::RelatedField )

)

to try and make the "<0.1" translate into "0" but the Average function is just drawing a blank (empty).

If I leave the value as "<0.1" then the average treats it as 0.1, which is technically incorrect.

Any ideas on how I can overcome this problem?

[comment]

You cannot put conditions INSIDE the Average() function.

I would make the field a Text field (since it allows entries which are not numbers), and add a calculation field with a Number result that would return 0 for entries like "<0.1", otherwise the entered number. Then average the calculation field instead.

[sonflare]

Bummer I was hoping that would not be the answer. There are a LOT of these result numbers that need to be averaged...

Oh well.

[comment]

Do you mean you have a lot of different FIELDS like that? If there's not too many related records, you could use a custom function instead - but you would need the Advanced version. Hmm.. How about something like this:

CleanAverage.fp7.zip

[sonflare]

Hey, wow!

That looks like my answer. Thanks heaps. I'm not all that advanced when it comes to calculations, but I see how that works. I could turn it into a custom function as I do have FMP Advanced.

Thanks again.

[Lee Smith]

I could turn it into a custom function as I do have FMP Advanced.

Update your Profile, so that we know how to answer your questions.

Main Menu [color:blue]>> MY PROFILE [color:blue]>> Control Panel [color:blue]>> FileMaker Questions

Lee