Fun With Functions 1

Today we will do some fun things. Of course you may think what I think is fun and what you think is fun might be quite different. Well, .. I guess you cannot always be correct. We will look at a lot of functions today and use Excel to generate data that has interesting features.

Fibonacci Sequence

Lets start with the Fibonacci Sequence and see if we can get Excel to generate part of it. Recall that the Fibonacci sequence is the following.

1 1 2 3 5 8 13 21 34 . . . (where each number is the sum of the two previous ones)

Your problem is to place a 1 in A1 and a 1 in B1 , a formula in C1 and expand it to the right generating the sequence. Cool Huh!

Pascal’s Triangle

You might recall Pascal’s triangle is easily constructed. See here for a nice discussion. Here is part of the triangle

Note that each number is the sum of the two numbers directly above it. How can we create this table in Excel. Well lets rotate it slightly and place it in excel

Here is the above chart in Excel rotated slightly. You job is to create this chart easily. Start with a collection of ones in the indicated slots and then create a formula in B2 that will be copied everywhere else.

This triangle is very famous as well as the fibonacci sequence above. The applications in Biology of the Fibonacci sequence are well known and you should take time to fill yourself in on these cool relationships.  Here is a short discussion.

The next section we will create a Magic squares. In a 4 by 4 magic square we insert the numbers from 1 to 16 in such a way that every row, column and diagonal adds to the magic sum, in this case 34. If we are constructing a n by n magic square the magic sum turns out to be n(n^2+1)/2 so a 4 by 4 would have the sum=4(4^2+1)/2=2*17=34. How did I come up with this formula? Hmmm. See Farrar’s site form more information on Quadramagicology as well as wikipedia.

Create a Magic square in excel that looks something like the following. I typed in the numbers 1 thru 16 and then placed functions around the square using the Excel. You can now swap numbers in the square to see if you can eventually get a magic square. You might try picking a cell that has a high row and column sum and swap it with a cell that has a low column and row sum. See what you can do

 

 #######################################################################################################################################################

Here are some interesting questions that you should be able to solve using the help support of Excel or the web.

#######################################################################################################################################################

1 How do you use the AutoSum feature of Excel?

2. If you have some ones birth date determine the persons age in Years, Months and Days. (see here for an secret older undocumented method). How would you do it otherwise?

2. Count the number of items in a column, row or array of values that contain data. Click help for COUNTA while in excel. What is the difference between COUNTA and COUNT?

3. Assuming the the selected cells contain strings count the number of items in the area that have the following properties: three characters long, string ends in “es”. Click on help and search for COUNTIF. Pay particular attention to the examples under COUNTIF help.

4. Given a column of grades create an adjacent column that contains The words PASS or FAIL. (Check out IF)

5. Create a table that has all multiplications possible multiplications from 1 to 10. Use row height=20 and column height=4.

6. Change the above table so it displays the values X^2-Y^2. Where X is the top row and Y is the left column. When you have this working select the entire chart including the X and Y columns and then Click the insert tab followed by Other Charts and then pick the left surface image and see what you get.

Comments are closed.