This is how my input looks like in excel,
| days_took_to_equip | cumu_percent |
|---|---|
| 1 | 0.017418302 |
| 2 | 0.020625735 |
| 3 | 0.023148307 |
| 4 | 0.025237133 |
| 5 | 0.026972115 |
| 6 | 0.028752754 |
| 7 | 0.030350763 |
| 8 | 0.032040087 |
| 9 | 0.033603853 |
| 10 | 0.035270349 |
| 11 | 0.036788458 |
| 12 | 0.037518976 |
| 13 | 0.038283738 |
| 14 | 0.039379516 |
| 15 | 0.040189935 |
| 16 | 0.040783481 |
| 17 | 0.041685215 |
| 18 | 0.042347247 |
| 19 | 0.043032109 |
| 20 | 0.043739798 |
| 21 | 0.044230616 |
| 22 | 0.04476709 |
| 23 | 0.045269322 |
| 24 | 0.045725896 |
| 25 | 0.046250956 |
| 26 | 0.046684701 |
| 27 | 0.047129861 |
| 28 | 0.047620678 |
| 29 | 0.047997352 |
| 30 | 0.048396854 |
Where my expected output is
| Range | Avg cum Percent |
|---|---|
| 1 to 10 | 0.027 |
| 1 to 20 | 0.033 |
| 1 to 30 | 0.038 |
Tried pivots tables and labelling is tricky here
I would need this out put to plot a graph
CodePudding user response：
Try-
=MAP(SEQUENCE(3,1,10,10),LAMBDA(x,AVERAGE(INDEX(B2:B31,SEQUENCE(x)))))
CodePudding user response：
I got three answers and the cells consists of formula
E3: =AVERAGE(INDEX($B$2:$B$31,SEQUENCE(RIGHT($D3,2))))
F3: =AVERAGE(INDEX($B$2:$B$31,ROW(INDIRECT("1:"&RIGHT($D3,2)))))
G3: =AVERAGE(OFFSET($A$1,1,1,RIGHT(D3,2)))