Formula to ramp values over a variable range
Hi!
I'm working on a budget/staffing spreadsheet. Right now, given a horizontal row of data representing day/weeks, I can easily assign a "phase" to each row, and then have a set number of hours/budget automatically distribute across those cells. See formula for cell E2 in snip below. The first two "sets" of data in rows 2,3 and 4,5 are working just fine, no big deal. Total values for Concepts always add up to 40, total values for Schematic Design always add up to 80.
What I'd like to introduce is a "ramp factor" from 0-100% that will affect those values. See the desired outputs in red below (values are fake). When ramping factor is 0%, it's just straight division similar to examples up top. When the ramp factor is increased, the values "later" (right-most) in the resulting strings are weighted more heavily, and the "earlier" (left-most) values are weighted lighter. In effect the values are starting lower and "ramping up" to their conclusion, but the total per "phase" still matches up with the references values in columns B and C.
Any ideas on how to efficiently implement this without introducing cheater cells or VBA?
I'm not super concerning about the rounding - I can figure that out later. I realize it may result in the resulting sum not exactly matching the original values in columns B and C, but I can live with that. I just need to get in the ballpark.
Thanks all.
[link] [comments]
Want to read more?
Check out the full article on the original site