An Easter Challange
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: An Easter Challange

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default


    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


  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin:
    T H A N K Y O U !

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com