Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dave Wilson
 
Posts: n/a
Default PivotTable, Scenarios, Macro??

Friends,

I have a complex spreadsheet that calculates a value. I want to use that
sheet to calculate the results using a column of numbers as inputs to the
sheet and putting the results adjacent to the input value so that I can then
graph to array of input value vs. the output values. I am tired of doing
this one value at a time.

Thanks, Dave


  #2   Report Post  
Max
 
Posts: n/a
Default

Think a one variable data table will do it ..

A simple illustration?

Suppose you have an output cell C1: =A1*2-5
where C1 will change, depending on inputs into A1
(A1 is a precedent to C1)

Put a value in A1: 10 (say)

On the same sheet, put in say, F1: =C1
(point to the output cell of interest)

List some possible inputs into A1
down in E2:E7 (say): 10,20,30 .. 60

Select E1:F7

Click Data Table
Leave "Row input cell" empty
For "Column input cell", put: $A$1
(or just inside the box, then click outside on cell A1)
Click OK

You'll get the results in E2:F7

10 15
20 35
30 55
40 75
50 95
60 115

with:
Input values in E2:E7
Output values in the adjacent F2:F7
(Use the above to graph)

Try changing the input values listed in E2:E7, you'll see
the output values in F2:F7 will recalc correspondingly

Extend to suit ..

--
Note: The formula: {=TABLE(,A1)} will appear in F2:F7
Albeit it looks like an array formula, it cannot just
be entered as such. The construct must be done/invoked
via the Data Table steps outlined
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Dave Wilson" wrote in message
...
Friends,

I have a complex spreadsheet that calculates a value. I want to use that
sheet to calculate the results using a column of numbers as inputs to the
sheet and putting the results adjacent to the input value so that I can

then
graph to array of input value vs. the output values. I am tired of doing
this one value at a time.

Thanks, Dave




  #3   Report Post  
Dave Wilson
 
Posts: n/a
Default

Max,

Thanks a million, I can use this all over the place!

Dave, Lat N7 59 Lon W 79 52, Panama. Where are you?

"Max" wrote in message
...
Think a one variable data table will do it ..

A simple illustration?

Suppose you have an output cell C1: =A1*2-5
where C1 will change, depending on inputs into A1
(A1 is a precedent to C1)

Put a value in A1: 10 (say)

On the same sheet, put in say, F1: =C1
(point to the output cell of interest)

List some possible inputs into A1
down in E2:E7 (say): 10,20,30 .. 60

Select E1:F7

Click Data Table
Leave "Row input cell" empty
For "Column input cell", put: $A$1
(or just inside the box, then click outside on cell A1)
Click OK

You'll get the results in E2:F7

10 15
20 35
30 55
40 75
50 95
60 115

with:
Input values in E2:E7
Output values in the adjacent F2:F7
(Use the above to graph)

Try changing the input values listed in E2:E7, you'll see
the output values in F2:F7 will recalc correspondingly

Extend to suit ..

--
Note: The formula: {=TABLE(,A1)} will appear in F2:F7
Albeit it looks like an array formula, it cannot just
be entered as such. The construct must be done/invoked
via the Data Table steps outlined
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Dave Wilson" wrote in message
...
Friends,

I have a complex spreadsheet that calculates a value. I want to use that
sheet to calculate the results using a column of numbers as inputs to the
sheet and putting the results adjacent to the input value so that I can

then
graph to array of input value vs. the output values. I am tired of doing
this one value at a time.

Thanks, Dave






  #4   Report Post  
Max
 
Posts: n/a
Default

"Dave Wilson" wrote
Thanks a million, I can use this all over the place!


You're welcome, Dave !
Glad it helped ..

.. Where are you?


In Singapore .. just a couple of degrees
off - dead centre in this island-city-nation <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


  #5   Report Post  
Max
 
Posts: n/a
Default

For "Column input cell", put: $A$1
(or just inside the box, then click outside on cell A1)


Minor typo correction: 2nd line above should read as
(or just click inside the box, then click outside on cell A1)


--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro does not run when data refreshed Larry Lehman Excel Discussion (Misc queries) 0 January 16th 05 08:31 PM
Record Macro Relative does not work? lbbss Excel Discussion (Misc queries) 3 December 13th 04 09:43 PM
Record Macro Relative does not work? lbbss Excel Discussion (Misc queries) 1 December 13th 04 08:55 PM
Executing macro for all worksheet from a different worksheet Biti New Users to Excel 3 December 8th 04 11:05 AM
Macro and If Statement SATB Excel Discussion (Misc queries) 2 December 3rd 04 05:46 PM


All times are GMT +1. The time now is 02:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"