Game Points Bank Account

[jgthtrotih]

Hi All,

Excel Workbook
	R	S	T	U	V	W	X
5	Your Points	Points Needed To Level Up	Current Level	Your Credit	Withdrawal	Deposit	Credit
6
7
8	0	You Need 18,000 Pts.	Intermediate	Your Credit is 0 Pts
9	11,500	6,500	Beginner	Your Credit is 0 Pts	2500	4000
10	10,454	7,546	Beginner	Your Credit is 454 Pts			454
11	12500	5,500	Beginner	Your Credit is 1000 Pts	2000	3500	1000
Sheet6

i have a "Game Points Bank Account" that keeps track of the points users earn or spend in the game...what i need help is creating a running total for the bank..ive tried everything nothing works..

(this is actually like a traditional bank account)

each player starts with 10,000 points

*Row 8 is the only row to have formulas*

*Rows 9-11 are typed in to show my desired results*

Here is the point value "Ranking System" for Column T

Excel Workbook
	AG	AH
5	Tier 1
6	Beginner	18000
7	Beginner	36000
8	Beginner	54000
9	Intermediate	72000
10	Intermediate	90000
11	Intermediate	108000
12
13	Tier 2
14	Advanced	126000
15	Advanced	144000
16	Advanced	162000
17	Expert	180000
18	Expert	198000
19	Expert	216000
20
21	Tier 3
22	Elite	234000
23	Elite	252000
24	Elite	270000
25	Superior	288000
26	Superior	306000
27	Superior	325000
Sheet6

Please ask for further questions!

Thanks!

 

[snowblizz]

There were some changes in the formulas. Also you need to change the sorting of the values, and you can delete the 0, Beginner line.

Excel Workbook
	R	S	T	AA	AB
5				325000	Superior
6				306000	Superior
7				288000	Superior
8	72000	You Have Enough Pts.	Intermediate	270000	Elite
9	10000	You need 8000 Pts.	Beginner	252000	Elite
10	18000	You Have Enough Pts.	Beginner	234000	Elite
11	71998	You need 2 Pts.	Intermediate	216000	Expert
12	126000	You Have Enough Pts.	Advanced	198000	Expert
13	71999	You need 1 Pts.	Intermediate	180000	Expert
14	-85	You need 18085 Pts.	Beginner	162000	Advanced
15	10000	You need 8000 Pts.	Beginner	144000	Advanced
16				126000	Advanced
17				108000	Intermediate
18				90000	Intermediate
19				72000	Intermediate
20				54000	Beginner
21				36000	Beginner
22				18000	Beginner
Sheet1

Excel 2007

Cell Formulas
Range	Formula
S8	=IF(ISNA(INDEX($AA$5:$AB$22,MATCH(R8,$AA$5:$AA$22,0),1)),"You need "&INDEX($AA$5:$AB$22,MATCH(R8,$AA$5:$AA$22,-1),1)-R8&" Pts.","You Have Enough Pts.")
S9	=IF(ISNA(INDEX($AA$5:$AB$22,MATCH(R9,$AA$5:$AA$22,0),1)),"You need "&INDEX($AA$5:$AB$22,MATCH(R9,$AA$5:$AA$22,-1),1)-R9&" Pts.","You Have Enough Pts.")
S10	=IF(ISNA(INDEX($AA$5:$AB$22,MATCH(R10,$AA$5:$AA$22,0),1)),"You need "&INDEX($AA$5:$AB$22,MATCH(R10,$AA$5:$AA$22,-1),1)-R10&" Pts.","You Have Enough Pts.")
S11	=IF(ISNA(INDEX($AA$5:$AB$22,MATCH(R11,$AA$5:$AA$22,0),1)),"You need "&INDEX($AA$5:$AB$22,MATCH(R11,$AA$5:$AA$22,-1),1)-R11&" Pts.","You Have Enough Pts.")
S12	=IF(ISNA(INDEX($AA$5:$AB$22,MATCH(R12,$AA$5:$AA$22,0),1)),"You need "&INDEX($AA$5:$AB$22,MATCH(R12,$AA$5:$AA$22,-1),1)-R12&" Pts.","You Have Enough Pts.")
S13	=IF(ISNA(INDEX($AA$5:$AB$22,MATCH(R13,$AA$5:$AA$22,0),1)),"You need "&INDEX($AA$5:$AB$22,MATCH(R13,$AA$5:$AA$22,-1),1)-R13&" Pts.","You Have Enough Pts.")
S14	=IF(ISNA(INDEX($AA$5:$AB$22,MATCH(R14,$AA$5:$AA$22,0),1)),"You need "&INDEX($AA$5:$AB$22,MATCH(R14,$AA$5:$AA$22,-1),1)-R14&" Pts.","You Have Enough Pts.")
S15	=IF(ISNA(INDEX($AA$5:$AB$22,MATCH(R15,$AA$5:$AA$22,0),1)),"You need "&INDEX($AA$5:$AB$22,MATCH(R15,$AA$5:$AA$22,-1),1)-R15&" Pts.","You Have Enough Pts.")
T8	=INDEX($AA$5:$AB$23,MATCH(R8,$AA$5:$AA$23,-1),2)
T9	=INDEX($AA$5:$AB$23,MATCH(R9,$AA$5:$AA$23,-1),2)
T10	=INDEX($AA$5:$AB$23,MATCH(R10,$AA$5:$AA$23,-1),2)
T11	=INDEX($AA$5:$AB$23,MATCH(R11,$AA$5:$AA$23,-1),2)
T12	=INDEX($AA$5:$AB$23,MATCH(R12,$AA$5:$AA$23,-1),2)
T13	=INDEX($AA$5:$AB$23,MATCH(R13,$AA$5:$AA$23,-1),2)
T14	=INDEX($AA$5:$AB$22,MATCH(R14,$AA$5:$AA$22,-1),2)
T15	=INDEX($AA$5:$AB$22,MATCH(R15,$AA$5:$AA$22,-1),2)

 

[jgthtrotih]

Snow,

Changed Out There Formulas..Checked It Out Works Perfect!

There are a few changes i would like to make/add but that can be dealt with another time.?

Thanks!

 

[jgthtrotih]

Hey,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Column
Case 22
Target.Offset(0, -4).Value = Target.Offset(0, -4).Value - Target.Value
Case 23
Target.Offset(0, -5).Value = Target.Offset(0, -5).Value + Target.Value
Case 24
Target.Offset(0, -6).Value = Target.Offset(0, -6).Value + Target.Value
Case Else
Exit Sub
End Select
End Sub

currently this code is for the whole colums of R V W X can it be tuned down to the ranges of

R8:R42 V8:V42 W8:W42 X8:X42

and every time i try and edit a multiple rows i get a "debugger" error?

Thanks!

 

[snowblizz]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row < 8 Or Target.Row > 42 Then Exit Sub
Select Case Target.Column
Case 22 'Column V
Target.Offset(0, -4).Value = Target.Offset(0, -4).Value - Target.Value
Case 23 'Column W
Target.Offset(0, -5).Value = Target.Offset(0, -5).Value + Target.Value
Case 24 ''Column X
Target.Offset(0, -6).Value = Target.Offset(0, -6).Value + Target.Value
Case Else
Exit Sub
End Select
End Sub

Should do it.
Column "R" shouldn't be in there, right? Wasn't part of the original.

How do you mean "edit multiple rows"? It should immediately exit if multiple cells are changed. And it should only react to user changes. As far as I'm aware you can only edit one cell at a time.
I can't replicate any error. What does the error say exactly?

 

[jgthtrotih]

Snow,

The code isnt working its still letting the users enter numbers in columns V W X than calutlating the total in column R...beyond Row 42

How do you mean "edit multiple rows"? It should immediately exit if multiple cells are changed. And it should only react to user changes. As far as I'm aware you can only edit one cell at a time.
I can't replicate any error. What does the error say exactly?

OK nvm this error it does what you say it does..it does exit on multiple row edits..so that's fine.

Thanks!

 

[jgthtrotih]

The code isnt working its still letting the users enter numbers in columns V W X than calutlating the total in column R...beyond Row 42

This is working now...didn't put it in the right spot.

There are a few changes i would like to make/add but that can be dealt with another time.?

Thanks!

 

[jgthtrotih]

Hi All

Excel Workbook
	N	O	P	Q	R
5			Rank		Your Points
6
7
8	1		16		4
9	2		9		16,483
10	3		12		6,364
11	4		10		12500
12	5		8		18,000
13	6		6		63,000
14	7		15		200
15	8		13		4,994
16	9		11		6,499
17	10		5		75,000
18	11		3		198,040
19	12		2		201,004
20	13		4		76,554
21	14		7		34,531
22	15		1		295,000
23	16		17		4
24	17		14		1,232
Sheet6

i found the formula i think on the Microsfot Website

i have developed a rank system..my only problems is i need a way to pull out the highest and lowest score with the corresponding number

So with the data above i would get for the lowest score

"Player 16 has a Rank of 17 with 4 Pts"

For The Highest

"Player 15 has a Rank of 1 with 295,000 Pts"

There are 35 Player Spots 17 are Filled

Thanks!

 

[snowblizz]

Try this.
Though I recommend starting a new thread for a new question. You'll get more views that way usually.

Excel Workbook
	L	M
26	Lowest	Player 16 has a rank of 17 with 4 points
27	Highest	Player 15 has a rank of with 295000 points
Sheet1

Excel 2007

Cell Formulas
Range	Formula
M26	="Player " & INDEX(N8:N42,MATCH(MAX(P8:P42),P8:P42,0),1) & " has a rank of " & INDEX(P8:P42,MATCH(MAX(P8:P42),P8:P42,0),1) & " with " & INDEX(R8:R42,MATCH(MAX(P8:P42),P8:P42,0),1)& " points"
M27	="Player " & INDEX(N8:N42,MATCH(MIN(P8:P42),P8:P42,0),1) & " has a rank of " & INDEX(Q8:Q42,MATCH(MIN(P8:P42),P8:P42,0),1) & " with " & INDEX(R8:R42,MATCH(MIN(P8:P42),P8:P42,0),1)& " points"

 

[jgthtrotih]

Snow,

Thanks That Works Nicely..

Though I recommend starting a new thread for a new question. You'll get more views that way usually.

i would have started a new thread but i thought that the question went along with the with the topic. i might have another question.. in the near future but as of now everything is perfect!

Thanks For All Your Help!

Thanks!