Excel/Spreadsheet tips

Talk about what worked out for you on QManager. Strategies, fun things or just habits you acquired.
User avatar
Speedy
Legend
Posts: 3503
Joined: Sun Apr 13, 2014 2:11 am
Team Name: Crewe Cuts
Location: Vancouver, BC
Contact:

Excel/Spreadsheet tips

Postby Speedy » Wed Mar 16, 2016 2:04 pm

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.
User avatar
Xter
Hall-Of-Famer
Posts: 1140
Joined: Wed Jul 22, 2015 8:56 pm
Team Name: MU, MM, FFH, HC, GL
Location: Washington

Re: Excel/Spreadsheet tips

Postby Xter » Thu Mar 17, 2016 2:30 am

Speedy wrote: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 :cry:
Xter

Millport Quidditch Association
Fareham Fighting-Hamsters Organization
Glasglow Lagavulin Organization
Halifax Canadiens Organization

Founder, Chief Editor, and Writer at Quidditch Weekly

Contact
User avatar
Speedy
Legend
Posts: 3503
Joined: Sun Apr 13, 2014 2:11 am
Team Name: Crewe Cuts
Location: Vancouver, BC
Contact:

Re: Excel/Spreadsheet tips

Postby Speedy » Sun Mar 20, 2016 1:48 am

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.
User avatar
Sokolov
Hall-Of-Famer
Posts: 1960
Joined: Sun Mar 16, 2014 7:41 pm
Team Name: Bristol Bangtails

Re: Excel/Spreadsheet tips

Postby Sokolov » Sun Mar 20, 2016 3:58 pm

Speedy wrote: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.
Bristol Bangtails
Honour League
Founded Season 19
:Honour2nd: :Honour2nd: :Honour2nd: :Honour3rd:
:Gold1st: :Silver1st: :Bronze1st: :Steel1st: :Iron1st:
User avatar
Ripp
Captain
Posts: 467
Joined: Wed Aug 19, 2015 12:38 pm
Team Name: Newcastle Gunslingers, Poole Koerakoonlased
Location: Estonia

Re: Excel/Spreadsheet tips

Postby Ripp » Fri Apr 08, 2016 1:10 pm

Speedy, would you show us the formulas you use to calculate players weighted averages? :D
Newcastle Gunslingers
"We deal in lead, friend."
User avatar
Sokolov
Hall-Of-Famer
Posts: 1960
Joined: Sun Mar 16, 2014 7:41 pm
Team Name: Bristol Bangtails

Re: Excel/Spreadsheet tips

Postby Sokolov » Fri Apr 08, 2016 1:52 pm

Ripp wrote:Speedy, would you show us the formulas you use to calculate players weighted averages? :D


Eh don't worry, they're wrong anyway
Bristol Bangtails
Honour League
Founded Season 19
:Honour2nd: :Honour2nd: :Honour2nd: :Honour3rd:
:Gold1st: :Silver1st: :Bronze1st: :Steel1st: :Iron1st:
User avatar
Speedy
Legend
Posts: 3503
Joined: Sun Apr 13, 2014 2:11 am
Team Name: Crewe Cuts
Location: Vancouver, BC
Contact:

Re: Excel/Spreadsheet tips

Postby Speedy » Fri Apr 08, 2016 2:26 pm

Sokolov wrote:
Ripp wrote:Speedy, would you show us the formulas you use to calculate players weighted averages? :D


Eh don't worry, they're wrong anyway

:lol:

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.
User avatar
Ripp
Captain
Posts: 467
Joined: Wed Aug 19, 2015 12:38 pm
Team Name: Newcastle Gunslingers, Poole Koerakoonlased
Location: Estonia

Re: Excel/Spreadsheet tips

Postby Ripp » Fri Apr 08, 2016 2:41 pm

Speedy wrote:
Sokolov wrote:
Ripp wrote:Speedy, would you show us the formulas you use to calculate players weighted averages? :D


Eh don't worry, they're wrong anyway

:lol:

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. :D. I was sure that you won't, but it was worth a try.
Newcastle Gunslingers
"We deal in lead, friend."

Return to “Tips & Tricks”

Who is online

Users browsing this forum: No registered users

cron