Excel/Spreadsheet tips

[Speedy]

I made this thread for managers to share their tips and tricks regarding Excel/etc.

I just figured out a way to have the in-game date shown in my spreadsheet, and wondered if anyone knew of a more efficient way to represent this.

To get "S43 M10" I am using the following:

Code: Select all

="S"&INT((TODAY()-DATE(2012,7,12))/31)&" M"&INT((TODAY()-DATE(2012,7,12))-(INT((TODAY()-DATE(2012,7,12))/31)*31))

I was using multiple cells to compute the same information, [i.e. C1 =TODAY(), C2 =DATE(2012,07,12) ] but personally, I'd rather have the equation in one cell.
The date was chosen because at the moment it returns the right result. It probably isn't the day the site opened because there have been a few days where the match engine broke and had to be redone.
It's also not entirely future-proof, because there is always a chance that the match engine will repeat days again.

[Xter]

I made this thread for managers to share their tips and tricks regarding Excel/etc.

I just figured out a way to have the in-game date shown in my spreadsheet, and wondered if anyone knew of a more efficient way to represent this.

To get "S43 M10" I am using the following:

Code: Select all

="S"&INT((TODAY()-DATE(2012,7,12))/31)&" M"&INT((TODAY()-DATE(2012,7,12))-(INT((TODAY()-DATE(2012,7,12))/31)*31))

I was using multiple cells to compute the same information, [i.e. C1 =TODAY(), C2 =DATE(2012,07,12) ] but personally, I'd rather have the equation in one cell.
The date was chosen because at the moment it returns the right result. It probably isn't the day the site opened because there have been a few days where the match engine broke and had to be redone.
It's also not entirely future-proof, because there is always a chance that the match engine will repeat days again.

Oh man! I took the Certiport exam for Excel and Excel Master a while ago. There is some crazy stuff you can do in Excel...that I have most likely forgotten

[Speedy]

Shit. This date calculation assumes that I am playing before the matchday switchover. How do I trick the spreadsheet into changing the day at 3:00pm PST (when the matchday ends for me).

Or do I need to add in another 20 characters to an already bulky equation?

My best guess is to tell the computer to tell Excel (and only Excel) that we are in a different timezone, nine hours ahead of my actual timezone. So Poland, or something.

[Sokolov]

Shit. This date calculation assumes that I am playing before the matchday switchover. How do I trick the spreadsheet into changing the day at 3:00pm PST (when the matchday ends for me).

Or do I need to add in another 20 characters to an already bulky equation?

My best guess is to tell the computer to tell Excel (and only Excel) that we are in a different timezone, nine hours ahead of my actual timezone. So Poland, or something.

Can't you just do something like (timezone hour calculation)+9? Sorry, I haven't actually had a chance to decipher your formula.

[Ripp]

Speedy, would you show us the formulas you use to calculate players weighted averages?

[Sokolov]

Speedy, would you show us the formulas you use to calculate players weighted averages?

Eh don't worry, they're wrong anyway

[Speedy]

Speedy, would you show us the formulas you use to calculate players weighted averages?

Eh don't worry, they're wrong anyway

But no, I'd rather not.
Everyone has their own system and they're sort of a trade secret. They've taken me many hours of research and tweaking.

That being said, I'll tell you how I got them:

Go through top teams to see which traits the players tend to favour. Do this enough and the anomalies will cancel out, leaving a general guide to key stats for each position.
Then simplify the data so that you can ignore some stats for some positions (e.g. STR for Keepers) and fit each weighting into a nice little formula.

[Ripp]

Speedy, would you show us the formulas you use to calculate players weighted averages?

Eh don't worry, they're wrong anyway

But no, I'd rather not.
Everyone has their own system and they're sort of a trade secret. They've taken me many hours of research and tweaking.

That being said, I'll tell you how I got them:

Go through top teams to see which traits the players tend to favour. Do this enough and the anomalies will cancel out, leaving a general guide to key stats for each position.
Then simplify the data so that you can ignore some stats for some positions (e.g. STR for Keepers) and fit each weighting into a nice little formula.

I know, I know. I've spent hours on doing exactly the same thing and I'm quite content with mine for now.

I was just hoping that you'll lose your attention and copy them there as a help for a fellow Excelman. . I was sure that you won't, but it was worth a try.