Average of (Fractile function)

[mario-sarkis]

settu_periasamy

No of Clients        No of Trasactions
10                         100
20                         200
30                         300
40                         400
50                         500
60                         600
70                         700
80                        800

My Question  as  we can see the total nober of transaction in 3600

If I use the function fractile(Trasactions,0.9) this will give me the number of client that did the 90% of transactions …

I need to find lets  say the average number of transactions per Clients who did 90% of these transactions.

Please help Anyone ?

dreamer4

settu_periasamy

sunindia

[sunny_talwar]

When I use the below formula, I get the output as 730.

=Fractile([No of Trasactions], 0.90)

What should the average be for customers??? Is it going to be the average of all the customer with No of Transactions below 730??

[mario-sarkis]

hi sunindia  yes i need the average of transactions by customer with the condition <=730 (excluding the 10 Percent rest)

Can you help it needs an aggr fucntion ?

[sunny_talwar]

May be this:

=Avg({<[No of Trasactions] = {"<= $(=Fractile([No of Trasactions], 0.90))"}>}[No of Trasactions])

Gives you the average of 100, 200, 300, 400, 500, 600 and 700 = 400

[mario-sarkis]

ID	Segment	Amount
1	Medium	1000
2	Small	300
3	Small	500
4	Medium	2000
5	Small	100
6	Small	200
7	Medium	500
8	Small	600
9	Medium	3000
10	Medium	2500
11	small	50

ID	Segment	Amount	Total Nb of IDs	90% of 5	Avg Amount
7	Medium	500	5	4.5	1500
1	Medium	1000
4	Medium	2000
10	Medium	2500
9	Medium	3000

ID	Segment	Amount	Total Nb of IDs	90% of 5	Avg Amount
11	small	50	6	5.4	290
8	Small	600
6	Small	200
5	Small	100
3	Small	500
2	Small	300

Output:

Output:	Avg 90%	Median	Avg	Min	Max
Medium	1500	2000	1800	500	3000
Small	290	250	291.6666667	50	600

sunindia sorry for disturbing you , this is exactly wut i want as an output a Pivot Table as it show above and the first table is my row Data  , as you can see i need to sort it Decreasing by segment and the calculate in a set analysis i dont want to change my row data in the script

Hope you can help thank you my friend

[mario-sarkis]

Average 90% is calculated :

                          for medium lets say is calculate it as   (500+1000+2000+2500)/4=1500         

                          for Small (50+100+200+300+500)/5=230

sunindia

[sunny_talwar]

this?

Avg 90%Expression:

=Avg(If(Aggr(Fractile(TOTAL <Segment> Amount, 0.90) >= Amount, Segment, Amount), Amount))