Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro does not run when data refreshed | Excel Discussion (Misc queries) | |||
Record Macro Relative does not work? | Excel Discussion (Misc queries) | |||
Record Macro Relative does not work? | Excel Discussion (Misc queries) | |||
Executing macro for all worksheet from a different worksheet | New Users to Excel | |||
Macro and If Statement | Excel Discussion (Misc queries) |