How to input the experience formula into excel?

[dilloncyh]

Anyone knows how to input the formula for calculating the experience into excel?

 

http://runescape.wikia.com/wiki/Xp

 

I know it's a noob question, I can just check the xp table in some websites or directly into the number into excel, and it's more like a math question then an rs question, but I really need this to help me to calculate something about rs.

 

 

 

thank you

[Vulxai]

Well the calculation I put in is just slightly off, but it still is pretty close (99 is 10xp off, so not too bad, but might give you an idea). I just used that (28)/4+75*2^(28)/7 formula that they posted above the xp table. Here's what I did.

 

The A Column was the Level. A1 was the title "LEVEL", and then under that was the levels. I just put a "1" in A2, and then in A3 I put "=A2+1" and then copied that cell and highlighted the next 97 cells and pasted. It automatically changes the formula for you, so I just changed 100 cell formulas with a simply copy and paste.

 

The B Column was the total xp. B1 was the title "TOTAL XP", and then under that in B2 I put "0"...I don't think any explanation is necessary. Then in B3 I put "83" since that's what the site said was the xp for level 2. In B4 I put the formula "=B3+C4" which will take the total xp that you have and add it to the xp needed, which will come next. Then again, just copied and pasted the formula into the next cells until the "Level 99" cell.

 

The C Column was the xp needed. C1 was the title "XP NEEDED", and then under that in C2 I put "0"...again, no explanation necessary. Then in C3 I put again "83" since that's how much xp you need to hit level 2. Then in C4 I put "=(A3/4)+75*2^(A3/7)" That'll take what's in A3 (which is what level you are) and run in through that formula. Then I copied that cell, highlighted all the cells til the "Level 99" cell, pasted and it automatically adjusts your formula.

 

Like I said, the formula isn't right on since I got level 99 to be "13,034,441", but it's close enough if you can get the exact formula in there.

 

EDIT: Not sure if you're familiar with the formating or not, but just in case you aren't, if you right click on the B (not b1 or b2, the actual B), it'll highlight that ENTIRE column and bring up the right click menu. Go to Format Cells. Under the Number tab (the one you should start in), choose the Number menu in the left hand box. There you can choose to put the decimal place to "0" if you wish to not see decimals and have it automatically round, and you can also click the "Use 1000 separator (,)" to have it automatically put in commas where needed (between thousands and hundreds, between millions and thousands, etc).

[dilloncyh]

Well the calculation I put in is just slightly off, but it still is pretty close (99 is 10xp off, so not too bad, but might give you an idea). I just used that (28)/4+75*2^(28)/7 formula that they posted above the xp table. Here's what I did.

 

The A Column was the Level. A1 was the title "LEVEL", and then under that was the levels. I just put a "1" in A2, and then in A3 I put "=A2+1" and then copied that cell and highlighted the next 97 cells and pasted. It automatically changes the formula for you, so I just changed 100 cell formulas with a simply copy and paste.

 

The B Column was the total xp. B1 was the title "TOTAL XP", and then under that in B2 I put "0"...I don't think any explanation is necessary. Then in B3 I put "83" since that's what the site said was the xp for level 2. In B4 I put the formula "=B3+C4" which will take the total xp that you have and add it to the xp needed, which will come next. Then again, just copied and pasted the formula into the next cells until the "Level 99" cell.

 

The C Column was the xp needed. C1 was the title "XP NEEDED", and then under that in C2 I put "0"...again, no explanation necessary. Then in C3 I put again "83" since that's how much xp you need to hit level 2. Then in C4 I put "=(A3/4)+75*2^(A3/7)" That'll take what's in A3 (which is what level you are) and run in through that formula. Then I copied that cell, highlighted all the cells til the "Level 99" cell, pasted and it automatically adjusts your formula.

 

Like I said, the formula isn't right on since I got level 99 to be "13,034,441", but it's close enough if you can get the exact formula in there.

 

EDIT: Not sure if you're familiar with the formating or not, but just in case you aren't, if you right click on the B (not b1 or b2, the actual B), it'll highlight that ENTIRE column and bring up the right click menu. Go to Format Cells. Under the Number tab (the one you should start in), choose the Number menu in the left hand box. There you can choose to put the decimal place to "0" if you wish to not see decimals and have it automatically round, and you can also click the "Use 1000 separator (,)" to have it automatically put in commas where needed (between thousands and hundreds, between millions and thousands, etc).

 

Thank you very much, this really helps a lot, but I want to know is it possible to use only 1 or 2 cell for the formula? I mean I only need to input my xp and my target level, then I can get the xp needed. Or can I make the whole table, and when I inout the level I need(let say 99), so it can direct me to the cell of the 99's xp?

 

thank you

[JoeDaStudd]

Using Row 1 for names/titles

A for level and B for exp

 

C2

=((B2)/4+75*2^((B2)/7))-A2

 

EDIT using B for Exp and A for level looks nicer

Edited January 13, 2010 by JoeDaStudd

[Ronan]

I think the tricky part with compacting this is getting a reasonable formula for the Sigma notation of the Experience formula. As far as I'm aware, Excel doesn't have that great support for this.

 

Doing a bit of searching though, I've managed a reasonable solution with OpenOffice Calc - I've not got Excel but you could certainly try it out and see if it works.

 

(Little note: I've not played around with spreadsheets for quite sometime so the result is pretty ugly formulae - if anyone's got a better solution I'd love to see how it's done! :^_^:)

 

Assume the cell containing the actual experience you have at the moment is CellA.

Assume the cell containing the target level you desire is CellB.

 

The formula I've used to calculate the needed experience to get to that target level is as follows:

 

OpenOffice Calc Version:
=ROUND((SUM((ROW(INDIRECT(("a1:a"&CellB-1)))/4) + (75 * 2^(ROW(INDIRECT(("a1:a"&CellB-1)))/7))))-CellA; 0)

Microsoft Excel Version:
=ROUND((SUM((ROW(INDIRECT(("a1:a"&CellB-1)))/4) + (75 * 2^(ROW(INDIRECT(("a1:a"&CellB-1)))/7))))-CellA, 0)

 

You'll be replacing the occurrences of CellA and CellB with the actual references to the appropriate cells according to where they are on your spreadsheet.

 

Note: It's not 100% accurate, as I've gone for the choice of using the Approximation formula from the link you gave, certainly initially it'll be less confusing to understand the formula needed. If desired, you could adapt it with the more accurate version to get more accuracy.

 

Give it a try and let me know how it goes! ;)

 

Edit:

 

I always find images helpful, so here's what it looks like for me:

 

 

Thanks 010jonathan for mentioning rounding! ;)

[010jonathan]

I think the tricky part with compacting this is getting a reasonable formula for the Sigma notation of the Experience formula. As far as I'm aware, Excel doesn't have that great support for this.

 

Doing a bit of searching though, I've managed a reasonable solution with OpenOffice Calc - I've not got Excel but you could certainly try it out and see if it works.

 

(Little note: I've not played around with spreadsheets for quite sometime so the result is pretty ugly formulae - if anyone's got a better solution I'd love to see how it's done! :^_^:)

 

Assume the cell containing the actual experience you have at the moment is CellA.

Assume the cell containing the target level you desire is CellB.

 

The formula I've used to calculate the needed experience to get to that target level is as follows:

 

=(SUM((ROW(INDIRECT(("a1:a"&CellB-1)))/4) + (75 * 2^(ROW(INDIRECT(("a1:a"&CellB-1)))/7))))-CellA

 

You'll be replacing the occurrences of CellA and CellB with the actual references to the appropriate cells according to where they are on your spreadsheet.

 

Note: It's not 100% accurate, as I've gone for the choice of using the Approximation formula from the link you gave, certainly initially it'll be less confusing to understand the formula needed. If desired, you could adapt it with the more accurate version to get more accuracy.

 

Give it a try and let me know how it goes! ;)

 

Edit:

 

I always find images helpful, so here's what it looks like for me:

 

An idea to add to your code ;p : you should make it round the result :P

[Ronan]

An idea to add to your code ;p : you should make it round the result :P

 

Good call! ;) Added the two different versions now, as far as I'm aware - Excel uses commas rather than semi-colons. Someone may want to test that though. Thanks! :)

[dilloncyh]

I think the tricky part with compacting this is getting a reasonable formula for the Sigma notation of the Experience formula. As far as I'm aware, Excel doesn't have that great support for this.

 

Doing a bit of searching though, I've managed a reasonable solution with OpenOffice Calc - I've not got Excel but you could certainly try it out and see if it works.

 

(Little note: I've not played around with spreadsheets for quite sometime so the result is pretty ugly formulae - if anyone's got a better solution I'd love to see how it's done! :^_^:)

 

Assume the cell containing the actual experience you have at the moment is CellA.

Assume the cell containing the target level you desire is CellB.

 

The formula I've used to calculate the needed experience to get to that target level is as follows:

 

=(SUM((ROW(INDIRECT(("a1:a"&CellB-1)))/4) + (75 * 2^(ROW(INDIRECT(("a1:a"&CellB-1)))/7))))-CellA

 

You'll be replacing the occurrences of CellA and CellB with the actual references to the appropriate cells according to where they are on your spreadsheet.

 

Note: It's not 100% accurate, as I've gone for the choice of using the Approximation formula from the link you gave, certainly initially it'll be less confusing to understand the formula needed. If desired, you could adapt it with the more accurate version to get more accuracy.

 

Give it a try and let me know how it goes! ;)

 

Edit:

 

I always find images helpful, so here's what it looks like for me:

 

An idea to add to your code ;p : you should make it round the result :P

 

 

thank you, very 'pro'(I have never seen a similiar formula before). But when I input ur formula in Excel, the result shown is always 83-my current xp. So for example, if I input 0 as my curent xp, the result I get is 83, if I input 84 as my current xp, I will get -1. Idk it's because I input wrongly or other reasons.

 

Anyway, thank you for ur help.

[Ronan]

I'll get a trial of Excel and see if I can find out what's happening - could well be something that's different in Excel in comparison to OO Calc. :)

 

Edit:

 

Yea, it seems to be a difference in functionality between OpenOffice Calc and Excel unfortunately. I'll have a play around with it later if it's still needed. ;)