StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAccurate realtime updating solution for distribution prediction based on current statistics
text
Body
copied!<p>As a part of my current project I need to find a pretty Excel solution for the following: there's online game where people gain experience (similar to SO rep) and move to the next level as soon as enough exp is gained (again, this is very much like SO privileges). Sure thing this requires different time for everyone: some get enough exp during the 1st week, others need 5 weeks to pass the level. Statistics is the following:</p> <pre><code>Week from join: Week 1 Week 2 Week 3 Week 4 Week 5 Level up: 5% 15% 40% 30% 10% </code></pre> <p>That is, if during this week we had some people joined the game, 5 weeks after all of them will pass to the next level (some will give up and leave, but for the simplicity I do not include that factor in the list of conditions).</p> <p>As for every game, the number of current players matters. To keep the game popular and exciting, there should be definite <strong>minimal</strong> number of players on each level. Obviously, this value is changing constantly: new people come, while others move to the next level. However - and that's where the action begins! - this <strong>minimal value of current players is NOT constant</strong> and may wary depending on many factors. For the simplicity it means that for future 10-15 weeks there's a set of predicted minimal amounts of players for each week that should be achieved (this is based on collected statistics, but for the given task this is a <strong>known</strong> set).</p> <p>So far so good, the picture looks like this:</p> <p><img src="https://i.stack.imgur.com/anTOK.png" alt="Sample 1"></p> <p>While in the leftmost column there are numbers of people <strong>joined the game each week</strong>, row of bold numbers under each week is the number of people <strong>passed during this week to the next level</strong>.</p> <p>Having numbers of newcomers, the calculation of total resulting level ups for each week is trivial. The opposite is not at all such easy (well, at least for me) - and that IS the question: <strong>how to predict the required number of newcomers for each week</strong> (leftmost column) - to obtain the desired number of people who will pass to the next level during next weeks (bold row in the bottom)?</p> <p>I still can't find an appropriate and accurate solution for 2 weeks so far. I tried all possible options - from complex & array formulas to Solver addin, but no luck: I always have more unknowns than equations. However, I'm pretty sure this is not an impossible task: for some solutions Solver could provide numbers fairly close to the desired, but not for every set of desired values.</p> <p>Some assumptions and (perhaps) important conditions:</p> <ol> <li>Though should not be a problem, but actual weekly distribution %%% numbers are <strong>updated on demand basis</strong> via DB query and further Excel processing.</li> <li>Actual newcomer values available so far from the DB are of course considered as <strong>known values</strong> for the equations. Obviously they cannot be changed, and e.g. if one week <code>less than desired</code> number of people joined the game in fact, that should affect calculated predictions for future weeks to achieve overall.</li> <li><strong>Total sum of players</strong> actually passed to the next level <strong>should remain unchanged</strong> for the certain time period: e.g. if we want that during each winter week starting Jan, 1 at least 100 people join the game, that gives us 8 full weeks and therefore 800 gamers in total. However, in case in January we actually got 80 new players each week - it means hard work for February) For certainty "checkpoint" <strong>subtotals are calculated every 10 weeks</strong>. In any case, any low values must be compensated during several upcoming weeks.</li> <li>The opposite is also true: if we have <strong>more newcomers than expected</strong> for the given week - we may attract less players during next week(s) to meet the next subtotal goal.</li> <li><strong>The flow of players is infinite</strong> - picture sample is given just for the purpose of clarity and better understanding. Desired values may be calculated for as long in the future as required - prediction pattern is repeatable. If that matters for the solution - let it be <strong>for 10 weeks from current</strong>.</li> <li>Pure formula solution is preferable - even very complicated. However, VBA is still an acceptable option. In any case, accurate realtime / on demand update of calculated values is absolutely better than simple, but semi-manual or approximate solution.</li> </ol> <p>As for my every question here - I don't ask for the <code>ready to play</code> solution, but the right guidance or a valuable advice will be very much appreciated. I deliberately do not post this to <a href="https://stats.stackexchange.com/">https://stats.stackexchange.com/</a> - it's more like programming question, since the theory is obviously simple... or perhaps too simple, so that I don't see quite obvious solution.</p> <p>Please feel free to request any additional details or explanations via comments: although I tried to be as clear and simple as possible, some aspects that are not important for me may be in fact vital for the accurate solution.</p> <p>Sample file (with the above sample): <a href="https://www.dropbox.com/s/q3bnagyet86i34r/StatPrognosis01.xlsx" rel="nofollow noreferrer">https://www.dropbox.com/s/q3bnagyet86i34r/StatPrognosis01.xlsx</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload