Results 1 to 7 of 7
Thread: Bypassing Scenarios (Ex 2002)

20031215, 16:24 #1
 Join Date
 Jul 2002
 Location
 Efrat, Israel
 Posts
 21
 Thanks
 0
 Thanked 0 Times in 0 Posts
Bypassing Scenarios (Ex 2002)
Hi,
I have a complicated spreadsheet that after cutting down, does the following:
A1 5
B1 6
C1 A1*B1
D1 8
E1 C1/D1
I

20031215, 16:43 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Bypassing Scenarios (Ex 2002)
Not sure exactly what you are after.
Where are the 20 values of "A1" going to be?
Are you just putting the 20 values in G1:g20 and want in H1:H20 the calcs? Then H1 is:
=G1*$B$1/$D$1
and you can copy this to H2:H20.
If you want the sum of all the 20 to go in H1 then H1:
=sum(G1:G20)*$B$1/$D$1
Am I missing something?
Steve

20031215, 17:17 #3
 Join Date
 Jul 2002
 Location
 Efrat, Israel
 Posts
 21
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Bypassing Scenarios (Ex 2002)
Hi
Sorry, I

20031215, 17:26 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Bypassing Scenarios (Ex 2002)
I still don't understand what you are looking for.
How do you want to see 20 answers in E1?
Are you looking for the sum with the 20 possibilities of A1?
If so, then the sum is (as I mentioned):
The ("sum of the A1s") *B1/D1 [You didn't answer where you want to put the "A1"s]
Where do you want the "nearby Table of possibilities"? I was thinking you wanted:
The 20 "A1"s in G1:g20 and then the 20 "E1"s in H1:H20.
If this is not what you are after you will have to explain better.
Steve

20031215, 17:52 #5
 Join Date
 Jul 2002
 Location
 Efrat, Israel
 Posts
 21
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Bypassing Scenarios (Ex 2002)
Hi,
Attached is a file that I hope explains my question.
Mordy

20031215, 18:32 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Bypassing Scenarios (Ex 2002)
Your spreadhseet is "more or less" what I understood it to be. In F5 enter the formula:
<pre>=E5*$A$3/$A$5</pre>
and copy it down to F24.
I understand that your equations are more complex, but it is the same principle. Have the formula as a function of "A1" and enter it down the column.
If the formula is too complex and you literally want to enter the values in E5:E24 one by one into A1 and put the results in F5:F24 you could do this with a macro:
<pre>Option Explicit
Sub CreateTable()
Dim rngSource As Range
Dim rngReplace As Range
Dim rngResult As Range
Dim rCell As Range
Dim vOriReplace
Set rngSource = Range("E5:E24")
Set rngReplace = Range("A2")
Set rngResult = Range("A6")
vOriReplace = rngReplace.Value
For Each rCell In rngSource
rngReplace.Value = rCell.Value
ActiveSheet.Calculate
rCell.Offset(0, 1).Value = rngResult.Value
Next
rngReplace.Value = vOriReplace
ActiveSheet.Calculate
Set rngSource = Nothing
Set rngReplace = Nothing
Set rngResult = Nothing
End Sub</pre>
If neither result is what you are after, I guess I am still confused.
Steve

20031215, 18:55 #7
 Join Date
 Jul 2002
 Location
 Efrat, Israel
 Posts
 21
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Bypassing Scenarios (Ex 2002)
Hi,
Thank you very much. Tommorow (its 10pm here) i'll redo the macro in the "real world".
Mordy