Print Page | Close Window

Average of part of the list of numbers

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19030
Printed Date: 03 May 2024 at 12:20am


Topic: Average of part of the list of numbers
Posted By: muncy
Subject: Average of part of the list of numbers
Date Posted: 05 Feb 2013 at 6:33am
I'm tying myself in knots trying to write a formula. I'll try to explain.
 
I have formula that counts the number of days between two events on tickets. This is used in the report so it looks like:
 
Ticket1 45
Tic ket2 07
Tic ket3 145
Ticket4 23
Ticket5 62
 
etc (There will normally be a minumum of about 30 tickets.
 
What I want to do is disregard the best 10% and the worst 10% then do an average on the rest. So, in the case of the above, 07 and 145 would be disregarded and the remaining values would be averaged (43.33). Of course, depending on the number of values returned it isn't always going to be 1 of the top and the bottom. It needs to be 1 or 10% whichever is higher.
 
Any help appreciated.
 
 


Replies:
Posted By: Sastry
Date Posted: 05 Feb 2013 at 10:50pm
HI

Step 1 :

Find out the Maximum & Minimum values and it's percentages

Maximum 10% could be : Maximum - (Maximum *.10)
Minimum 10% could be  : Minimum + (Minimum*.10)

Step 2 :

Find out the count of Min and Max values like

Numbervar min;
if {No.of.Days} < Minimum + (Minimum*.10) Then
   min:=min+1;

Numbervar max;
If if {No.of.Days} <  Maximum - (Maximum *.10) Then
max:=max+1;

Step 3 :

Find out whether your min & max count is more than 1 or not and suppress those records through selection expert :

If min > 1 Then
{No.of.Days} < Minimum + (Minimum*.10)
Else
Minimum({No.of.Days}

Follow the same logic for Maximum.

You need to write a running total to arrive average.







-------------
Thanks,
Sastry

Posted By: muncy
Date Posted: 06 Feb 2013 at 3:16am
This has given me what I want. Thank you so much


Print Page | Close Window