Small formula for calculation

[Ramballah]

Hello Everyone,

Today I feel stupid once again, I feel like this answer is probably really easy but I just cannot figure this out.
I have a sheet which has lvl 1 to lvl 100 noted down and the XP needed to gruduate the level.
For example to "achieve" lvl 1 i need 500 xp. Now the issue with this little game is that there is no total xp mentioned anywhere.
All I know is how much xp it takes to the next lvl, and once you reach the level your XP get's reset to 0.
So I have figured out the total XP by now. But now first look at my sheet then Ill explain my question.

1	500	26	13,000	51	25,500	76	38,000
2	1,000	27	13,500	52	26,000	77	38,500		Level	59
3	1,500	28	14,000	53	26,500	78	39,000		XP in Level	9,750
4	2,000	29	14,500	54	27,000	79	39,500		XP needed	1,659,750
5	2,500	30	15,000	55	27,500	80	40,000
6	3,000	31	15,500	56	28,000	81	40,500
7	3,500	32	16,000	57	28,500	82	41,000
8	4,000	33	16,500	58	29,000	83	41,500
9	4,500	34	17,000	59	29,500	84	42,000
10	5,000	35	17,500	60	30,000	85	42,500
11	5,500	36	18,000	61	30,500	86	43,000
12	6,000	37	18,500	62	31,000	87	43,500
13	6,500	38	19,000	63	31,500	88	44,000
14	7,000	39	19,500	64	32,000	89	44,500
15	7,500	40	20,000	65	32,500	90	45,000
16	8,000	41	20,500	66	33,000	91	45,500
17	8,500	42	21,000	67	33,500	92	46,000
18	9,000	43	21,500	68	34,000	93	46,500
19	9,500	44	22,000	69	34,500	94	47,000
20	10,000	45	22,500	70	35,000	95	47,500
21	10,500	46	23,000	71	35,500	96	48,000
22	11,000	47	23,500	72	36,000	97	48,500
23	11,500	48	24,000	73	36,500	98	49,000
24	12,000	49	24,500	74	37,000	99	49,500
25	12,500	50	25,000	75	37,500	100	50,000
							2,525,000

Here you can see the lvls with the XP going up by 500 each time.
On the right you see a little section where it says Level 59, XP in level 9,750 and XP needed 1,659,750.
What I want is to just fill in which level I am (59 for example) and that I have 9,750 XP already into the 29,500. And then it calculates exactly how much XP I need till 2,525,000 total XP.
So I did it manually for now but I rather want it to be automated if possible. So if I were to be lvl 75 I would only need: 1,137,500 XP till lvl 100 if I don't have any XP in the level.
I hope I was clear enough, if not please ask away!

Thanks in advance

 

[jasonb75]

Noting that there is a consistent increase of 500 each time

Book1
	A
2	59
3	9,750
4	1,659,750
Sheet1

Cell Formulas
Range	Formula
A4	A4	=SUMPRODUCT(500*ROW(INDIRECT("1:100")))-IFERROR(SUMPRODUCT(500*ROW(INDIRECT("1:"&A2-1))),0)-A3

 

[Ramballah]

Noting that there is a consistent increase of 500 each time

Book1
	A
2	59
3	9,750
4	1,659,750
Sheet1

Cell Formulas
Range	Formula
A4	A4	=SUMPRODUCT(500*ROW(INDIRECT("1:100")))-IFERROR(SUMPRODUCT(500*ROW(INDIRECT("1:"&A2-1))),0)-A3

It looks like a solid formula but it does not give me the correct answer. If I change my lvl to 16, and xp in level to 5315 for example the result should be: 2,459,685 but ur formula gives 2,524,497 which is only 503 xp from lvl 100 (2,525,000 xp)
While I am at it, do you think theres also a possibility to add a few questions?
If i am lvl 16 and got 5,315 xp can u add A5 to show my current total XP which is: 65,315 and have another cell in A6 for example that if my current xp is 65315 and I would gain let's say 1,000,000 xp on top of that it would calculate which LVL i will end up with which should be: 64

 

[jasonb75]

the result should be: 2,459,685

That's what I get

Book1
	A
2	16
3	5,315
4	2,459,685
Sheet1

Cell Formulas
Range	Formula
A4	A4	=SUMPRODUCT(500*ROW(INDIRECT("1:100")))-IFERROR(SUMPRODUCT(500*ROW(INDIRECT("1:"&A2-1))),0)-A3

 

[Ramballah]

That's what I get

Book1
	A
2	16
3	5,315
4	2,459,685
Sheet1

Cell Formulas
Range	Formula
A4	A4	=SUMPRODUCT(500*ROW(INDIRECT("1:100")))-IFERROR(SUMPRODUCT(500*ROW(INDIRECT("1:"&A2-1))),0)-A3

My bad, I used a wrong cell reference. Also can you look at my previous reply, I editted that one with a new question...

 

[Eric W]

Some simpler formulas:

Book1
	A	B
1	Level	59
2	XP in level	9750
3	XP needed for next level	20250
4	XP needed to get to level 100	1630250
Sheet2

Cell Formulas
Range	Formula
B3	B3	=(B1+1)*500-B2
B4	B4	=2525000-B2-(250*B1*(B1+1))

These seem to work in my testing, but try them and let us know.

 

[Ramballah]

Some simpler formulas:

Book1
	A	B
1	Level	59
2	XP in level	9750
3	XP needed for next level	20250
4	XP needed to get to level 100	1630250
Sheet2

Cell Formulas
Range	Formula
B3	B3	=(B1+1)*500-B2
B4	B4	=2525000-B2-(250*B1*(B1+1))

These seem to work in my testing, but try them and let us know.

These work nicely I think I might use this one in stead. Now just for my last questions remain which I asked for in my first reply. I hope its possible.

 

[jasonb75]

Some simpler formulas:

I simplified it for 365 using sequence instead of indirect, but mine is still nowhere near as simple as that ?

 

[Eric W]

Oh, the magic of triangle numbers! ?

Try this:

Book1
	A	B
1	Level	3
2	XP in level	22
3	Total XP	3022
4	XP needed for next level	1978
5	XP needed to get to level 100	2521978
Sheet2

Cell Formulas
Range	Formula
B3	B3	=(B1+1)*B1*250+B2
B4	B4	=(B1+1)*500-B2
B5	B5	=2525000-B3

 

[Eric W]

Here's the last part:

Book1
	A	B
1	Level	34
2	XP in level	22
3	Total XP	297522
4	XP needed for next level	17478
5	XP needed to get to level 100	2227478
6
7	Additional points:	500000
8	Gets you to level:	55
Sheet2

Cell Formulas
Range	Formula
B3	B3	=(B1+1)*B1*250+B2
B4	B4	=(B1+1)*500-B2
B5	B5	=2525000-B3
B8	B8	=INT((SQRT(1+(B7+B3)/62.5)-1)/2)