We help IT Professionals succeed at work.

# Excel Trimean calculation in Crystal

on
Medium Priority
727 Views
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 :) )
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT

Commented:
Hi Gryff,
Do you discard based on value or based on position in the list?

Pete

Commented:
The discard is literally the 10% highest value numbers and the 10% lowest value numbers.  I have included below the description I am going by. The example is a small set of numbers so it appears to be just removing the highest and lowest number but if the data set was larger there would be more exclusions.

"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
Commented:
Your best bet is to create a UFL (user function library) and use vb, etc. to write the code for this function.  There's a document with instructions here: http://support.businessobjects.com/communityCS/TechnicalPapers/scr_user_defined_functions.pdf.asp

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.

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
Hmm, looking through the documentation you linked to I am not sure this will be possible. The report is being made for another companies intranet so I assume this DLL would need to be loaded onto their intranets webserver and incorporated into their pages in someway. Even assuming this is allowed and someone here knows how to do it (both unlikely :( ) I think the time it would take to write the .dll is not practical for us.

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.

Commented:
Well, there is a solution - it is possible to do this in Crystal, it's just a whole lot of work.  I gave you the high-level algorythm that you'd use in a set of Crystal formulas.

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.
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
A UFL will take no longer to write than the code in Crystal.  It can then be used in several reports.  I am not familiar with web delivery but, yes it would need to be included in the distribution.  Since you will be putting many dlls onto their server one more shouldn't be a problem.

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

Commented:
The site already has the normal crystal .dll's loaded onto the intranet (this was done years ago), incidently it occurs to me to mention that this is being written in crystal 8.5 rather than the latest version of crystal. Generally we just place the report into a folder on the webserver and then modify a web page to link to the file. I *think* there is some Javascript somewhere that loads all the crystal files, this is not a standard crystal web reports enviroment unfortunately.

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.

Commented:
Ok, For future queries this is what I have done and it seems to have the desired results. Unfortunately it won't handle more than 1000 records per group, though hopefully this should be sufficient. This is ultimately what Frodoman suggested as an alternative to the UFL.

This Formula is in the detail line
Shared Values() as Double
shared aCount as Number
if isnull({SCCall.Call_DTime}) then
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
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
else
end if

dim Avg, Cnt as number
Avg = 0
Avg = Avg + Values(i)
Cnt = Cnt + 1
next i
aCount = 0
Formula = avg/cnt
else
Formula = ""
end if

Commented:

Incidentally, the 1000 limit does increase if you upgrade to a newer version of Crystal.  If you ever need more than 1,000 records you could always use two arrays and put all values below the median in one and the remainders in the other.

Cheers,

frodoman

Commented:
Thanks a Lot Frodoman,

I have done some tests and I hit the loop limits for my sorting before I hit the 1000 record limit. Just have to tell users to limit their reports to 6 months at a time or so.
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile