I'm having a hard time calculating a cumulative sum without using charts (with above()). The case is roughly this:
I have sales data by customer (let's forget the other dimensions for now) which I need to process. I've made a chart that has 3 colums: customerid, customer ranking (based on sum(sales)) and cumulative percentage of sales (meaning that the percentage of the last customer = 100%). The cumulative column uses above() function which is okey in a chart.
Here's an example:
Customer ID
Sales Ranking
Cumulative Sales %
13
1
0.15
3
2
0.29
2
3
0.42
7
4
0.54
5
5
0.65
9
6
0.75
10
7
0.85
1
8
0.90
6
9
0.94
4
10
0.96
15
11
0.98
14
12
0.99
8
13
0.99
11
14
0.99
12
15
1.00
The real question is this: how can I extract a specific customer id based on the cumulative percentage? Let's say I want to have a text box that has the id of the customer that is the first one to have a percentage greater than 0.5 (meaning customer 7). How do I do this?
I've managed to do this with a macro but that's one extra click the user has to make.
