An Easter Challange

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
More of a challenge than for myself (in fact it's not for myself).

Can anyone think of a way to work out seemingly illogical differences in numbers.

The reason I ask and further clarification is that we all know that Celsius to Fahrenheit is ((Number*9)/5)+32 (well, if you didn't, you all do now)

If you had say a sample set of numbers C in ColumnA and F in ColumnB and you didn't know the logical reasons for the difference in them. Is possible to program Excel in such a way that it will return the most logical formula. Say return an equation in a disired cell

Anyone get that?

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-03-28 11:16
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
On 2002-03-27 06:58, Ian Mac wrote:
More of a challenge than for myself (in fact it's not for myself).

Can anyone think of a way to work out seemingly illogical differences in numbers.

The reason I ask and further clarification is that we all know that Celsius to Fahrenheit is ((Number*9)/5)+32 (well, if you didn't, you all do now)

If you had say a sample set of numbers C in ColumnA and F in ColumnB and you didn't know the logical reasons for the difference in them. Is possible to program Excel in such a way that it will return the most logical formula. Say return an equation in a disired cell

Anyone get that?

The long and short answer is no.

The mechanics of Discovery is a research area in cognitive science [which is a short hand for artificial intelligence and cognitive psychology among other disciplines]. A few systems that are built attempt to devise formulas/mathematical laws that fit emprical numeric data.

Such systems are often programmed in LISP which in turn are programmable.

Excel does not have (nor has to have) functions that can evaluate formulas dynamically constructed. The & operator along with Longre's EVAL (a UDF) are only elements we have for that purpose.

If interested, what follows might give an idea what is involved in discovering a formula by a machine/program that simulates (human) cognition:

Langley, P., Simon, H. A., Bradshaw, G. L., Zytkow, J. M. (1987). Scientific discovery: Computational explorations of the creative processes.Cambridge, MA: The MIT Press.

Aladin
 
Upvote 0
Number theory for solution sets will allow for the prediction of the next number in a known list or series. This combined with statistical analysis can also predict which of a family of choices is the best fit for the next number in a series.

If the series fits a clasic profile we can code a solution formula, based upon the form of the solution series.

If 1=2, 2=8, 3=18, 4=32 and 5=50
where F=x(sq)+x(sq)+c if c=0 or the clasic quadratic equation.

Or, any of the n+x family of sets...

Equation fitting requires a database of patterns and a uniform series of sample data points. If you have this information you can match the solution family.

Theivens and Norton's Black Box solution can reduce a complex equation to its simplest form. Given the input and output a subtitution formula can be developed.

A program could be made to calculate this, in fact this is the basis of intergrated chip design today. Logic gates are reduced to the simplest configuration possible, still outputting the correct solution, using such programs. All computer chips are designed this way. JSW
 
Upvote 0
On 2002-03-27 08:36, Aladin Akyurek wrote:
On 2002-03-27 06:58, Ian Mac wrote:
More of a challenge than for myself (in fact it's not for myself).

Can anyone think of a way to work out seemingly illogical differences in numbers.

The reason I ask and further clarification is that we all know that Celsius to Fahrenheit is ((Number*9)/5)+32 (well, if you didn't, you all do now)

If you had say a sample set of numbers C in ColumnA and F in ColumnB and you didn't know the logical reasons for the difference in them. Is possible to program Excel in such a way that it will return the most logical formula. Say return an equation in a disired cell

Anyone get that?

The long and short answer is no.


The mechanics of Discovery is a research area in cognitive science [which is a short hand for artificial intelligence and cognitive psychology among other disciplines]. A few systems that are built attempt to devise formulas/mathematical laws that fit emprical numeric data.

Such systems are often programmed in LISP which in turn are programmable.

Excel does not have (nor has to have) functions that can evaluate formulas dynamically constructed. The & operator along with Longre's EVAL (a UDF) are only elements we have for that purpose.

If interested, what follows might give an idea what is involved in discovering a formula by a machine/program that simulates (human) cognition:

Langley, P., Simon, H. A., Bradshaw, G. L., Zytkow, J. M. (1987). Scientific discovery: Computational explorations of the creative processes.Cambridge, MA: The MIT Press.

Aladin

Number theory for solution sets will allow for the prediction of the next number in a known list or series. This combined with statistical analysis can also predict which of a family of choices is the best fit for the next number in a series.

If the series fits a clasic profile we can code a solution formula, based upon the form of the solution series.

If 1=2, 2=8, 3=18, 4=32 and 5=50
where F=x(sq)+x(sq)+c if c=0 or the clasic quadratic equation.

Or, any of the n+x family of sets...

Equation fitting requires a database of patterns and a uniform series of sample data points. If you have this information you can match the solution family.

Theivens and Norton's Black Box solution can reduce a complex equation to its simplest form. Given the input and output a subtitution formula can be developed.

A program could be made to calculate this, in fact this is the basis of intergrated chip design today. Logic gates are reduced to the simplest configuration possible, still outputting the correct solution, using such programs. All computer chips are designed this way. JSW

Hi Ian, Aladin, and Joe:
Thanks Aladin and Joe for scholarly input.
How about use of Numerical Methods, .. and
how about looking at creating a formula from the Celsiuns and Farenheit data, say for lack of better words, by reverse engineering.
By looking at enough data (and with some assumptions of course), one may recognize that it will fit the straight line equation formula ...
y=mx+c ... with x as the slope and c as the offset
Could one not then come up with the best fit for value of x and c that will fit the data.
I don't know if it is relevant here ... but I thought I will mention it any way!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard coded signature
This message was edited by Yogi Anand on 2003-01-19 15:12
 
Upvote 0
Hi Ian, Aladin, and Joe:
Thanks Aladin and Joe for scholarly input.
How about use of Numerical Methods, .. and
how about looking at creating a formula from the Celsiuns and Farenheit data, say for lack of better words, by reverse engineering.
By looking at enough data (and with some assumptions of course), one may recognize that it will fit the straight line equation formula ...
y=mx+c ... with x as the slope and c as the offset
Could one not then come up with the best fit for value of x and c that will fit the data.
I don't know if it is relevant here ... but I thought I will mention it any way!


I take the gist of Ian's question to be to return a formula after examining a set of numbers by means of formulas and/or VBA code. Considering your example, we should have a system of formulas and/or WBA code that return an Excel formula, equivalent of y=mx+c.

Here a set of heuristics (citing from Langley et al.)

(1) If the values of a term are constant, then infer that the term always has that value.

(2) If the values of two numerical terms increase together, then consider their ratio.

(3) If the values of one term increase as those of another decrease, then consider their product.

BACON.1 has the above heuristic rules in the form of productions as they are called in artificial intelligence. BACON, given a set of emiprically obtained numbers relating to the distances between, say, 3 planets, attempts to discover Kepler's third law of planetary motion: that is,

D^3/P^2 = c, where D is the distance, P is the period, and c is a constant.

Aladin
 
Upvote 0
Ian,

return a formula ?

As a practical analogy : I often wonder if you can take the "525" that the Countdown thing produces and reverse engineer the formula given the sets of numbers that the contestant picks out....


(((((20)-(3))*(5))+(20))*(5))

given the numbers are static ie chosen, you then have 4 sets of 4 mathematical functions

4^4 = 256, so maybe only 256 paths to go down

or 4 sets of loops in VBA with 4 sub loops


However, with F to C, Excel would not know how many math operations are involved..... unless it knew you were converting F to C........could be tricky

interesting question though
:)
 
Upvote 0
It may be an easier coding task given a table of C temp and corresponding F temps to convert one value to the other, without knowing the formula. Rather than code a solution that delivers the formula for the conversion. JSW
 
Upvote 0
On 2002-03-27 15:35, Joe Was wrote:
It may be an easier coding task given a table of C temp and corresponding F temps to convert one value to the other, without knowing the formula. Rather than code a solution that delivers the formula for the conversion. JSW


But then you would not know how to convert a temperature unless it were in the table.
 
Upvote 0
On 2002-03-27 12:13, Aladin Akyurek wrote:

Hi Ian, Aladin, and Joe:
Thanks Aladin and Joe for scholarly input.
How about use of Numerical Methods, .. and
how about looking at creating a formula from the Celsiuns and Farenheit data, say for lack of better words, by reverse engineering.
By looking at enough data (and with some assumptions of course), one may recognize that it will fit the straight line equation formula ...
y=mx+c ... with x as the slope and c as the offset
Could one not then come up with the best fit for value of x and c that will fit the data.
I don't know if it is relevant here ... but I thought I will mention it any way!


I take the gist of Ian's question to be to return a formula after examining a set of numbers by means of formulas and/or VBA code. Considering your example, we should have a system of formulas and/or WBA code that return an Excel formula, equivalent of y=mx+c.

Here a set of heuristics (citing from Langley et al.)

(1) If the values of a term are constant, then infer that the term always has that value.

(2) If the values of two numerical terms increase together, then consider their ratio.

(3) If the values of one term increase as those of another decrease, then consider their product.

BACON.1 has the above heuristic rules in the form of productions as they are called in artificial intelligence. BACON, given a set of emiprically obtained numbers relating to the distances between, say, 3 planets, attempts to discover Kepler's third law of planetary motion: that is,

D^3/P^2 = c, where D is the distance, P is the period, and c is a constant.

Aladin

Firstly, I must say I had hoped to get this sort of reponse The easter bunny hath delivered

Secondly, I was NOT asking for the rocket science people, possibly, think I was.

My question was actually sparked from some of our staff doing puzzles in downtime, the sort that are what would the value of be given:

{3,3;4,3.5;17,10;22,12.5;29,16;36,19.5;43,23;50,"?"}

where the answer would be 26.5 because the logic behind it is simply (N+N)/2.

And I thought it might be interesting to see if there was a way to return the equation rather than the answer.

I can see how this would be very difficult, but for the these sort of puzzles may be do able, as gerenally they only go to about 2 or 3 decimal points.

Then there is the added additional type of puzzle that is, what's the next number in the sequence?

e.g. the next number in:

{3,33,153,633,2553,10233,"?"}

this also begs the question as to how does Excel work in terms of Autofilling numbers by highlighing the ones already in place and drag Along/Down.

Thoughts,




_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-03-27 16:12
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top