Adjust growth rate based on rank or count

So I've been tackling a problem for a few hours, and I just can't figure out how to approach it. I know what I want to do, but not how to do it.

I'm making an automated character sheet, and for the most part it's simple. The user selects various values from dropdowns on the first tab, and that determines values behind the scenes in the second tab. Stats are ranked effectively from 0-16, and their values adjust accordingly. All simple math.

The trouble comes with the option to change the growth rate. When selecting their ranks, they are required to total up to 72. Faster/Slower growths rates adjust the final values based on the ranks they provided. Here's what I want to do...

I want to adjust things up or down on a curve. Values 0 and 16 don't change at all, as those are the min and max values respectively. The closer to the central 8 value, the stronger the effect of the modified value is. The total amount of change applied should match the value given by the growth rate modifier (if values average at 50%, then if rate value is 3, the new overall average would need to be 53%).

B5U9NiX.png

Here's the part of the worksheet I'm working on. I've tried a few different things, such as using a countif to check for multiple instances of the same value, modifiers based on a bell curve, etc. I can get the final value close to the XP Mod Target, but if I change any values it throws everything off anyways. At this point I'm taking shots in the dark and really have no idea what I'm doing, just what I want.

If it helps, I'd like to attach the file in question. I cannot figure out how though, so if y'all think it'll help to mess around in the file itself, I'd appreciate the assistance in uploading. I had thought it was the attach button, but when I try to use it it gives a blank dropdown, so I'm guessing I'm supposed to upload it elsewhere, but cannot find out where.

Edit: Thanks to protonLeah for helping me figure out how to upload the spreadsheet in question! The parts of the problem can be found in the second tab, labeled "VenusWork".
Edit2: Thanks to protonLeah again for the title suggestion. I hope that helps out more!

Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
  
• Your post title should describe your problem, I.e what you are trying to do, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

Do you have some recommendations on what I can use to be more specific? Would "trying to match a weighted value to specific value" work, or do you have a better way to express it? I'm not too good at expressing things properly in words, so your help here would be welcome.

Edit: Will that work, or do y'all have something that would help out more?
Edit2: Thanks to protonLeah for the help with this!

That's fine now - thank you.

There have been several views of your file, but so far no suggestions. This probably means that people are not understanding what you want help with. I have looked twice and am not at all clear on what you are after - sorry. Maybe it's time to calrify by clearly mcoking up manually what you want to see in your spreadsheet.

I'm struggling at the first hurdle! Which are the nine values you refer to? Which cells?

On the first tab, there's a section that looks like this:

Sample4.png

As the user chooses different values from the dropdown menu, this row gets affected in the second tab:

Sample5.png

As I mentioned later on in the post, the next three rows convert the rank into a number.

Sample6.png

Then, in the first tab again, in the section above, the user changes the growth rates where the arrow points.

Sample7.png

That gets reflected here, which modifies the values below it. The range of Fast to Slow is given numbers from -3 to +3. This changes the percentage that the final values should match, from the base 50% it starts off at, to modifying the numbers to reach an overall average of 47% to 53% based on what the value is.

The rest of the information is provided in my previous post, where I explain what each row does.

It's all way above my head - sorry.

I have called on the wider community for help.

Like Ali, I'm not entirely sure where all of the numbers are coming from or where they're supposed to go, but here's my guess:

My interpretation of the request is that in cells B29:J29, you want formulas that modify B28:J28 such that the overall total of the figures in B28:J28 have been increased/reduced by a percentage determined by M29. However, you also want it weighted such that the alteration to the figures in B28:J28 is proportionally greater the closer the value in B25:J25 gets to 8. Correct? Close? Maybe?

I went with the formula below in B29, filled right through J29. The formula must be array-entered (confirmed with Ctrl + Shift + Enter instead of the regular Enter):

=B28+(SUM($B$28:$J$28)*(0.01*$M$29))*ABS(ABS(B25-8)-8)/SUM(ABS(ABS($B25:$J25-8)-8))

Experiment with the attachment a bit to see if it works as desired. You can delete O28:O29, I just included those to measure how I was faring. It looks like you want K33 to equal M33, and my solution comes close, but in K33 you're taking an average of percentages of proportionally determined decimals, so they're off by a fraction. My goal was that if M29 is -2, then row 29 should be 2% less than row 28, following the proportional distribution described above. If M29 is +3, it should be 3% greater. Etc. Hopefully that's close to what you meant?

It's really close, but the numbers are slightly off... but it does exactly what I need it to do! If I use the formulas you provided, but change the point augmentation of the growth rate from +/- 1 to +/-1.1, it works exactly (ex: If at "Faster" growth rate, if I have it be -2.2, the formula results in a 48%, which is the -2 it should be form the base of 50%). Could you walk me through step by step in the formula so I can see how it works? Hopefully should I have this problem in the future I can use your method (or a variation of it) to get the results I need.

Edit: Aggh, or not. Spoke too soon. Tweaking some of the values in the the first tab changes the result away from what I'm trying to get. Give me a sec to upload some stuff to help...

Example1.png
The arrows show what is determining what. On the first tab, the ranks the user enters are reflected in the second tab, where all the work happens.
The next step converts those ranks into numbers - these numbers determine the ADD value below.
Growth Rate is reflected similarly. Like ranks, each value is associated with a different number (from -3 to +3).
When there is no growth rate modifier, the Percent and Mod Percent lines both average to 50.
When there is a growth rate modifier, Mod percent must equal the experience target mod. Because that's what's being worked on, that's what we need to reach.

Example2.png
When you change the values on the first tab, they're reflected in the second tab. When changed, they have to match the value. This is where I caught the issue - when changing values, the formulas went form just needing to modify what the growth mod is to that no longer working.


I've uploaded an updated version of the sheet including your formulas so you can experiment with it more. I think you're on the right track - this is better than anything I've been able to come up with. I hope that helps you understand where the numbers are coming from. If you need more clarification, could you specify which numbers you're trying to find? The more specific you are, the better I can be explaining what comes form where, and what needs to be what. If it helps, Base value is the minimum value, and I've added a Diff row to show the difference between the max and min values for each stat (the number a rank corresponds to represents a certain number of sixtheenths of the diff value. For example, a D- results in a 4/16ths of the Diff value. If there is a faster/slower growth rate, this value gets tweaked based on the other stats, then the final value is added to the base in the ModPct row).

I'm glad I could (almost) help! The numbers are off because I didn't quite understand what you were attempting to do, and it seemed sounder to make the X% adjustment to the actual stats, rather than the average of the stats' percentages. That's what my initial effort does. The crucial part I didn't understand was the need/role for the bit with the percentages in rows 33:35. I still don't quite get it, but I don't think I need to - I'm pretty sure I can make the math work. Try the following in B30. As before, it must be array-entered with Ctrl + Shift + Enter:

=B$29+(0.09*$M$30*ABS(ABS(B$25-8)-8)/SUM(ABS(ABS($B25:$J25-8)-8)))*B$28

Fill right through J30, and the totals should adjust such that the average of the percentages in B34:J34 is adjusted according to the input in M30. Experiment with the attachment a bit to see if it holds while you tinker with the character build/settings.

Excellent! I'm glad we got there. Here's my attempt at an explanation, which we'll do from the inside out:

1) ABS(B$25-8): Takes the RankVal in row 25 and turns it into "Distance from 8"

2) ABS(ABS(B$25-8)-8): Takes the "Distance from 8" and inverts it, making it "Closeness to 8", so that you get lower values as the RankVal approaches 0 or 16 and higher values as you approach 8.

3) SUM(ABS(ABS($B25:$J25-8)-8)): Does the exact same as steps 1 and 2, but it's an array formula that calculates the "Closeness to 8" for all 9 categories, then SUMs them, giving us the total closeness to 8 for all 9 categories

4) ABS(ABS(B$25-8)-8)/SUM(ABS(ABS($B25:$J25-8)-8)): This creates a fraction, dividing each particular category's closeness to 8 by the summed total closeness to 8. This step basically gives us the proportion of the adjustment that must be applied to this particular category (column B, or Health, in this case).

5) 0.09*$M$30: There are 9 categories, and M30 gives us the percentage point value that must be adjusted, so .09*M30 gives us the total percentage point shift that we must see. To clarify, the unmodified average percentage is .50 (i.e. 50%) across 9 categories, so the sum of those percentages is 4.5 (.50 * 9 categories). If we're going to boost the average percentage by 2% (i.e. M30 = 2), then we need the sum of the percentages to increase .09 * 2 = .18. We need the sum of the percentages to equal 4.68, in other words, so that when we take the average of the 9 categories, we get an average of .52 (52%). This step basically tells us the combined magnitude of the adjustment that needs to be made.

6) 0.09*$M$30*ABS(ABS(B$25-8)-8)/SUM(ABS(ABS($B25:$J25-8)-8)): Having calculated the combined magnitude of the adjustment, we then multiply it by the proportion of the adjustment that should come from this particular category (column B/health), based on the nearness of the category's RankVal to 8. The result of this step is the percentage change we need, but the final result needs to apply this percentage change to the ADD value in row 29, so:

7) (0.09*$M$30*ABS(ABS(B$25-8)-8)/SUM(ABS(ABS($B25:$J25-8)-8)))*B$28: Your percent calculation is B29/B28 (Add/Diff). We know the adjusted percentage amount we need based on the last step, so to transform that into the adjustment needed to B29 to achieve that percentage adjustment, we multiply our adjusted percentage amount by B28, telling us how much we need to increase B29 in order to achieve the target percentage adjustment.

8) B$29+(0.09*$M$30*ABS(ABS(B$25-8)-8)/SUM(ABS(ABS($B25:$J25-8)-8)))*B$28: Having calculated how much we need to increase B29, we then add that increase to the existing B29, giving us our Mod value.

Hopefully that made sense? If you select B30 and go to Formulas > Evaluate Formula, you can watch Excel step through the formula one calculation at a time (in the proper order), which can prove instructive. I use it all the time when I'm trying to build a complex solution in a piecemeal fashion.