Mchllwoods
New Member
- Joined
- Feb 15, 2019
- Messages
- 18
- Office Version
- 365
- Platform
- Windows
I made a pareto sheet/chart of the my members XP in my Discord server based on Trump excel tutorial and a few of my own designs. I'm just not sure what I should calculate the %running total from...
Should I be using the Current Column or Remains Column?
Here's the data...
Here is the Dashboard data...
Should I be using the Current Column or Remains Column?
Here's the data...
MSXP.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | |||
3 | 10 | 4 | 380 | 710 | 1.1K | Average | ||||||||
4 | 49 | 50 | 4.9K | 9.2K | 14.2K | Subtotal | ||||||||
5 | Level | Count | Current | Remains | XP Needed to Level Up | Username and Members' Count | Highlighted Bars 01 | Highlighted Bars 02 | Remaining Bars 01 | Remaining Bars 02 | Cumulative | |||
6 | 23 | 1 | 681 | 3.2K | 3.9K | cacodemom666 at Level: 23 | 681 | 3.2K | #N/A | #N/A | 13.8% | |||
7 | 22 | 1 | 1.8K | 1.8K | 3.6K | RagingGirl84 at Level: 22 | 1.8K | 1.8K | #N/A | #N/A | 50.1% | |||
8 | 15 | 1 | 522 | 1.5K | 2.0K | DeadFaceEcho at Level: 15 | 522 | 1.5K | #N/A | #N/A | 60.6% | |||
9 | 9 | 3 | 464 | 491 | 955 | 3 Members at Level: 9 | 464 | 491 | #N/A | #N/A | 70.0% | |||
10 | 8 | 1 | 380 | 440 | 820 | S0ME0NE C00L at Level: 8 | 380 | 440 | #N/A | #N/A | 77.7% | |||
11 | 7 | 2 | 201 | 495 | 695 | 2 Members at Level: 7 | 201 | 495 | #N/A | #N/A | 81.8% | |||
12 | 6 | 2 | 168 | 413 | 580 | 2 Members at Level: 6 | #N/A | #N/A | 168 | 413 | 85.2% | |||
13 | 5 | 2 | 276 | 199 | 475 | 2 Members at Level: 5 | #N/A | #N/A | 276 | 199 | 90.8% | |||
14 | 4 | 8 | 197 | 183 | 380 | 8 Members at Level: 4 | #N/A | #N/A | 197 | 183 | 94.8% | |||
15 | 3 | 6 | 100 | 195 | 295 | 6 Members at Level: 3 | #N/A | #N/A | 100 | 195 | 96.8% | |||
16 | 2 | 4 | 80 | 140 | 220 | 4 Members at Level: 2 | #N/A | #N/A | 80 | 140 | 98.4% | |||
17 | 1 | 10 | 43 | 112 | 155 | 10 Members at Level: 1 | #N/A | #N/A | 43 | 112 | 99.3% | |||
18 | 0 | 9 | 35 | 65 | 100 | 9 Members at Level: 0 | #N/A | #N/A | 35 | 65 | 100.0% | |||
Pareto |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B3:B4 | B3 | =XLOOKUP(F3,tblPoints[XP Needed to Level Up],tblPoints[Level XP],,1) |
C3:F3 | C3 | =AVERAGE(C$6#) |
C4 | C4 | =SUM(C$6#) |
D4:F4 | D4 | =SUM(D6#) |
B6:B18 | B6 | =SORT(UNIQUE(INDIRECT(B5)),,-1) |
C6:C18 | C6 | =COUNTIFS(Experience,">0",Level,B6#) |
D6:D18 | D6 | =AVERAGEIFS(Current,Level,B6#) |
E6:E18 | E6 | =F6#-D6# |
F6:F18 | F6 | =XLOOKUP(B6#,tblPoints[Level XP],tblPoints[XP Needed to Level Up]) |
H6:H18 | H6 | =IF(C6#=1,XLOOKUP(B6#,Level,Username)&" at Level: "&B6#,C6#&" Members at Level: "&B6#) |
I6:J18 | I6 | =IF($M6#<=Dashboard!$C$5,D6#,NA()) |
K6:L18 | K6 | =IF($M6#>Dashboard!$C$5,D6#,NA()) |
M6:M18 | M6 | =SUBTOTAL(9,OFFSET(D6#,,,SEQUENCE(ROWS(H6#))))/D4 |
Dynamic array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Current | =Sources!$O$4:$O$56 | D6 |
Experience | =Sources!$F$4:$F$56 | C6 |
Level | =Sources!$N$4:$N$56 | C6:D6, H6 |
pCumulative | =Pareto!$M$6# | I6:L6 |
pMembers | =Pareto!$H$6# | M6 |
Username | =Sources!$M$4:$M$56 | H6 |
Here is the Dashboard data...
MSXP.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | |||
4 | Scroll Bar # | 80 | Above | Below | ||||||
5 | Cumulative | 80.5% | Target | 80% | 20% | |||||
6 | Error Bar | 12.5 | Members | 7 | 43 | 14% | 86% | |||
7 | Accumulated | 3.8K | 1.1K | 78% | 22% | |||||
Dashboard |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F5 | F5 | =Dashboard!C4/100 |
G5,I6:I7 | G5 | =1-F5 |
C5 | C5 | =XLOOKUP(Dashboard!F5,Pareto!M6#,Pareto!M6#,,1) |
C6 | C6 | =COUNTA(Pareto!H6#)+0.5-1 |
F6 | F6 | =SUM(Pareto!C6:XLOOKUP(Dashboard!C5,Pareto!M6#,Pareto!C6#)) |
G6 | G6 | =Pareto!C4-F6 |
H6:H7 | H6 | =MIN(1,F6/SUM(F6:G6)) |
F7 | F7 | =SUM(Pareto!D6:XLOOKUP(C5,Pareto!M6#,Pareto!D6#)) |
G7 | G7 | =Pareto!D4-F7 |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
pCumulative | =Pareto!$M$6# | F6:F7, C5 |
pMembers | =Pareto!$H$6# | C6 |