Am I doing this right?

Mchllwoods

New Member
Joined
Feb 15, 2019
Messages
18
Office Version
  1. 365
Platform
  1. 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...
MSXP.xlsx
BCDEFGHIJKLM
31043807101.1KAverage
449504.9K9.2K14.2KSubtotal
5LevelCountCurrentRemainsXP Needed to Level UpUsername and Members' CountHighlighted Bars 01Highlighted Bars 02Remaining Bars 01Remaining Bars 02Cumulative
62316813.2K3.9Kcacodemom666 at Level: 236813.2K#N/A#N/A13.8%
72211.8K1.8K3.6KRagingGirl84 at Level: 221.8K1.8K#N/A#N/A50.1%
81515221.5K2.0KDeadFaceEcho at Level: 155221.5K#N/A#N/A60.6%
9934644919553 Members at Level: 9464491#N/A#N/A70.0%
1081380440820S0ME0NE C00L at Level: 8380440#N/A#N/A77.7%
11722014956952 Members at Level: 7201495#N/A#N/A81.8%
12621684135802 Members at Level: 6#N/A#N/A16841385.2%
13522761994752 Members at Level: 5#N/A#N/A27619990.8%
14481971833808 Members at Level: 4#N/A#N/A19718394.8%
15361001952956 Members at Level: 3#N/A#N/A10019596.8%
1624801402204 Members at Level: 2#N/A#N/A8014098.4%
171104311215510 Members at Level: 1#N/A#N/A4311299.3%
180935651009 Members at Level: 0#N/A#N/A3565100.0%
Pareto
Cell Formulas
RangeFormula
B3:B4B3=XLOOKUP(F3,tblPoints[XP Needed to Level Up],tblPoints[Level XP],,1)
C3:F3C3=AVERAGE(C$6#)
C4C4=SUM(C$6#)
D4:F4D4=SUM(D6#)
B6:B18B6=SORT(UNIQUE(INDIRECT(B5)),,-1)
C6:C18C6=COUNTIFS(Experience,">0",Level,B6#)
D6:D18D6=AVERAGEIFS(Current,Level,B6#)
E6:E18E6=F6#-D6#
F6:F18F6=XLOOKUP(B6#,tblPoints[Level XP],tblPoints[XP Needed to Level Up])
H6:H18H6=IF(C6#=1,XLOOKUP(B6#,Level,Username)&" at Level: "&B6#,C6#&" Members at Level: "&B6#)
I6:J18I6=IF($M6#<=Dashboard!$C$5,D6#,NA())
K6:L18K6=IF($M6#>Dashboard!$C$5,D6#,NA())
M6:M18M6=SUBTOTAL(9,OFFSET(D6#,,,SEQUENCE(ROWS(H6#))))/D4
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Current=Sources!$O$4:$O$56D6
Experience=Sources!$F$4:$F$56C6
Level=Sources!$N$4:$N$56C6:D6, H6
pCumulative=Pareto!$M$6#I6:L6
pMembers=Pareto!$H$6#M6
Username=Sources!$M$4:$M$56H6


Here is the Dashboard data...
MSXP.xlsx
BCDEFGHI
4Scroll Bar #80AboveBelow
5Cumulative80.5%Target80%20%
6Error Bar12.5Members74314%86%
7Accumulated3.8K1.1K78%22%
Dashboard
Cell Formulas
RangeFormula
F5F5=Dashboard!C4/100
G5,I6:I7G5=1-F5
C5C5=XLOOKUP(Dashboard!F5,Pareto!M6#,Pareto!M6#,,1)
C6C6=COUNTA(Pareto!H6#)+0.5-1
F6F6=SUM(Pareto!C6:XLOOKUP(Dashboard!C5,Pareto!M6#,Pareto!C6#))
G6G6=Pareto!C4-F6
H6:H7H6=MIN(1,F6/SUM(F6:G6))
F7F7=SUM(Pareto!D6:XLOOKUP(C5,Pareto!M6#,Pareto!D6#))
G7G7=Pareto!D4-F7
Named Ranges
NameRefers ToCells
pCumulative=Pareto!$M$6#F6:F7, C5
pMembers=Pareto!$H$6#C6
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top