Upgrade your account to a Community Supporter account and remove most of the site ads.

Reply to thread

Message: <blockquote data-quote="CapnZapp" data-source="post: 4617033" data-attributes="member: 12731">Here's my stab at an Excel formula (for xp). Using it is a two-step process.Step 1. Assuming column A holds the level as previously discussed (i.e. A1 is the header, A3-A31 holds the numbers 1-30), copy the following into cell B31:[code]=1000*MAX(A31-1;0)+250*IF(A31>2;SUM(A$2:A29))+250*IF(A31>6;SUM(A$2:A25))+500*IF(A31>10;SUM(A$2:A21)+1)+1000*IF(A31>14;SUM(A$2:A17))+2000*IF(A31>18;SUM(A$2:A13))+4000*IF(A31>20;SUM(A$2:A11))-5000*IF(A31>21;SUM(A$2:A10))+7000*IF(A31>22;SUM(A$2:A9))+15000*IF(A31>26;SUM(A$2:A5))[/code]...and hit enter.Step 2. Then CTRL+C that cell (B31), mark the entire column (cells B2:B31), and paste.Voila (I hope)!As you can see, I'm using the SUM function to compress each band of Delta 2 increase. (=Not having to have one entry for each level, which would kind of have made it look stupid to have a function at all...). This only works if you do copy-paste as instructed, so A31 becomes A30 for the cell above, and so on.You will get individual cell errors when each such reference reach A0, but this doesn't impact the final sums (in my Excel, anyway).There are three kinks that make the function bigger:#1: the extra +1 at 11th level (as this marks the beginning of a new "band", I don't need a whole extra IF)#2: the jump at 21, requiring the extra IF(>20)#3: the "backwards" jump at 22, requiring the extra IF(>21). Notice how this is subtracted from the total.Otherwise, you can see how the numbers multiplied always are the difference between the old and new delta 2 "band" number.I have a vague suspicion you could condense down the formula even further (not having to repeat all those IF(SUM). I simply don't have the Excel-fu to even begin to understand how to accomplish this, however. (Merely utilizing SUM in this way required some googling... on "number series excel" to be exact <img src="https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png" class="smilie smilie--emoji" loading="lazy" width="64" height="64" alt=":)" title="Smile    :)"  data-smilie="1"data-shortname=":)" /> )</blockquote>

Verification