SG,
Thanks for the reply. The calculation chain is quite complex, with a couple calculations splitting the input into different paths based on defined market ratios which are in turn creating "choke" points; this is what is not allowing me to follow your guidance above.
My initial reaction was to seek a Numbers feature to plot this relationship by my identifying cell locations and stating a range, creating a program supported function; I am unaware how to do this if the functionality does exist however.
So this brought me to using AppleScript, an applicable solution. Here's the insight that I can provide thus far for the script:
- Create a 2D array from existing "Data" table
- Insert first item from array into cell "C2" of table “Calculations”
- Copy resulting sales calculation to a defined data store ("calculatedSales")
- Copy resulting semi-fixed costs calculation to a defined data store ("calculatedSemiFixedCosts")
- Copy resulting variable costs calculation to a defined data store ("calculatedVariableCosts")
- Repeat for the remaining items in the 2D array
- Write 'calculatedSales' to B column (+1 due to header) of "Data" table
- Write 'calculatedSemiFixedCosts' to C column of "Data" table
- Write 'calculatedVariableCosts' to D column of "Data" table
Here's a visualization of the above:
With this completed, a data set could be created on demand and plotted by Numbers.
Here's what I have thus far:
tell application "Numbers"
activate
-- for this script, grab data set from "Data" table (1 Column with 25 rows)
set scriptData to my scriptDataSets(1, 25)
-- for this script, create a store for calculated values
set calculatedSales to my scriptDataSets(1, 25)
set calculatedSemiFixedCosts to my scriptDataSets(1, 25)
set calculatedVariableCosts to my scriptDataSets(1, 25)
end tell
tell application "Numbers"
activate
-- for this script, grab data set from "Data" table (1 Column with 25 rows)
set scriptData to my scriptDataSets(1, 25)
-- for this script, create a store for calculated values
set calculatedSales to my scriptDataSets(1, 25)
set calculatedSemiFixedCosts to my scriptDataSets(1, 25)
set calculatedVariableCosts to my scriptDataSets(1, 25)
-- todo: identify the correct table here
set the columnCellCount to count of cells of column 2
repeat with i from 1 to count of the scriptData
-- get a data set from the data set list
set salesColumnData to itemi of the calculatedSales
tell column (columnIndex + i)
-- iterate the data set, populating row cells from to to bottom
repeat with q from 1 to the count of salesColumnData
tell cell (columnIndex + q)
set value to item q of salesColumnData
end tell
end repeat
set semiFixedColumnData to itemi of the calculatedSemiFixedCosts
tell column (columnIndex + i)
-- iterate the data set, populating row cells from to to bottom
repeat with q from 1 to the count of semiFixedColumnData
tell cell (columnIndex + q)
set value to itemq of semiFixedColumnData
end tell
end repeat
set variableColumnData to itemi of the calculatedVariableCosts
tell column (columnIndex + i)
-- iterate the data set, populating row cells from to to bottom
repeat with q from 1 to the count of variableColumnData
tell cell (columnIndex + q)
set value to item q of variableColumnData
end tell
end repeat
end tell
end tell
end tell
end repeat
end tell
My apologies for the formatting above. My largest issues relate to identifying the correct locations of tables to be read from and write to, then writing the values correctly. This process could have been done through Automator or via keystrokes, however SGIII's post on a stack overflow ticket inspired a fundamental approach to this. Any help would be welcomed and thanks from everyone who forgot to say thanks.
Jordan
