Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>No need for VBA or complex array formulas - some simple math converted to formulas will be all you need:</p> <p>The main idea is, that you can determine the number for each week by "reversing" the calculation. The logic is the following: (Note: cell references based on the screen shot, i.e. the yellow 5% would be B2):</p> <ul> <li>If 5% of one week's "joiners" make it to the next level and week 1 (overall) should have 10 people to make it, 10/5% = 200 people need to join in the first week.<br> As a formula, this means: B5:<code>=B12</code>, A5:<code>=B5/B2</code></li> <li>From there you can calculate how the joiners from the first week distribute as you already do (i.e. no change to the rest of row 5)</li> <li>In week 2, you want 35 people to be the next level. From the former step you know that from week 1 joiners you have 30 people reach that level. Therefore, 5 people must come from week 2 joiners. As they again make 5% of that wave, the total wave must be 5/5%=100<br> In formulas: C6:<code>=C12-C5</code>, A6: <code>=C6/B2</code></li> <li>...</li> </ul> <p>Now instead of creating an individual formula for each week, you can generalize this with some INDEX functions. For this, I extended your example by the week numbers as both header columns&amp;rows:</p> <p><img src="https://i.stack.imgur.com/y8LhD.jpg" alt="enter image description here"></p> <p>To calculate the number per "joiner wave per week", i.e. range C5:O17, use the following formula:</p> <pre> =IF($A5&gt;C$4+N("Nobody from this wave joined yet:"),0, IF(C$4=$A5+N("The first week for the new wave:"), IF($A5=1+N("Very first wave must be expected number in this week:"),C18, N("All other weeks, this must be expected number minus 'levelups' from former weeks:")+(C$18-SUM(C4:C$5)) ), N("For any later week after the joining, apply weekly uplevel ratio to total number of joiners:")+INDEX($C$2:$O$2,C$4-$A5+1)*$B5) ) </pre> <p>and for the desired number of joiners per week, i.e. range B5:B17:</p> <pre> =INDEX($C5:$O5,$A5)/$C$2 </pre> <p>Here's the updated <a href="https://www.dropbox.com/s/jk7kd270awma3wt/StatPrognosis01_Solution1.xlsx" rel="nofollow noreferrer">solution file</a>!</p>
    singulars
    1. This table or related slice is empty.
    1. POAccurate realtime updating solution for distribution prediction based on current statistics
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPeter L.
    1. USPeter Albert
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POAccurate realtime updating solution for distribution prediction based on current statistics
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks very much Peter! That saved me hours and hours of life. With all my respect, upvote and accept. Well done!
      singulars
      1. PO
      1. USPeter L.
    2. COUnfortunately, the solution returns negative values in case it does not exactly suit the initial distribution Pattern. Sorry, only had a chance to review it deeply a few days ago.
      singulars
      1. PO
      1. USPeter L.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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