Interested in my League Settings Spreadsheet?

dappawit · 08-12-2002, 03:19 PM

I have my own spreadsheet I use to keep track of and suggest League Totals season to season. There are a few such spreadsheets around, and I don't know if mine is different enough (or good enough

) that's its worth sharing, but I like it and maybe so will someone else.

Anyway, my Excel spreadsheet works like this... For the first season you use it, you enter the league settings you already have, the results you want per team, the actual league totals you get at the end of the season, and the number of teams in your league. (The only real tedious part is adding up the league totals manually and entering them.) It will spit out new league totals which try to correct for the difference between the league totals and what you wanted per team. For each year after the first, you just have to enter the results you want per team, the actual league totals, and the number of teams in your league. The new league settings are carried over automatically to the next year entry in the spreadsheet.

Now for an explanation of the math...
To calculate the new league totals, it takes the per-team totals you wanted, multiplies it by the number of teams in your league (to get expected league wide totals), then divides that by the actual totals. This should result in a number close to one, and the closer it is to one, the closer the expected stats are to the actual ones.
Now, to get the new settings, it adds one to this number and divides by two (by adding one an dividing by 2, this acts as a damper of sorts and more or less prevents the settings from being too high one year, then equally too low the next, which happened to me alot) then multiplies this by the old settings, resulting in settings to be used the next year. These new settings are carried to the next year so you don't have to manually enter the new settings.

It will work for a league of any size, and you will even be able to change the league size from year to year while using the same spreadsheet. You can also change the per team totals from year to year in case you want to change them to replicate different eras or something. You can also continue to add year after year of information just by copying and pasting the cells used for the calculations, so even if I only put space for 20 years in the sheet, just by copying and pasting you can make it last as many years as you want.

It has worked well for me, and is in a nice user-friendly and colorful format. I don't currently have web space to post it, and if demand is low (which I expect

) then I should be able to just email it to anyone interested. If a lot of people want it, then hopefully someone will be willing to post it online.

If you have any questions, I'd be more than happy to answer them,
Dave

dappawit · 08-13-2002, 02:37 PM

Ahh, you can attach files in this forum. Since the zipped spreadsheet is only about 15KB, I guess I'll go ahead and post it here.

Jermanfu · 08-13-2002, 04:17 PM

Dappawit, it seems you've put alot of time into this project. Very nice work. Your spreadsheet is very well done.

JimServo · 08-13-2002, 10:25 PM

Agreed. Very well done.

08-12-2002, 03:19 PM	#1
dappawit Minors (Rookie Ball) Join Date: Jul 2002 Location: Raleigh, NC Posts: 34	Interested in my League Totals Spreadsheet? I have my own spreadsheet I use to keep track of and suggest League Totals season to season. There are a few such spreadsheets around, and I don't know if mine is different enough (or good enough ) that's its worth sharing, but I like it and maybe so will someone else. Anyway, my Excel spreadsheet works like this... For the first season you use it, you enter the league settings you already have, the results you want per team, the actual league totals you get at the end of the season, and the number of teams in your league. (The only real tedious part is adding up the league totals manually and entering them.) It will spit out new league totals which try to correct for the difference between the league totals and what you wanted per team. For each year after the first, you just have to enter the results you want per team, the actual league totals, and the number of teams in your league. The new league settings are carried over automatically to the next year entry in the spreadsheet. Now for an explanation of the math... To calculate the new league totals, it takes the per-team totals you wanted, multiplies it by the number of teams in your league (to get expected league wide totals), then divides that by the actual totals. This should result in a number close to one, and the closer it is to one, the closer the expected stats are to the actual ones. Now, to get the new settings, it adds one to this number and divides by two (by adding one an dividing by 2, this acts as a damper of sorts and more or less prevents the settings from being too high one year, then equally too low the next, which happened to me alot) then multiplies this by the old settings, resulting in settings to be used the next year. These new settings are carried to the next year so you don't have to manually enter the new settings. It will work for a league of any size, and you will even be able to change the league size from year to year while using the same spreadsheet. You can also change the per team totals from year to year in case you want to change them to replicate different eras or something. You can also continue to add year after year of information just by copying and pasting the cells used for the calculations, so even if I only put space for 20 years in the sheet, just by copying and pasting you can make it last as many years as you want. It has worked well for me, and is in a nice user-friendly and colorful format. I don't currently have web space to post it, and if demand is low (which I expect ) then I should be able to just email it to anyone interested. If a lot of people want it, then hopefully someone will be willing to post it online. If you have any questions, I'd be more than happy to answer them, Dave __________________ "The bases were drunk, and I painted the black with my best yakker. But Blue squezzed me, and I went full. I came back with my heater, but the stick flares one the other way and chalk flies for two bases. Three earnies! Next thing I know, skipper hooks me and I'm sipping suds with the clubby." - Ed Lynch Last edited by dappawit; 08-13-2002 at 02:38 PM.

08-13-2002, 04:17 PM	#3
Jermanfu All Star Reserve Join Date: Mar 2002 Location: Baton Rouge, LA. Posts: 899	Dappawit, it seems you've put alot of time into this project. Very nice work. Your spreadsheet is very well done. __________________ "I took the two most expensive asprins in history." - Wally Pipp on his decision to sit out a game with a headache which let Lou Gehrig into the lineup for the first time. http://s10.photobucket.com/albums/a121/Jermanfu/

08-13-2002, 10:25 PM	#4
JimServo Major Leagues Join Date: Dec 2001 Location: Near Philadelphia, PA Posts: 418	Agreed. Very well done. __________________ "When I was a small boy in Kansas, a friend of mine and I went fishing and as we sat there in the warmth of the summer afternoon on a river bank, we talked about what we wanted to do when we grew up. I told him that I wanted to be a real major league baseball player, a genuine professional like Honus Wagner. My friend said that he'd like to be President of the United States. Neither of us got our wish." -Dwight D. Eisenhower