How To Calculate Strength Of Schedule In Excel
08-21-2012,03:54 PM #1
Registered User 
formula for strength of schedule?
So I'chiliad trying to create a "strength of schedule" component for the football season. Having trouble thinking of a way to conform it to reflect the boilerplate value of a squad's opponents upwardly until the current day's games. I've attached a small spreadsheet attempting to show what I'one thousand trying to exercise... I manually filled in the values for "b" team's forcefulness of schedule with an caption of how I got to that number. Whatsoever ideas? Thanks!
08-22-2012,12:52 PM #two
Registered User
Re: formula for strength of schedule?
08-22-2012,01:32 PM #three
Re: formula for strength of schedule?
roasthawg,Attached is a modified version of your posted workbook.
First, I simplified the formulas in column Thou and H. In cell G2 and copied over and downwardly to H10 is this regular formula:
And so I addressed your actual question. In cell J2 and copied over and downward to cell K10 is this regular formula:
Is that what you're looking for?
Hope that helps,
~tigeravatarForum Rules: How to utilize code tags, mark a thread solved, and keep yourself out of problem
08-22-2012,03:39 PM #4
Registered User
Re: formula for strength of schedule?
I like the simplification of the get-go formula... thank you!Every bit far as what I'm afterward however, still not there. The formula you gave
looks like information technology'southward calculating the squad's defense. I demand the squad'southward opponents AVERAGES as of the date of the current game.For instance... on Jan four team "A" has played three games, one each against teams "B", "C", and "D". I desire to know the AVERAGE of team's "B", "C", and "D" pts scored and pts against for all games up thru January three. Make sense? If not, permit me know. Thanks for the help!
Terminal edited by Cutter; 08-22-2012 at 07:32 PM. Reason: Removed whole mail quote
08-22-2012,03:43 PM #5
Re: formula for strength of schedule?
roasthawg,The formula I provided gave correct results based on what you entered manually for team "b". When I checked it manually against team "a" it also provided right results. I know the formula doesn't utilize Average() but it does apply (Sumif()+Sumif())/Countif() in order to get the average. I had to get this road considering of using opposite columns to calculate opponent average.
If it is, in fact, yielding wrong results, please show me what effect is incorrect, what it should be instead, and how y'all arrived at that answer.
08-22-2012,03:56 PM #6
Registered User
Re: formula for force of schedule?
Deplorable, I wan't capitalizing AVERAGE every bit commentary on your use of sumifs instead of averageifs... only to try to clarify what I am later on.Ok, here's an example of an wrong result using your formula... in cell B8 we have squad "C". The date of this game is Jan three. Upwardly until this signal of the season team "C" has completed games confronting teams "Due east" and squad "F". Prior to the start of Jan 3rd's games team "E" averaged 3 as evidenced in cell G9 and team "F" averaged 3.v equally evidenced in cell H10. The average of these two numbers is the result I'm after... three.25 in this example. Hope that helps... cheers for taking time on this!
Last edited by Cutter; 08-22-2012 at 07:33 PM. Reason: Removed whole postal service quote
08-22-2012,04:38 PM #7
Re: formula for forcefulness of schedule?
roasthawg,Ah, ok. That makes things much more complicated. In order to use only formulas, I had to use the MCONCAT function which is provided by the Morefunc add-in.
Attached is version ii. Column Thousand is a helper column. In cell M2 and copied down is this formula:
Then, in jail cell J2 and copied downwardly to J10, then copied to cells K2:K10 is this array formula:
Information technology is very long considering it is very complex. Yous needed to get teams that had played confronting the current row's teams prior to the current row'southward date, and then boilerplate all of their scores together. This formula does that and is the only way I can think of to do then without resorting to VBA (a UDF or macro). It does nevertheless provide the right results for squad "b" and the corect results for team "c" and does and so using the logic described in your nearly recent post.
You can hide column M if you want.
08-22-2012,05:00 PM #8
Re: formula for strength of schedule?
Its in the "Excel Programming / VBA / Macros" subforum, that's true. Generally speaking though information technology is amend to avert the use of VBA unless absolutely necessary. User's posted workbook is a .xlsx file which by definition has no macros, and there were already assortment formulas in it, so I just kept with formulas. If OP would like a VBA solution I tin can provide that as well, but information technology didn't (and still doesn't) seem to be necessary.
08-22-2012,05:06 PM #ix
Registered User
Re: formula for strength of schedule?
Ok, wow... cheers and so much for the assist. Looks similar it does what I need so I'm gonna mark the thread as solved and then see if I can decipher the formula. Would a macro be easier in your stance? Thanks again for the help!
08-22-2012,05:09 PM #x
Re: formula for forcefulness of schedule?
A macro or UDF would certainly make it cleaner, and depending on your level of expertise with VBA it could certainly be easier. But then anytime yous opened the workbook you'd be prompted to enable macros, or if y'all distributed the workbook others would get the prompt and would exist very unlikely to know how to interact with macros at all.Still, if you'd like a VBA solution, let me know.
08-22-2012,05:14 PM #xi
Registered User
Re: formula for forcefulness of schedule?
I'd dear to come across a vba solution if not besides much trouble... I'm the simply one using the workbook and then no bug there. Thanks!
08-22-2012,06:06 PM #12
Re: formula for force of schedule?
roasthawg,Attached is v3. It contains a button that will get calculate the opponent average. This is the macro the button is assigned to:
08-22-2012,07:38 PM #thirteen
Registered User
Re: formula for force of schedule?
Thank you so much for all the help... I'm gonna play around with both of these and see which solution I like ameliorate!
Source: https://www.excelforum.com/excel-programming-vba-macros/854576-formula-for-strength-of-schedule.html
Posted by: mcqueengreasse.blogspot.com
0 Response to "How To Calculate Strength Of Schedule In Excel"
Post a Comment