•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Solving for (constant/unknown=given value) where I give the desired output of a ratio and get the initial value needed.
Our take
To solve for the initial value needed in Excel when you have a desired share amount for a specific ratio, you can use a straightforward formula. Given your existing setup with ratios of 0.1, 50, and 50, you can input the desired share amount into one of the cells, such as C1. The formula to calculate the required initial value (A1) can be expressed as: A1 = (C1 * 100.1) / B1.
- 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
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Formula to ramp values over a variable rangeHi! 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. https://preview.redd.it/p4egj1rz8usg1.png?width=1607&format=png&auto=webp&s=f49a5bbe794032bcdb177a03bd01957906397d80 submitted by /u/bikesaremagic [link] [comments]
- Solve equation system while allowing an error marginHello. To preface, I am a complete beginner, and am using Excel 2021 in French. I've been fighting a losing battle against a spreadsheet for a few days. In a nutshell, I'm trying to create a nutrition spreadsheet to calculate the amount of each ingredient I have to use in my meal to reach my calories and macronutrients targets. This will be linked to an ingredient randomizer, which will generate a 3-ingredient recipe each time the spreadsheet is refreshed. I've tried to solve the equation system using matrices. The resulting (translated) formula would be =MMULT(MINVERSE(C3:E5);I6:I8), where C3:E5 (green) is a square matrix with the ingredients' nutritional value and I6:I8 (yellow) is the target weight of each macronutrient. https://preview.redd.it/pkbhn9jvb1og1.png?width=897&format=png&auto=webp&s=3d298ca76fff21414821ce50ebeefabade10fe2e The problem, as seen in the example above, is that I end up with barely any chicken/rice and 2kg of broccoli. My formula gives me the only exact solution, whereas I need an approximate solution that stays within practical boundaries. I'm sure there is a formula to apply upper and lower limits to a result (which I have yet to research), but I'm afraid that wouldn't entirely solve the problem since it would probably end up creating impossible equations. Using the solver is also out of the question, since I need a formula that will automatically calculate each time I open/refresh the spreadsheet. I'm trying to figure out a way to add some leeway while entering the initial calorie target, potentially offsetting total calorie count by ~50kcals as needed to provide realistic ingredients weights. Any help or advice would be much appreciated, please let me know I if can provide more info/clarification. Thanks in advance submitted by /u/moriarty222 [link] [comments]
- Get rid of rounding errorHello, got a problem with a spreadsheet at work. For an calculation I need to calculate the average Full time equivalent of staff. Sheet is set up like this this: Column A: Person ID Column B: duration of period in days Column C: full-time equivalent Column D: weighted full-time equivalent for period Column E: average Full time equivalent over year Column A to C are preset base data. Formulas are in column D and E Calculation is basically done, broken down the formula is: In column D: =ROUND([duration days]/365×[Full time eqivalent for period];4) In column E it is: =SUMIF([Person ID]; [Person ID]; [Weighted for period]) But because if the rounding it doesn't add up to 1, it gies to 0,9999, even if the full year average is 1. Any ideas, how to fix that? (Other than IF(0,9999; 1; do the math) 😂) Was thinking about something like " if all Full time equivalent for period (column C) for one person ID (column A) are 1,0000, then make the average over year 1,0000, if not make the calculation based on my current formula for column E" but I can't get the operators right for that. Thanks in advance fir any help😂 Edit: moving rounding to column E instead of D did the trick. Sometimes its the obvious stuff xD Thanks to all submitted by /u/AttemptSlow612 [link] [comments]
- Need a formula to calculate performance percentages relating to time taken versus time expected standardsI am wanting to create what feels like a complex formula. Essentially, I have a table at work (which i cannot share for hopefully obvious reasons) that helps me convert time taken into a decimal hour. That's great and all, but I would also like to get a real time percentage comparing how long I took versus how long I should have taken. I work different processes and each one has its own standard of time to complete. I also have to record how many of each process I do every day. An example would be Process A requires one completion every 45 minutes, while Process B requires one completion every 3 hours, and Process C requires one completion every 5.5 hours. I want to compare my rate of completion in real time as the day progresses. Like, I completed one Process A in 55 minutes, so my rate is at 81.8% instead of 100%, or maybe I only took 35 minutes so my rate is 128.57%. Anyone have a suggestion that will compare the desired rate from one column to my actual rate in another column, with specification of process names? I consider myself intermediate at Excel, at best. TIA! submitted by /u/AlgPond [link] [comments]