1 / 17

Dynamic Programming From An Excel Perspective

Dynamic Programming From An Excel Perspective. Dynamic Programming From An Excel Perspective. Dynamic Programming From An Excel Perspective Ranette Halverson, Richard Simpson Catherine Stringfellow Department of Computer Science Midwestern State University.

annick
Download Presentation

Dynamic Programming From An Excel Perspective

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Dynamic ProgrammingFrom An Excel Perspective

  2. Dynamic Programming From An Excel Perspective Dynamic ProgrammingFrom An Excel Perspective Ranette Halverson, Richard Simpson Catherine Stringfellow Department of Computer Science Midwestern State University

  3. Dynamic Programming From An Excel Perspective Dynamic Programming • A popular method for solving problems by breaking them down into overlapping sub-problems that display optimal substructure • Can be thought of as a top-down approach utilizing a bottom-up evaluation • Normally used to solve optimization problems

  4. Dynamic Programming From An Excel Perspective Excel • Generally taught in the freshman application classes • Seldom taught to computer science majors • In reality CS majors need to be able to use spreadsheets • So what do we do?

  5. Dynamic Programming From An Excel Perspective Solution • Do not need to teach the spreadsheet AT ALL • Include spreadsheet usage in a few of their projects and/or homework • Spreadsheet usage includes • Graphing data collected via empirical analysis of two algorithms. • Rapidly construct mathematical tables for applications • Simulating wave-front parallel algorithms • Evaluating dynamic programming tables (the point of this talk)

  6. Dynamic Programming From An Excel Perspective A Very Simple Example (used in Computer Science for Science Majors) The memo-ization of the recursive Fibonacci function. Remember the complexity of the following? int Fib( int n) { if (n<3) return 1 else return ( Fib(n-1)+Fib(n-2) ); }

  7. Dynamic Programming From An Excel Perspective Two well-known O(n) solutions intFibMemo(intn,int * A){ if (A[n]!=0) return A[n]; else { A[n]= FibMemo(n-1,A) + FibMemo(n-2,A); return A[n]; } }; int Fib(int n) { int * A = new int[n+1] ; for (inti=1;i<n+1;i++){ A[i]=0;} A[1]=A[2]=1; return FibMemo(n,A); } // A recursive Memoized version int Fib( int n) { A=new int[n+1]; A[1]=A[2]=1; for(inti=3 ; i<=n ; i++) A[i] = A[i-1] + A[i-2]; return A[n]; } // Pure Bottom up calculation using // an array. The non array version is // not relative to our discussion.

  8. Dynamic Programming From An Excel Perspective Excel’s simple approach Kand copy cell to the right =A1+B1

  9. Dynamic Programming From An Excel Perspective Pascal's triangle is constructed in Excel in the bottom-up approach. The programmed solution can be handled via DP as in the Fibonacci example, either using an array with or without memoized recursion. The pure recursive version is computationally unacceptable. formula =B1+A2 is copied from B2 to the remaining cells.

  10. Dynamic Programming From An Excel Perspective There are many DP algorithms that appear throughout our curriculum. • Longest Common Subsequence Bioinformatics class. • Sequence Alignment: Bioinformatics • Optimal Binary Search Tree: Algorithm Analysis • Matrix Chain Multiplication: Algorithms Analysis • Graph Algorithms

  11. Dynamic Programming From An Excel Perspective Longest Common Subsequence (LCS) Definition: find the longest subsequence that is common to two (or more) sequences. Example Seq1 = B D C A B A Seq2 = A B C B D A B LCS = BCBA Note: The LCS is not a substring!

  12. Dynamic Programming From An Excel Perspective Longest Common Subsequence (LCS) DP leads to the following recursive approach. Let z=z1 z2 … xk be the LCS of x1 x2 … xi-1xi y1 y2 … yj-1yj Where c[ib ,j] is the length of the LCS of x1..xi and y1..yj

  13. Dynamic Programming From An Excel Perspective The initialized LCS Table Copy the following cell formula to all grey cells. These represent the c(i,j)’s IF(D$2=$B4,C3+1,MAX(D3,C4)) Cell formula

  14. Dynamic Programming From An Excel Perspective And the solution is Note diagonal increments Length of LCS

  15. Dynamic Programming From An Excel Perspective DP Problems with complex table manipulation • Optimal Binary Search Tree (in paper) • Matrix Chain Multiplication Question: What do you do with problems that require the processing of rows or columns in such a way that the usual cell function approach is not adequate? Excel does not allow cell function side effects! Hmm. Write the code in the include macro language (VB?)

  16. Dynamic Programming From An Excel Perspective Summarizing • CS students can benefit from work with Excel • Excel can support many projects in the CS curriculum. • Table processing available in Excel supports some algorithm visualization quite well. • This approach works particularly well with the simpler DP problems.

  17. THE END

More Related