Do you discard based on value or based on position in the list?

Pete

Solved

Posted on 2006-03-28

I have a report that works out the average of a series of numbers using the build in summary fuction but I need to work out the trimean of the same sets of numbers. For those unaware of what trimean is (like I was) it is an average of the numbers after discarding the top and bottom 10 percent of the numbers.

The numbers are contained within a group and I will need to work out the total trimean figure for multiple groups at the end of the report, I imagine this will be fairly easy once I know how to do it for one group.

I will appreciate any help in doing this, it feels like it should be easy but my brain seems insufficient for the task this week (possibly less late nights would help :) )

The numbers are contained within a group and I will need to work out the total trimean figure for multiple groups at the end of the report, I imagine this will be fairly easy once I know how to do it for one group.

I will appreciate any help in doing this, it feels like it should be easy but my brain seems insufficient for the task this week (possibly less late nights would help :) )

10 Comments

"Trimean=is an Excel function where by Trimean calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. This function can be used to to exclude outlying data from our analysis. The example below excludes 20% which is 10% from the lower numbers and 10% from the upper numbers"

Example

Trimean Ave

4 4

5 5

6 6

*excluded 20 20

6 6

3 3

4 4

5 5

*excluded 1 1

4 4

5 5

4.666666667 5.727272727

It should be possible to do it within Crystal but it would be a major pain. First you'll need to read through all of the records and put them into a dynamically sized array. Then sort the array and count the elements and figure out which elements get dropped (don't forget special cases like what, if anything, gets dropped if there are only five items in the list, if the calculations call for dropping 1.5 elements on each end do you drop 1 or 2 on each end or drop 1 on the low end and 2 on the high end, etc.). Once you know which items to ignore you can simply add the remaining elements and divide.

Unfortunately array management in Crystal can be frustrating so although implementation is possible in Crystal it'll be much easier outside.

The problem I have is that the Trimean seems trivial to do in excel, but creating a dynamic report with varying number of groups is difficult (at least for me) if not impossible in excel but the reverse is true for crystal. I think projects like this are created to punish me for a mispent previous life.

I suppose I should find out what the closing procedure is in this case, where there is no real solution.

I don't have the time to write the entire thing right now but if you want to attempt to work through it I'm happy to help.

Just FYI, I suspect the reason there is no Trimean function in Crystal is because it doesn't seem to be a standard function anywhere. I did some checking on the internet and found at least three different implementations of it - some change the % discarded and some actually do a more weighted average by using the 25th, 50th, and 75th percentile and averaging them with double-weight on the 50th.

How large are the datasets? How much variance is there in the size?

By this I mean are the number of values typically 10-20 or 50-60 or 100 or is there a much wider range say some are 10 and others are 500.

mlmcc

The report allows for a date range so the datasets could be fairly small (one or two items per group) with a short date range on a quiet clients, to many hundreds per group (this would be less common but still possible) with a large date range on busy clients.

So assuming vb6, how would I take in the data for each detail line and then return a trimean (I can work out the logic for this part myself easily enough) in the group total. The UDF example seems to take data in and returns it straight away, I would need to make the function accept the figures on each detail line and at the end of the group return the trimean result.

It seems that I could do most if not all of this with some formula's within crystal just as easily and without requiring the need for a custom .dll that we need to argue past security policies. In fact now that I have had some more sleep I see that Frodomans second paragraph was referring to that and not the UDF. oops.

This Formula is in the detail line

Shared Values() as Double

shared aCount as Number

if isnull({SCCall.Call_DTime}

Formula = ""

else

aCount = aCount + 1

Redim Preserve Values(aCount)

Values(aCount) = {SCCall.Call_DTime}

Formula = cstr(Values(aCount))

end if

This formula is place in the group Footer

Shared aCount as Number

if aCount > 0 then

Shared Values(aCount) as Double

dim i, j, tmp, discard

For i = 1 To UBound(Values)

For j = UBound(Values) To 1 + i Step -1

If Values(i) > Values(j) Then

tmp = Values(j)

Values(j) = Values(i)

Values(i) = tmp

End If

Next

Next

If aCount > 2 then

Discard = int(aCount*.1)

if Discard < 1 then discard = 1

else

Discard = 0

end if

dim Avg, Cnt as number

Avg = 0

for i = 1+Discard to UBound(Values)-Discard

Avg = Avg + Values(i)

Cnt = Cnt + 1

next i

aCount = 0

Formula = avg/cnt

else

Formula = ""

end if

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**26** Experts available now in Live!