How to Findout 'Avg. of 1st 25%' and Avg. of 1st 75%

[#1]

Hi All,

I have a one query regarding 'Avg. of 1st 25%' and Avg. of 1st 75%. I am not able to create the logic for stated requirements but the same logic i have done in excel.it would be great  can anyone help me out of this.

I am attaching Excel sample data in there you will find other calculations too ,in Red colors calculations are looking for help

  Notes:

Avg. of 1st 25%  :   Sort the E2E times in order of increasing (ascending) times & then take average of first 25% completed applications (non null completed dates)-- finding 25 % using this logic count(calendar_days)*.25 but struggling to find average of first 25% for calendar days

Avg. of 1st 75% :Sort the E2E times in order of increasing (ascending) times & then take average of first 75% completed applications (non null completed dates)

Please see the excel logic highlighted in  'Red Color'

[Sergey_Shuklin]

Hi, Sunni!

You can try to use a Fractile() function.

For instance:

Fractile([Calendar Days],0.25) should return 7, and all values under the seven will be your aim.

So all you need is a sum of all values under the fractile and then you must divide it on count of values of the same fractile.

Hope you'll find how to use it!

[#3]

Hi Sergey ,

i am able to find Fractile([Calendar Days],0.25) but i am not getting any idea how to do sum of first 25% or 75%.

could you please provide me the logic so that i can understand so easy .

[marcus_sommer]

Maybe this will be helpful: Re: statistical aggregations - parts between frac 0.1 and 0.9

- Marcus

[#5]

Marcus Summer ,unformately those links are not helpful for requirements

[Sergey_Shuklin · Accepted solution]

Hello, Sunni!

Sorry, if I explained it confused!

I'd used your data. Please, see the picture below and attached file:

It's have a little difference with your excel calculation, but, I think, it's a matter of fractile()'s precision.

[#7]

Hi Sergey,

Thank you for the logic,its working for me.As you said there is difference with my excel calculation that's true but its not big margin ,happening only for decimal values anyway decimals are considerable right .

once again thank you .

[Sergey_Shuklin]

Hello, Sunni!

Glad to help! 😃