# Imitating excel's goal seek

E

#### endy_tj

Hi,

In MS Excel there is a feature called Goal Seek. If you have a function
and you need certain output from the function, you can ask goal seek to
find the input for that function. The search for input value is done
brute force style.

I tried to imitate this feature in ruby when I wanted to calculate tax
subsidy (tax subsidy is also taxed, tax calc utilizes tax brackets, non
reversible, etc.) Here's my function:

def subsidy_seek(net_salary, &net_if_subsidy)
subsidy = 0
net_calc = net_if_subsidy.call(subsidy)
increment = 65536

until net_salary - net_calc < 10
until net_if_subsidy.call(subsidy + increment) < net_salary
increment /= 2
end

subsidy += increment
net_calc = net_if_subsidy.call(subsidy)
end

subsidy
end

It works, but it's a very specific function.

Is there a ruby library for this kind of feature?

Is there a mathematical term for goal seek? So if I want to learn more
I can key in that term in google?

Anybody know what kind of process goes behind goal seek? It seems to be
able to handle most situations.

R

#### Robert Love

In said:
Is there a mathematical term for goal seek? So if I want to learn more
I can key in that term in google?

Anybody know what kind of process goes behind goal seek? It seems to
be able to handle most situations.

The general term is optimization though yours looks more like searching.
One book to look at is "Numerical Recipes". It comes in C and Fortran
flavors. There are plenty of books on numerical methods that teach how
to perform what you want and give examples.

This is one area where Python is ahead of Ruby. Python has many
bindings to top notch numerical libraries. I hope that some work is
being done in this area for Ruby.

L

#### listrecv

In general, I would say that Python is much better suited for
mathematical and scientific programming than Ruby ever can be. Math
and science are founded in _precision_ and _explicitness_ - you need to
know *exactly* what is going on. Python forces this, whereas Ruby, with
all its beautiful metaprogramming open classes magic, makes it quite
tricky.

(Incidentally, I've read that one reason scientists don't use Excel in
the first place is that the source for each cell is not readily visible
without user action.)

G

#### Graham

Incidentally, I've read that one reason scientists don't use Excel in
the first place is that the source for each cell is not readily visible
without user action
Which scientists are these? I've spent a career supporting basic
research scientists who use Excel for everything-
- Excel as a database
- Excel as a reporting tool
- Excel for creating tables for reports
- Excel for statistics (Aargh!)
- Excel for graphics / charting

Some people even used it as a spreadsheet, once the = button had been
explained to them.

C

#### coachhilton

You also may want to try a simple genetic algorithm (GA) for solving
the goal problem. I seem to recall someone mentioning that a fledgling
GA implemented in Ruby has been posted to the RAA. Even though it may
not have all the bells and whistles of say Matt Wall's popular GALib
(see source forge), a basic GA can easly solve such function
optimization problems even w/Ruby's (supposed) precision issues

Ken

#### WorldsInWorld

I have a working code for Goal Seek in Python (sorry don't know Ruby). It bases on, first, the Line Search Method, and, the Bisection method (by use of only Numpy). Here you can find the details regarding the code: https://github.com/DrTol/GoalSeek_Python

## Ask a Question

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.