Excel Trimean calculation in Crystal

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 :) )
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

GryffAuthor 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"
      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
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

GryffAuthor 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.
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.
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.


GryffAuthor 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.
GryffAuthor 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 = ""
    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
    If aCount > 2 then  
        Discard = int(aCount*.1)
        if Discard < 1 then discard = 1
        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
    Formula = ""
end if
Glad to help.

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.


GryffAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.