Formula to ramp values over a variable range
Our take
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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Solving for (constant/unknown=given value) where I give the desired output of a ratio and get the initial value needed.Excel Version Office 365 ver. 2603 build 19822.20182 click-to-run Excel Environment Windows desktop Your Knowledge Level Haven't touched excel in decades I have a formula where I input a value and it splits it in 3 ratios (0.1:50:50). D26 has the formula (=$A$1/100.1) to get the value of one part. B1-3 have the ratios 0.1, 50, and 50. C1-3 have the formulae (B1-3*$D$26) to get the shares of A1 by each ratio. I'm looking for a formula where I can input a desired share amount for any of the ratios and get the initial value needed to produce that share EDIT: I know I'm probably waayyyyyyy overthinking this, but its driving me crazy EDIT2: Example - An A1 value of 500 gives: a D26 of 4.995005, and C1-3 of 0.4995, 249.7502 and 249.7502. I want to, for example, input a C1 of 500 and get the A1 required to get that. Which, plugging-and-chugging my example, would be 500500 submitted by /u/LordSloth113 [link] [comments]
- Increment row but not column when copying a formula?Trying to fix an old spreadsheet at work. The current formula is this: =SUM(R6,R16,R26,R36,R46,R56) I need to build 4 more columns to the right of the existing one with the same formula, that increments the row but leaves the column the same, like this: =SUM(R7,R17,R27,R37,R47,R57) I don't think I can use $ for this as that always locks the column right? Photo Example below. I need to add three more Flav columns on the right hand side, pulling from lists like on the left hand side. https://preview.redd.it/v9iyxye9xrwg1.png?width=604&format=png&auto=webp&s=c0ad4b7502a0ec344221e29965de8dda99ed045e submitted by /u/fullofpaint [link] [comments]
- I need to adjust rows of values based on a percentage against fixed values on my recipe calculator.I’ve tried the Ai route and it’s helped, but I can’t quite nail this. I’m struggling with the formulas to execute. Excuse poor terminology, this is not my field of expertise. I’m writing a recipe calculator with the following columns- ingredients (C), grams (D), percentage of ingredient of total recipe (E), sugar % (F), fat % (G), protein % (H), carbs % (I), sodium % (J) and calories (K). The ingredients and nutritional % relating to each ingredient is populated via a dropdown list pulled from a table on another worksheet in the same work book. My goal is to have 20 rows of ingredients. 5 rows (rows 3-7) will have fixed weight (grams) values that don’t change as I make adjustments. The remaining 15 rows (rows 8-22), I would like to adjust the grams required in the 15 rows to achieve the % result I need of the nutritional value I determine. The bottom of the table will have a total % of each nutritional value that I want to be able to adjust. M For example- 1000g ice cream recipe requires 22% sugar Row 3 - 476g Cream = 2.6% sugar Row 4 - 204g Milk = 4.8% sugar Row 5 - 3g Vanilla Bean = 0% sugar Row 6 - 149g Egg Yolk = 0.2% sugar Row 8 - 125g Caster Sugar = 100% sugar Row 9 - 43g Glucose = 80% sugar This recipe = 31.27% sugar. I’d like to enter the % value at the bottom of the table, to change the weight of ingredients in rows 8-22. To further explain, in this example, i want the added sugar (caster sugar & glucose) to update their weights to calculate the final percentage as required. I would also like to do this with fat as well in the future. submitted by /u/occasional_superhero [link] [comments]
- Tiered Results Formula with Multiple OutcomesI know the basics but I'm a lil stumped on a formula to use for this specific situation. I have to build payroll spreadsheets for every employee based on their pay plans. Some of their plans were changed where they get a larger percentages based on the gross total for that month. To keep it simple, how would I build something to give a different percent based on the total in one cell. Just to keep it simple for this example. 0-100 pays 1% 101-300 pays 1.25% 300+ pays 1.5% I've done similar IF formulas but never quite exactly like this situation. I appreciate the help, as I'm still just learning and couldn't find an exact answer from Google based on how I was trying to phrase it. submitted by /u/swidgen504 [link] [comments]