Salesforce and other SMB Solutions are coming soon. ×

# A complicated average

## Recommended Posts

For the purposes of averaging in a new grade I need for a calculation to look at six numbers, each in its own field, drop the three lowest and then replace them with a new number 3 times. It has to be done without replacing the min one at a time. An example: Given the numbers 5, 8, 4, 12, 2, 7 and a new number 10. I need my calculation field to replace the 5, 4, and 2 above with the 7, returning 7, 8, 7, 12, 7. Any thoughts?

##### Share on other sites

The example seems to contradict what you're saying in the first part. What happened to the new number 10?

##### Share on other sites

Leaving out the 10 was a mistake on my part.

##### Share on other sites

I don't know about others, but you're certainly overestimating my abilities by expecting me to understand this. If you have made a mistake, please correct it. As it stands now, I have no idea what you're trying to do. Nor why.

It would also help knowing which version of FMP you're using.

##### Share on other sites

Without going into a long drawn out explanation I need a calculation that will drop the three smallest numbers in a set of numbers and then return the remaining numbers.

##### Share on other sites

First I thought I had it, but now I am in doubt.

What if there are identical numbers in the original series?

say 1,4,6,4,1,7

then a sorted series would look like

1,4,6,7

option 1: then you'd want any number under 6 be replaced? so: 6,6,6,6,6,7 or 7,7,7,7,7,7

or

option 2: 1,1,4,4,6,7 replaced as 4,4,6,4,4,7 (using 3rd in line = the number 4)

same question: what about 2,3,2,2,3,3 ?

kjoe

##### Share on other sites

fwiw, here's a stab at option 1.

kjoe

##### Share on other sites

I'm a little confused now. I need it to drop the three lowest but it could be the same number 3 times if necessary. Thus, given 1, 1, 4, 4, 6, 7 the calculation needs to return 4, 6, 7 (the three largest numbers).

##### Share on other sites

I'm confused too. "Drop" and "replace" are different things. In your first post you said quite clearly,

return a list of replaced numbers, now you seem to be asking for a shortened list? And your example drops one 4 of two. Does it matter which one?

kjoe

##### Share on other sites

Ultimately it is my goal to replace the three dropped numbers with the contents of another number field, so yes, I did say replace; however, I think I can manage that portion. What I really need help with is getting rid of the three smallest numbers and somehow keeping the three largest. In my example, one 4 was dropped but the other 4 was kept because I only want three numbers to be dropped. If I had dropped both then I would have dropped 4 numbers. And no, it doesn't matter which of the two 4's was dropped.

##### Share on other sites

I did not succeed in doing this with a calculation, it could be possible though.

I had to resort to a script. The script under button puts the highest three values in sorted order and then sets the fourth, fifth and sixth fields to the value of the third, but you could pick any value.

There's an unused script that sorts all six values. The scripts leave the original field values intact.

hope it helps

kjoe

#### Archived

This topic is now archived and is closed to further replies.

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.5k
×
×
• Create New...