Add weighted variable to existing value

**macross** · 12-27-2010, 01:06 AM

Hope someone can help with what I am, trying to do

I have a spreadsheet with numerous values in cells. I need a formula to modify those cells with a number in a weighted range.

e.g.

Say I have the value 75 in a cell.. lets say B2 if it matters
I want to add 0,1,2,3,OR 4 to it
29% chance it will be 0, 30% it will be 1, 20% it will be 3, and 21% it will be 4

It doesn't need to output this new value to a new cell, simply modify the existing cell.

Truly appreciate any help you more experienced users can provide.

**squiggler47** · 12-27-2010, 02:25 AM

=LOOKUP(RAND(),{0,0.29,0.59,0.79,0.99},{0,1,3,4,2})+$B$2
note that this part of the formula below is the sum of the chances :-
0.29 chance (29%) of 0, 0.59 = 30%+29% chance of 1, 0.79 = 30%+29%+20% chance of 3

{0,0.29,0.59,0.79,0.99}

since you left out the number 2 I wasnt quite sure how that came into it, so I added it to the end with a 1% chance!

**squiggler47** · 12-27-2010, 02:27 AM

Note you cant modify the existing value with a formula, unless you use VBA or the value is generated with a formula in which case you can add the value to the cell.

**macross** · 12-27-2010, 02:45 AM

I had actually figured out another way to do this before you replied, but your solution is much cleaner and will allow me to do a little more than what I came up with. I can live without the direct cell output so that is fine too.

I don't suppose there is an easy way to add an IF type statement to this so that the formula won't apply itself if it finds a certain value in another cell

e.g.

A1=75 B1=1
A2=70 B2=2
A3=85 B3=10
etc etc

Have your formula apply to the A cell but have the formula not modify the A value if the adjacent B value = 2 (Still needs to output the unmodified A values though in those cases.

**squiggler47** · 12-27-2010, 02:54 AM

=if(A2=2,$b$2,LOOKUP(RAND(),{0,0.29,0.59,0.79,0.99},{0,1,3,4,2})+$B$2)

**macross** · 12-27-2010, 02:56 AM

Wonderful, thank you!

**macross** · 12-27-2010, 11:35 AM

So I took your formula and expanded on it, this look ok to you still?

Please Login or Register  to view this content.

It is supposed to allow for 3 weighted variables based on the value of an additional cell but also still returns the original value if one cell = 1009 or that other cell is 3+

**squiggler47** · 12-27-2010, 11:41 AM

you could do this to shorten it!

=IF($J3=1009,A3,A3+CHOOSE($G3+1,LOOKUP(RAND(),{0,0.25,0.6,0.85,0.95},{0,1,2,3,4}),LOOKUP(RAND(),{0,0.45,0.85,0.95},{0,1,2,3}),LOOKUP(RAND(),{0,0.75,0.95},{0,1,2}),0,0))