How to get a kind of Goal Seek in Numbers?

There is no such feature in Numbers.

Yvan KOENIG (VALLAURIS, France) lundi 23 janvier 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

Implement the goal-seeking algorithm into an Applescript that works in conjunction with your table.

or

Design a table that implements the algorithm. This might be a very very slow table due to the number of calculations and will be limited in the number of iterations due to the limitations on max number of columns or rows.

Here is an example.

The cell B3 contain the parameter which is supposed to evolve

The cell C5 contains the formula :

=PMT(B3/12,B2,B1)

Select the range starting with the cell storing the variable parameter and the cell containing the formula calculating the value to reach.

Here it's B3 :: C5

-{code}

--[SCRIPT goal-seeker]

(*

Enregistrer le script en tant que Script : goal-seeker.scpt

déplacer le fichier ainsi créé dans le dossier

<VolumeDeDémarrage>:Utilisateurs:<votreCompte>:Bibliothèque:Scripts:Applications :Numbers:

Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

Sélectionner la cellule 'variable' et la cellule 'résultat'

Aller au menu Scripts , choisir Numbers puis choisir “goal-seeker”

Le script demande de définir 'but' : valeur à atteindre.

Il fait ensuite varier le contenu de la cellule 'variable' jusqu'à ce que la valeur de la cellule 'résultat' atteigne le but.

--=====

L’aide du Finder explique:

L’Utilitaire AppleScript permet d’activer le Menu des scripts :

Ouvrez l’Utilitaire AppleScript situé dans le dossier Applications/AppleScript.

Cochez la case “Afficher le menu des scripts dans la barre de menus”.

Sous 10.6.x,

aller dans le panneau “Général” du dialogue Préférences de l’Éditeur Applescript

puis cocher la case “Afficher le menu des scripts dans la barre des menus”.

--=====

Save the script as a Script: goal-seeker.scpt

Move the newly created file into the folder:

<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:

Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

Select the cell 'variable' and the cell 'result'

Go to the Scripts Menu, choose Numbers, then choose “goal-seeker”

It ask you to enter the goal value.

Then it increment the content of 'variable' until the contents of 'variable' reach the goal value.

--=====

The Finder’s Help explains:

To make the Script menu appear:

Open the AppleScript utility located in Applications/AppleScript.

Select the “Show Script Menu in menu bar” checkbox.

Under 10.6.x,

go to the General panel of AppleScript Editor’s Preferences dialog box

and check the “Show Script menu in menu bar” option.

--=====

Yvan KOENIG (VALLAURIS, France)

2012/01/24

*)

--=====

on run

local prompt, goal, dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2

if my parle_anglais() then

set prompt to "Enter the numerical value to reach"

else

set prompt to "Saisir la valeur à atteindre"

end if

repeat

display dialogpromptdefault answer "-900"

try

set goal to (text returned of result) * 1

exit repeat

on error

beep 1

end try

end repeat

set {dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

repeat 100 times

value of cellrowNum1 of columncolNum1

set value of cell rowNum1 of column colNum1 to (result + 1.0E-4) as text

value of cellrowNum2 of columncolNum2

if (round result rounding to nearest) = goal then exit repeat

end repeat

end tell

end run

--=====

(*

set { dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

*)

on get_SelParams()

local d_Name, s_Name, t_Name, row_Num1, col_Num1, row_Num2, col_Num2

tell application "Numbers" to tell document 1

set d_Name to its name

set s_Name to ""

repeat with i from 1 to the count of sheets

tell sheet i to set maybe to the count of (tables whose selection range is not missing value)

if maybe is not 0 then

set s_Name to name of sheet i

exit repeat

end if -- maybe is not 0

end repeat

if s_Name is "" then

if my parleAnglais() then

error "No sheet has a selected table embedding at least one selected cell !"

else

error "Aucune feuille ne contient une table ayant au moins une cellule sélectionnée !"

end if

end if

tell sheet s_Name to tell (first table where selection range is not missing value)

tell selection range

set {top_left, bottom_right} to {name of first cell, name of last cell}

end tell

set t_Name to its name

tell cell top_left to set {row_Num1, col_Num1} to {address of its row, address of its column}

if top_left is bottom_right then

set {row_Num2, col_Num2} to {row_Num1, col_Num1}

else

tell cell bottom_right to set {row_Num2, col_Num2} to {address of its row, address of its column}

end if

end tell -- sheet…

return {d_Name, s_Name, t_Name, row_Num1, col_Num1, row_Num2, col_Num2}

end tell -- Numbers

end get_SelParams

--=====

on parle_anglais()

return (do shell script "defaults read 'Apple Global Domain' AppleLocale") does not start with "fr_"

end parle_anglais

--=====

--[/SCRIPT]

--{code}

Yvan KOENIG (VALLAURIS, France) mardi 24 janvier 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

This can also be resolved using a separate table that works through an iteration process through many rows. I wrote something like that when Numbers first came out, and it worked fairly well.

Jason