Menu
What's New
By:
Filters

Could anybody help me make a spreadsheet?

Huskie

Huskie

Elite
Joined
Nov 22, 2005
Posts
2,556
Location
Brazil
Society
Apocalyptic Uprising
Avatar Name
John Huskie Hunt
I'm a complete noob with Excel.

After every hunting run I do, I manually register my new skill levels for every single skill that changed on a notepad file, go to skill calc tool on wiki and calculate one by one how much TT skillgain I got in each skill, then add it all up and calculate the percentage of skillgain per PED cycled on that run.

Needless to say, it takes an outrageous amount of time, compared to how quick it would be to just fill in a properly done spreadsheet with the new values for each skill, which would then automatically calculate the TT value of that gain, and then give me a total figure. So I kinda want to make such a spreadsheet, except, like I said at the very beggining: I'm a complete noob with Excel. And I wouldn't know how to make it calculate the skillgains in TT value from the previous amount that skill was at, because I have no idea of the mathematical way of representing skill TT value vs skill level, neither the specific proportion between them (all I know is that it increases exponentially).

So if anybody could give me any help at all on this, I'd be very, very grateful.
 
Doer's (entropedia) and Jdegre's (chipping optimiser) use different data/fits I think. Not sure the formulas they use have been published, if not you'd need their help (or possibly Witte if he has access to the entropedia one).
 
Can you clear your Messages box?
 
You can't just use jdegre's skill scanner to do exactly the same thing ? :scratch2:

IIRC if you're a registered user, it gives a comparison between old and new stats. (One of those who use it regularly might like to comment on this)
 
Serica said:
You can't just use jdegre's skill scanner to do exactly the same thing ? :scratch2:

IIRC if you're a registered user, it gives a comparison between old and new stats. (One of those who use it regularly might like to comment on this)
Click to expand...

Jdegre 's skill scanner will do it for you . So go get that and donate some to his tool. Totally worth it considering the way you are doing it.
 
TunerS said:
Can you clear your Messages box?
Click to expand...

Done.


JoshZuke said:
Jdegre 's skill scanner will do it for you . So go get that and donate some to his tool. Totally worth it considering the way you are doing it.
Click to expand...

Have tried contacting him before to do that, never got a hold of him.

Also, while the skillscanner can calculate skillgains, it doesn't give me totals, or calculate skillgain per PED cycled which means I'd still have to juggle around several applications and files to keep track of it all, whereas a spreadsheet would be an all-in-one thing.


EDIT: I *think* I figured out how to do the excel things for sums, subtotals and the like, all I need now is the formula for skill level vs tt value. I PM'd Witte and jdegre, but Doer's inbox was full. Does anybody know if there's a way for me to contact him in-game?
 
Last edited:
Huskie said:
I PM'd Witte and jdegre, but Doer's inbox was full. Does anybody know if there's a way for me to contact him in-game?
Click to expand...

He's not online all that much at the moment. Probably your best bet is to post in a (relevant) thread he's subscribed to (ie. one he's posted in).

I think someone has asked this question on the forum before without much luck. A nice closed form formula for skill volume was never found, as I understand it they both use lots of different formulae for different ranges of skill. So it might not go into Excel all that nicely, although it could be done with VBA behind the spreadsheet even if you can't get it into a simple excel formula.
 
Okay, yeah I've been trying to calculate the formula myself all this time, and it does seem it uses different formula for different skill intervals.

What I'm wondering is how the heck do I make excel calculate it based on that? To calculate the skillgain in TT I must subtract the earlier skill level from the latest skill level.

How do I make excel go "(if number of later skill level is between X and Y use A formula; if number of later skill level is between Y and Z use B formula)-(if number of earlier skill level is between X and Y use A formula; if number of earlier skill level is between Y and Z use B formula)"?

Jimmy B said:
...as I understand it they both use lots of different formulae for different ranges of skill. So it might not go into Excel all that nicely, although it could be done with VBA behind the spreadsheet even if you can't get it into a simple excel formula.
Click to expand...

What does this mean?
 
Sorry for double post, but I'm so happy I think this deserves a second post.

I have worked out the three formulas that I needed AND figured out a way to script it all into excel, so now my spreadsheet is COMPLETE!


:yay::yay::yay::yay::yay:

I'm pretty stoked about this.


Unfortunately it seems there's still a little bit of deviation between my formula and the one used on the wiki's skill calc. Which one is more correct, I can't really tell, since I can't test the TT values of skills in practice without spending a crapton of PEDs on ESIs, but my guess is that the skill calc's formula is more correct, since mine was based off of skill calc data.
 
Last edited:
Angel O2 Mercer said:
Will you be sharing your excel now?

:)
Click to expand...

Not just yet, I still need to refine the formulas, neither do I know yet if publishing it will be a good move (it might be that the people who can help me with the formula won't want this spreadsheet made public, so I'd rather have their help to make a good tool that won't be published, than not have their help and publish an inaccurate tool to the community). The whole formula issue seems to be a bit more complicated than I expected. At first I was trying to figure out the formula for the curve doing inputs of 250 levels intervals per data point, which made a neat regular curve, and gave me good results with over 99.999% accuracy, so I thought I was good to go.

But when I saw that there were still being some discrepancies between my formula and the results from the wiki's skill calc, I tried reducing the intervals between data points to 50 levels only, meaning 5 times as many data points, and the graph doesnt look quite like a neat curve anymore, but rather a bit of a serpentine around a curve, which means the formula is a fair bit more complex than what my previous attempts suggested, and I currently can only come up with 98% accurate formulas, which may seem pretty accurate, still, but for this specific subject it really isn't at all.

I tried leaving a visitor message on Doer's profile, hopefully he'll be back soon and lend me a hand on this. I got a reply from jdegre, in which he says his program uses a script to interpolate a bunch of formulas, so I suppose I couldn't use that for Excel.

At the very least I now know how to implement this crazy stuff on Excel, which is a huge step for somebody who didn't know even the most basic of basics a few hours ago. :)
 
Last edited:
Huskie said:
What does this mean?
Click to expand...

I basically meant this:

Huskie said:
I got a reply from jdegre, in which he says his program uses a script to interpolate a bunch of formulas, so I suppose I couldn't use that for Excel.
Click to expand...

You could probably write something similar to jdegre's script using VBA (which is code that sits behind Excel, and could be run, for instance, by pressing a button on the spreadsheet). That didn't sound like what you were after though.


Huskie said:
Which one is more correct, I can't really tell, since I can't test the TT values of skills in practice without spending a crapton of PEDs on ESIs, but my guess is that the skill calc's formula is more correct, since mine was based off of skill calc data.
Click to expand...

You don't need to spend PEDs of ESIs, or at least you didn't in the past. You could just look at an ESI on auction and it'll tell you how many skill points it'd give you.

Huskie said:
At the very least I now know how to implement this crazy stuff on Excel, which is a huge step for somebody who didn't know even the most basic of basics a few hours ago. :)
Click to expand...

Nice work :)
 
Jimmy B said:
You don't need to spend PEDs of ESIs, or at least you didn't in the past. You could just look at an ESI on auction and it'll tell you how many skill points it'd give you.
Click to expand...

True, I forgot about that, but it's useless now that I already figured out my formulas still aren't spot on.
 
Huskie said:
True, I forgot about that, but it's useless now that I already figured out my formulas still aren't spot on.
Click to expand...

Why do you need it exact?

That's a pretty tall order. Jedgre's and Doer's formulae aren't exact either, at least not for all skill levels.
 
Jimmy B said:
Why do you need it exact?

That's a pretty tall order. Jedgre's and Doer's formulae aren't exact either, at least not for all skill levels.
Click to expand...

Because in order to keep long-term track of things, the smaller the inaccuracy the better. My formulas have deviations that would render the results very inaccurate over the course of just a few runs only.

Here are images that can better illustrate the problem:

curveinaccurate.jpg


This is the formula I got (red line), which is based on big 250 level steps between each data point (blue dots) and seems very accurate.


curveprecise.jpg



This is what the "curve" really looks like when you use smaller steps between data points, notice how the blue dots serpentine around the red line, meaning it is going to be inaccurate very very often.
 
Last edited:
Uniqu3 said:
You need a sin in your function, here is the original thread where it was worked out between Doer Jdegre and the community: https://www.planetcalypsoforum.com/forums/showthread.php?58525-Fill-up-ESI-data
Click to expand...

Thanks, that's a useful link.

Ok, done reading it, and unfortunately there's no formula there. The ones given at the start while they were brainstorming are less accurate than the ones I got myself atm, all I could extract from it is that they use smaller intervals between formula changes, meaning more than 3 formulas. It still doesn't get me anywhere closer to solving the serpentine problem I showed in the pictures of my previous post.

On a side-note: I think I figured out how to calculate better formulas that can serpentine around, so far 99,98% accuracy and increasing. Still not enough, but hopefully it'll get there eventually.
 
Last edited:
Huskie said:
On a side-note: I think I figured out how to calculate better formulas that can serpentine around, so far 99,98% accuracy and increasing. Still not enough, but hopefully it'll get there eventually.
Click to expand...

How are you measuring accuracy? Against in-game values, or against one of the existing formulas (e.g. entropedia/optimiser).

Anyway, for long-term tracking, small random errors should cancel each other out in time. Or you could add in some correction since when you've gained a lot of skill it'll be easier to test it against in-game values.
 
Jimmy B said:
How are you measuring accuracy? Against in-game values, or against one of the existing formulas (e.g. entropedia/optimiser).

Anyway, for long-term tracking, small random errors should cancel each other out in time. Or you could add in some correction since when you've gained a lot of skill it'll be easier to test it against in-game values.
Click to expand...

The accuracy is an average of a bunch of factors like mean absolute error, maximum error and correlation coefficient, but basically it is being measured against the data points I got from using the entropedia skill calc. So, yeah, I don't want it to be 100% perfect, although it's slowly getting there.

Here's my progress so far:
curveserpentinesuccess.jpg


That highest data point is proving to be a bit problematic, to the point that I'm thinking that it is incorrect, but at least my formula is slowly getting to it, despite looking more and more like a monstrous equation.
 
Last edited:
Okay, I now have all the formulas I need calculated, but ran into one last snag before completing the spreadsheet.

Basically to calculate the TT gain from a hunting run I have to get the TT value of the skill level after the run, get the TT value of the skill level before the run, then subtract the latter by the former. So far, so good.

The thing is, that I'm using 20 different formulas to calculate the TT value (and that's because I'm stopping at 10k, if I went all the way to 20k it'd be 40 formulas). As said above, my spreadsheet knowledge is very limited, so the way I found to calculate this was to do it through nested IF statements.

For example: IF(1<=X<500;[formula_for_1-500];IF(500<=X<1000;[formula_for_500-1000];IF... etc.

The problem is that I'm getting the formula overflow error doing this, even if I try to only calculate one skill TT value per cell, then do the actual subtraction on a different one.

How could I go about calculating it without breaking the token limit?
 
Last edited:
Huskie said:
Okay, I now have all the formulas I need calculated, but ran into one last snag before completing the spreadsheet.

Basically to calculate the TT gain from a hunting run I have to get the TT value of the skill level after the run, get the TT value of the skill level before the run, then subtract the latter by the former. So far, so good.

The thing is, that I'm using 20 different formulas to calculate the TT value (and that's because I'm stopping at 10k, if I went all the way to 20k it'd be 40 formulas). As said above, my spreadsheet knowledge is very limited, so the way I found to calculate this was to do it through nested IF statements.

For example: IF(1<=X<500;[formula_for_1-500];IF(500<=X<1000;[formula_for_500-1000];IF... etc.

The problem is that I'm getting the formula overflow error doing this, even if I try to only calculate one skill TT value per cell, then do the actual subtraction on a different one.

How could I go about calculating it without breaking the token limit?
Click to expand...


Sorry I haven't followed the thread in depth but when trying to do nested if statements I would look into using a VLookup with a seperate table in your spreadsheet for your conditions. Just google Vlookup and nested IF statements and you should get some examples to see if will suit the purpose.

Ant
 
Anhithe said:
Sorry I haven't followed the thread in depth but when trying to do nested if statements I would look into using a VLookup with a seperate table in your spreadsheet for your conditions. Just google Vlookup and nested IF statements and you should get some examples to see if will suit the purpose.

Ant
Click to expand...

Thanks, that sounds useful, I'll give it a try.
 
Huskie said:
Okay, I now have all the formulas I need calculated, but ran into one last snag before completing the spreadsheet.

Basically to calculate the TT gain from a hunting run I have to get the TT value of the skill level after the run, get the TT value of the skill level before the run, then subtract the latter by the former. So far, so good.

The thing is, that I'm using 20 different formulas to calculate the TT value (and that's because I'm stopping at 10k, if I went all the way to 20k it'd be 40 formulas). As said above, my spreadsheet knowledge is very limited, so the way I found to calculate this was to do it through nested IF statements.

For example: IF(1<=X<500;[formula_for_1-500];IF(500<=X<1000;[formula_for_500-1000];IF... etc.

The problem is that I'm getting the formula overflow error doing this, even if I try to only calculate one skill TT value per cell, then do the actual subtraction on a different one.

How could I go about calculating it without breaking the token limit?
Click to expand...

There are neater ways of doing it, but a quick and easy hack would be to use a different column for each range. e.g:

Column AA: IF(1<=X<500;[formula_for_1-500];0)
Column AB: IF(500<=X<100;[formula_for_500-1000];0)
.
.
.
Column AT: IF(9500<=X<10000;[formula_for_9500-10000];0)
Column AU: SUM(AAx:ATx)

Then hide columns AA to AT to keep things tidy.
 
Jimmy B said:
There are neater ways of doing it, but a quick and easy hack would be to use a different column for each range. e.g:

Column AA: IF(1<=X<500;[formula_for_1-500];0)
Column AB: IF(500<=X<100;[formula_for_500-1000];0)
.
.
.
Column AT: IF(9500<=X<10000;[formula_for_9500-10000];0)
Column AU: SUM(AAx:ATx)

Then hide columns AA to AT to keep things tidy.
Click to expand...

Hmm that sounds interesting, but can it be copied over and over again for every new run's table, or would I have to manually select the columns to hide every time I copy all of it over?

Also, what does this x stand for here: "SUM(AAx:ATx)"?
 
Huskie said:
Hmm that sounds interesting, but can it be copied over and over again for every new run's table, or would I have to manually select the columns to hide every time I copy all of it over?

Also, what does this x stand for here: "SUM(AAx:ATx)"?
Click to expand...

Hi Huskie, he means the range with x.

like SUM(A13:Z22) would calculate all cells in the range of A13 to A22, then B13 to B22 and so on until Z13 to Z22

Think of it like calculating a large square table of cells all at once.
 
Viperstrike said:
Hi Huskie, he means the range with x.

like SUM(A13:Z22) would calculate all cells in the range of A13 to A22, then B13 to B22 and so on until Z13 to Z22

Think of it like calculating a large square table of cells all at once.
Click to expand...

Oh, right. Thanks.

Excuse my derping. :laugh:
 
Huskie said:
Hmm that sounds interesting, but can it be copied over and over again for every new run's table, or would I have to manually select the columns to hide every time I copy all of it over?
Click to expand...

I'm at home right now, and don't have Excel here, so I don't know. Try it and see. If the columns need to be hidden each time, you could save yourself a bit of time by recording a macro whilst you do it the first time. Then you could just run the macro each time you need to hide the columns.

But I'd imagine if you're copying the (template) worksheet into a new worksheet (rather than copy-pasting from one worksheet to another) then the hidden columns would be hidden in the copy too.
 
Jimmy B said:
I'm at home right now, and don't have Excel here, so I don't know. Try it and see. If the columns need to be hidden each time, you could save yourself a bit of time by recording a macro whilst you do it the first time. Then you could just run the macro each time you need to hide the columns.

But I'd imagine if you're copying the (template) worksheet into a new worksheet (rather than copy-pasting from one worksheet to another) then the hidden columns would be hidden in the copy too.
Click to expand...

Yeah, unfortunately copying hidden columns makes their pasted counterparts not be hidden, so I have to manually hide them again for every new set of data.

I'm asking on Excel forums parallel to here about how to solve all these problems, but with all due respect to the people there, they seem rather uncapable of helping without pasting a sample workbook on the thread, regardless of how simple and thoroughly explained the situation is, which would be ok, except the damn forum's attachment function won't let me upload my samples to it, despite being within the size limit and a supported file extension... :banghead:

Sorry for the rant, this was really just something that was bothering me and I needed to vent. :silly2:
 
Huskie said:
Yeah, unfortunately copying hidden columns makes their pasted counterparts not be hidden, so I have to manually hide them again for every new set of data.
Click to expand...

Are you copying the columns themselves?

Try making a template worksheet with all the formulas and everything hidden appropriately, but with no data. Then when you get round to entering data, copy the template to a new sheet (right-click on the worksheet tab, I think the menu has a 'Move or Copy' option from which you can create a new copy of the sheet, which I imagine would have the columns hidden).
 
You must log in or register to reply here.
Back
Top