Trying to F9 sum of random numbers until certain value is reached

L

langba

New to macros.....

I'm trying to convince an engineer that there are many possibilities t
a tolerance stackup.

Cells A1:A10 are nominal dimensions. The value doesn't matter.

Cells B1:B10 are all randomly generated plus values from .001 to .060.

Cells C1:C10 are all randomly generate minus values from -.001 t
-.060.

Cell C11 is sum(B1:C10).

What I do is hit F9 until my arm falls off until C11 is .030.

That's what the engineer is looking for over 10 dimensions, .030.

I want a macro to calculate until C11 = .030

I'm trying to show that there are many possibilities.

I read the Visual Basic help pages for hours but defer to you, th
experts.

What I have started with is:

Sub Regen()
If C11 <> .030 Then
Calculate
End If
End Sub

I know that won't work because it doesn't.

Thanks in advance
 
B

Bob Phillips

Take a look at Goal Seek in Help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
L

langba

Thanks. I don't think Goal Seek is what I'm looking for. Unless I'
using it wrong. Help wasn't helpful
 
T

Tom Ogilvy

In B1 I put

=ROUND(RAND()*0.059+0.001,3)+0*D1

in B2 I put
=ROUND(RAND()*0.059+0.001,3)
and copied B2 down

In C1 I put
=-1*=ROUND(RAND()*0.059+0.001,3)

In E1 i put

=Sum(B1:C10)

Leave D1 blank or put in any number

I then did goalseek and specified

Set Cell E1
To value .03
by changing cell D1

D1 is just a dummy cell to stimulate calculation.

In tools options, I changed Max iterations to 1000 under the calculate tab.


Worked consistently for me.
 
T

Tom Ogilvy

Just to clarify

In C1 I put
=-1*=ROUND(RAND()*0.059+0.001,3)

and copied that down to C10 (same with the formula in B2, copied down to
B10)

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
In B1 I put

=ROUND(RAND()*0.059+0.001,3)+0*D1

in B2 I put
=ROUND(RAND()*0.059+0.001,3)
and copied B2 down

In C1 I put
=-1*=ROUND(RAND()*0.059+0.001,3)

In E1 i put

=Sum(B1:C10)

Leave D1 blank or put in any number

I then did goalseek and specified

Set Cell E1
To value .03
by changing cell D1

D1 is just a dummy cell to stimulate calculation.

In tools options, I changed Max iterations to 1000 under the calculate tab.


Worked consistently for me.

--
Regards,
Tom Ogilvy
Click to expand...
 
D

Dana DeLouis

I'm trying to convince an engineer that there are many possibilities to
a tolerance stackup.
Click to expand...

If all you are doing is trying to "convince", then here is just one way.
Put .060 in B1, and -.030 in C1. This totals your .030.
Then whatever you put in B2, put the negative in C2.
If C3=-B3 (copied down), then the Total will always add to .030.
Another way would be to generate Random numbers in the remaining B2:B10, and
just rearrange the negative of these same numbers in C2:C10.

You could then put .050 in B1 and -.020 in C1, for a total of .030. Etc.
As you can see, there are many possibilities.

When using Solver or Goal Seek, I often find it easy to scale the problem.
In other words, multiply everything by 1000. You are generating numbers
from +- 1-60, and looking for a sum of 30.

Most likely, you will never test for an exact .030 due to rounding issues
(from what I see)
 
Top