Gryff
asked on
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 :) )
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 :) )
ASKER
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
"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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.
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.
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.
mlmcc
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
ASKER
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.
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.
ASKER
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}
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
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
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.
Cheers,
frodoman
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
ASKER
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.
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.
Do you discard based on value or based on position in the list?
Pete