|
I'm currently using a very basic formula for weighted averages: (B2*0.1)+(C2*0.1)+(D2*0.1)+(E2*0.2)+(F2*0.25)+(J2*0.25) This is for predictions, and I'd like to run a Monte Carlo experiment on it: do it, say, 5000 times with different weights. The trouble is I can't figure out how to constrain the weights. At the very least, I'd need them all to sum to 1, and I can't do that by just multiplying by RAND(). I could approximate it by doing RANDBETWEEN() (and dividing by 100) but that could still be quite a bit off in either direction. I searched online and most of the solutions I found were to make five of them random and the sixth 1 minus the sum of the other multipliers, which won't work for me; not only am I not sure offhand how to write that formula so that it works 5000 times, but also, if I'm doing this 5000 times, many of those 1-SUMs will end up negative. Is the RANDBETWEEN method my best option, or is there a smarter way to do this?
|
#
¿
Sep 28, 2020 13:30
|
|
|
|
| # ¿ May 11, 2024 13:29 |
|
|
Thank you both! Unfortunately, I'm not very good at macros, but esquilax's method got me really close to what I was looking for.esquilax posted:I couldn't gather from your post whether each weight is uniform from 0-100% or if it is constrained in some way (e.g. weight on B is expected to be normal with mean 0.1 and stdev 0.02) but this method works under any approach to generating the random weights. This is a new project, so the original weights were based on "what makes this formula come close to actual results in the data I already have?" Now I have them as RANDBETWEEN(X,Y)/10000, where X and Y are eyeballed based on the original weight's value (and divided by 10000 to get them between 0 and 1). esquilax posted:Note that this is not mathematically rigorous and probably would not fly in an academic setting - it takes whatever probability distributions you start out with and "tweaks" them in order to fit into the constraints. Good thing I'm in a corporate setting instead of an academic setting. 
|
|
#
¿
Sep 28, 2020 17:12
|
|
|
esquilax posted:In that case, one caveat from my method is that the tweaks it does to probability distributions might generate weights outside of your range. E.g. if your randbetween() is designed to constrain the B weight randomly between 0.05 and 0.15, it might end up using something like 0.04 or 0.18 (which would be outside of the 0.05 to 0.15 range). It sounds like that's ok based on what you've said. Yup, that's totally fine, but thank you for pointing it out!
|
|
#
¿
Sep 28, 2020 18:17
|
|
|
I feel like this is something thousands of people probably do in Excel every day and I just don't know the right terms to find their solutions, but maybe I'm wrong and it is complicated. I have one master sheet with 145 columns. I have a second input sheet with 101 columns, all of which are in the master sheet, all in the same order, but not all grouped together; the other 44 columns from the master sheet are mostly formulas based on those 101 columns, and appear at various points in between. The data in the input sheet is compiled weekly and appended to the master. What I'm looking for is a way to have probably another sheet, blank except for the column headers from the master sheet, that can pull the data from the input sheet into the columns with matching headers, or alternately, a way to insert blank columns in the input sheet where the master-sheet-only columns exist in the master. The end goal either way is to be able to copy the entire input sheet and paste it at the bottom of the master sheet without doing manual column organization (which is easy to screw up). Then I can just pull down the formulas in the blank columns and be done. Any thoughts would be appreciated. Note that I'm a lousy coder, if that affects your advice.
|
|
#
¿
Jan 23, 2024 23:12
|
|
|
I must either be explaining this wrong or completely misunderstanding, sorry.HootTheOwl posted:Can you move all the function columns over and then copy paste into the blank space? No, the column order has to be maintained, unfortunately. HootTheOwl posted:Or you can have your blank values be equal to the input sheet (=inputSheet!A2) and then anything you paste into the input sheet will change this new sheet. This is 3000–5000 rows per week. That'd be a lot of =s, and a bunch of N/As when some of them were blank in the input sheet. (It's also a fresh CSV every time, but I could rename it.) DarkHorse posted:It's not the most sophisticated way, but it's probably about the speed you're operating at based on your description. Use Column Groups to either make the master sheet hide the calculations columns, or insert blank columns in the Input sheet and collapse them until you're ready to copy/paste. There's a button to (un)collapse all which will save you time either way. This didn't work. I created a template sheet with the columns from the master sheet, grouped the columns that don't appear in the input, collapsed all, then tried to copy and paste from the input sheet. The data pasted into the collapsed columns. If a visual would be more clear, let's say this is the master sheet:  And this is the input sheet:  I'm looking for a less manual way to get here:  from where I could just drag the formula columns down and be done.
|
|
#
¿
Jan 24, 2024 03:58
|
|
|
DarkHorse posted:You'll have to use your template sheet, it's going to replace your input sheet. If you're testing things out, first copy your input sheet data to the template sheet manually. Copy from the now filled out template sheet (presumably with columns collapsed) and paste into your master sheet and everything should be in the right spot. Unfortunately, the input sheet is a system-generated CSV, so I can't replace it this way.
|
|
#
¿
Jan 24, 2024 04:34
|
|
|
|
| # ¿ May 11, 2024 13:29 |
|
|
DarkHorse posted:The Power Query method might be a good idea for you too then. Thanks. I've been screwing around with it all morning without success, but I'll keep looking at it.
|
|
#
¿
Jan 24, 2024 14:43
|
|