Could anybody help me make a spreadsheet?

Jimmy B said:

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).

Click to expand...

Yes, I was copying the columns themselves.

I'm currently trying the thing you mentioned but since the calculations refer to cells that are to the left of the actual calculation, the empty worksheet template either get's !REF errors if I place only the parts and formulas I need copied for new datasets to the main sheet, or I have to leave blank space to their left to the cells it references to, so there's no such reference errors, but then if I try to copy them over to the main sheet, the blank space will be copied over as well and need removal, too, which in the end is just as much of a hassle as hiding the columns over and over again.

Edit: the separate worksheet thing doesn't seem to work at all, unfortunately. It's a simple copy & paste just as if you had done it from the same sheet.

Ah OK. I'd need to have a play around in Excel I think.

You could reduce the number of columns that need hiding, by doing some nesting in the IF statements. I think you can nest 6 IFs if I recall right. So you could do the formulas for 0-500 through to 2500-3000 in the first column, 3000-3500 to 5500-6000 in the second, etc. Then you'll only have four columns to hide, which isn't much hassle.

Jimmy B said:

Ah OK. I'd need to have a play around in Excel I think.

You could reduce the number of columns that need hiding, by doing some nesting in the IF statements. I think you can nest 6 IFs if I recall right. So you could do the formulas for 0-500 through to 2500-3000 in the first column, 3000-3500 to 5500-6000 in the second, etc. Then you'll only have four columns to hide, which isn't much hassle.

Click to expand...

Yeah I'd like to have it just copy itself over and be ready to use, though, for convenience.

Don't get me wrong, I'm already using the sheet even though I have to hide the columns again, I'd just like to make it as dynamic as possible, after all, the whole point of making it was to save time.

Okay, I figured out how to do the VLOOKUP thing, more or less, which should save me a lot of trouble. The problem is that it is returning the raw number from the index cells, rather than applying the value from the search criteria cell to the formula in the index cell.

I'm gonna keep looking into it, to see if I find out how to do it better, but if anybody knows it and could post here, I'd be grateful.

Looks like you have learned a lot about excel in just a few weeks, kudos to you Huskie

Vloopup is a tricky one, not really apt at them myself, used to use them for work 10 odd years ago.

Not sure why you are getting only raw data back though.

Viperstrike said:

Looks like you have learned a lot about excel in just a few weeks, kudos to you Huskie

Vloopup is a tricky one, not really apt at them myself, used to use them for work 10 odd years ago.

Not sure why you are getting only raw data back though.

Click to expand...

Thanks I'll see if I can upload a sample somewhere of whats going on in my VLOOKUP table. My file is in brazillian portuguese, though, so I'm not quite sure if the functions get translated properly when being opened by an Excel version with another language.

Edit: here it is http://rapidshare.com/files/2115762853/vlookup sample.xls

Edit2: fixed the link to be the right sample.

Huskie said:

Yeah I'd like to have it just copy itself over and be ready to use, though, for convenience.

Don't get me wrong, I'm already using the sheet even though I have to hide the columns again, I'd just like to make it as dynamic as possible, after all, the whole point of making it was to save time.

Click to expand...

Have you played around with macros at all yet? If you're finding yourself repeating the same actions again and again, they might save you some time. Although there is code behind the macros, you don't need to know any code to create one using the 'Record Macro' thing. That'll automatically generate code to reproduce your actions from when you start recording to when you finish.

I don't know for sure it'll help you without seeing exactly what you're doing, but it might.

Huskie said:

Okay, I figured out how to do the VLOOKUP thing, more or less, which should save me a lot of trouble. The problem is that it is returning the raw number from the index cells, rather than applying the value from the search criteria cell to the formula in the index cell.

Click to expand...

I think you'll need to show what you're doing with VLOOKUP really. I can't see any VLOOKUP in the sample above unless it's lost in the conversion from Excel to OpenOffice.

VLOOKUP(_value, _array, _outputColumn, FALSE) works like this:

It looks up _value (e.g. 247) in the values in the first column of the _array (e.g. if _array was A1:D4 it would be looking for 247 in A1, A2, A3 and A4) and returns the value in the _outputColumn of the array when it finds the first exact match (e.g. if _outputColumn was 3 and if A2 and A3 both held 247, it would return the value in C2).

If you change FALSE to TRUE, it is no longer an exact match, and will instead use the row which has the highest value that is less than or equal to 247 (you need A1 to A4 to be sorted in ascending order), e.g. if A1 was 10, A2 was 100, A3 was 220, A4 was 250 then it would return the value in C3).

As an aside, that sample spreadsheet would be much nicer if the data entry was done vertically.

A1, B1, C1 and D1 would remain unchanged. Then put new data in B2, B3, etc. C2 would be B2-B1, etc. Then you can just pull down the formula in D1 (hover mouse on bottom right of the cell, click and drag) to D2, D3, etc.

Huskie, why not pop your spreadsheet up on GoogleDocs, set the permissions to let others change it, and then send JimmyB the link by PM ?

That way he can show you what he means, which can be a lot simpler than trying to write it all out line by line

Jimmy B said:

Have you played around with macros at all yet? If you're finding yourself repeating the same actions again and again, they might save you some time. Although there is code behind the macros, you don't need to know any code to create one using the 'Record Macro' thing. That'll automatically generate code to reproduce your actions from when you start recording to when you finish.

I don't know for sure it'll help you without seeing exactly what you're doing, but it might.

Click to expand...

Yeah I know a bit about it, but it starts kind of beating the purpose of it all when I have to make a macro to save time doing the thing that was supposed to save time itself.

Ultimately the idea, which I'm not yet sure if I'll follow through with, or not, would be to convert the spreadsheet into an app, kind of like jdegre's skillscanner, that can fetch most of the data automatically from the game and fill the spreadsheet out by itself. But before I get into any of that I'd rather make the sheet as simple (function-wise, not content-wise) as possible, so that eventually coding such an app later is also made easier. So the macro suggestion isn't too far off, it's just that I'll do it a bit differently, if I decide to go that route.

Jimmy B said:

I think you'll need to show what you're doing with VLOOKUP really. I can't see any VLOOKUP in the sample above unless it's lost in the conversion from Excel to OpenOffice.

Click to expand...

Hmm I uploaded the wrong sample, sry about that.

Here's the proper one: http://rapidshare.com/files/2115762853/vlookup sample.xls

Huskie said:

Hmm I uploaded the wrong sample, sry about that.

Click to expand...

No problem. In the new one, VLOOKUP is working as intended. It looks up values not formulas. I can't immediately think of a way of using VLOOKUP to achieve what you're trying to do.

If your skill values will always be entered as integers, you could do a similar approach but have the values calculated for each integer (in other words there'd be 10000 rows, with the appropriate formula applied to each). It might sound daunting, but it could be done in a few minutes if you know your way around Excel. Then you could use the VLOOKUP in the way you currently are doing. If you can have fractional skills it starts getting a bit silly though.

I think what you probably really want to do is to create a Custom Function for each range. They're described here and would solve all your problems I think. It does again involve coding though.

Jimmy B said:

No problem. In the new one, VLOOKUP is working as intended. It looks up values not formulas. I can't immediately think of a way of using VLOOKUP to achieve what you're trying to do.

If your skill values will always be entered as integers, you could do a similar approach but have the values calculated for each integer (in other words there'd be 10000 rows, with the appropriate formula applied to each). It might sound daunting, but it could be done in a few minutes if you know your way around Excel. Then you could use the VLOOKUP in the way you currently are doing. If you can have fractional skills it starts getting a bit silly though.

I think what you probably really want to do is to create a Custom Function for each range. They're described here and would solve all your problems I think. It does again involve coding though.

Click to expand...

Yeah, the actual spreadsheet will use fractions skill levels, as for higher levels those fractions can make a significant different in a skills TT value, not enormous, but enough to make the TT skillgain on a single run become very inaccurate for higher level avatars.

I'll have a look at the link you posted, thanks.

And a second commemorative double-post!

Thanks, Jimmy, but I ended up managing to find a completely different solution that doesn't require scripting. Apparently simply using CHOOSE(INT(A1/500);Formula1 with A1 as variable;Formula2 with A1 as variable;etc.) solved my problem nicely. Unfortunately it only allows for 30 formulae, so I'll have to get creative again once I decide to expand the spreadsheet to support the 10k-20k skill range and/or if I decide to convert it into an app.
But that's a problem for another day, for now, I will rejoice!

The only thing still missing on the spreadsheet is figuring out how to calculate the historic of my average % return and other such minor perks. Hopefully those won't be as difficult and I might just figure them out on my own after giving it some thought.

Nice one, that'll do it. It should be easy enough to do it for more than 30 formulae, as long as you can nest CHOOSE statements. Splitting it into two regions above and below 10k with another CHOOSE, something like CHOOSE(INT(A1/10000)+1,CHOOSE(INT(A1/500)+1, f0-500, f500-1000, ...), CHOOSE(INT((A1-10000)/500)+1, f10000-105000, f10500-11000)), would work I imagine.