Blog Roll

Posted in EXCEL BOOT CAMP

Manipulating Arrays and MMULT in Excel

This installment of the Excel Boot Camp teaches the basics of matrix multiplication, and how to utilize these mathematical operations in Excel through the SUMPRODUCT(), TRANSPOSE(), and MMULT() formulas.

This video teaches

  1. Arrays of numbers
  2. Array manipulation
  3. Array multiplication
  4. Matrix multiplication

vlcsnap-2018-08-02-10h06m35s476

1) Arrays of numbers

Any row or column or block of numbers is an array

2) Array manipulation

Transpose([■(a&b&c)]) = [■(a@b@c)]

Transpose([■(a@b@c)]) =[■(a&b&c)]

Transpose([■(a&d@b&e@c&f)]) =[■(a&b&c@d&e&f)]

(rotation on the diagonal)

3) Array multiplication

[■(a&b&c)]×[■(1@2@3)]=1a+2b+3c

In Excel =SUMPRODUCT([a b c],[1 2 3])

Row × column = single number.

Let’s practice with molar masses and combustion enthalpies

4) Matrix multiplication

Expanded form of SUMPRODUCT()

[■(a&b&c@d&e&f)]×[■(1&4@2&5@3&6)]

=[■(1a+2b+3c&4a+5b+6c@1d+2e+3f&4d+5e+6f)]

Matrix Multiplication Rule

M1 × M2 = M3

The columns of M1 must equal rows of M2.

The result matrix M3 will have the number of rows of M1 and the number of columns of M2.

[row1 by column1]× [row2 by column2]

M3 = [row1 by column2]

[2 by 3]×[3 by 2] = [2 by 2]

=MMULT(M1,M2)

You need to understand the above to do matrix multiplication (by hand AND in Excel).

See the video for examples of computing BOTH molar mass and combustion enthalpy combined in one shot.

Also enjoy the integration of wave functions to show orthonormality.

225 Integrals with “Ctrl+Shift+Enter”!

THAT is why we like array formulas.

Happy Excelling!

-DW

Advertisements
Posted in EXCEL BOOT CAMP, Science Education

Charting Pro Tips in Excel

This installment of the Excel Boot Camp covers three topics.

  1. Cell block navigation – No, this is not related to Huntsville, Texas. 😉
  2. Efficient ways to select data.
  3. Creating XY Scatter charts

Cell Block Navigation

For small blocks of data – tables that fit on one screen – the mouse is fine. But for LARGE sets of data like an FTIR spectrum that has over 2000 data points, navigating around the data set with the mouse is stupid. (Yes, I said it.)

You can navigate within a block of cells containing data in the following manner:

  • Arrow keys move the cell selection one row or column at a time.
  • “Ctrl + arrow” moves the cell selection to the far right, far left, top, or bottom of the block of cells according to the arrow that was used.
  • “Ctrl + Home” moves the cell selection to cell “A1” – the topmost corner of the sheet. This is very handy.
  • Ctrl + PgUp and PgDn change the sheets. (That’s ripe for a meme.)
  • “Ctrl + End” moves the cell selection to the bottom-right corner of the data on a sheet.

Selecting Data

Adding the Shift key to the above sequences allows you to select large chunks of data. This is necessary for EFFICIENTLY charting what you want to chart.

Say I had a data set with an x-values column and 15 y-values columns. And  furthermore, I only want to plot the first three y-columns against the same x column.

  1. I left-click the x-value column header.
  2. I press and hold Shift.
  3. I tap the right arrow 3 times.
  4. I add the Ctrl key so that Ctrl and Shift are BOTH pressed.
  5. I tap the down arrow, and voila! A block of the x-values and three columns of y-values are selected and ready to chart.

Charting Pro Tips

Since this is so visual, I’m going to punt and say “Watch the video…

TN-Charting2

I welcome your comments, likes and subscribes. You know the drill.

Happy pchemming!

DW

Posted in EXCEL BOOT CAMP, Science Education

Anchoring Cells in Excel

The first Excel Boot Camp topic is on anchoring cells. Newcomers to Excel must master this skill early on to be successful.

To enter formulas into Excel, simply click in a cell with the mouse, type “=” and enter the formula. Notice that you can use references to other cells in your formulas, and THAT is why spreadsheets are so useful.

Spreadsheets are NOT just tables of numerical values. They are complex interrelated formulas that change output when the input changes.

Spreadsheets are also incredibly visual which is why I favor them when teaching the applied mathematics related to physical chemistry.

So back to the main topic.

The dollar sign symbol “$” is the anchor. If you create a cell formula in cell B5 for a linear equation such as “y = mx + b” using cell references, it might look like this. “=B1*A5+B2”

B1 is the slope value at the top of the page, B2 is the intercept value at the top of the page, and A5 contains the x values for the equation. If you copy this formula down for several x values, the formula in B6 will have a slope of B2 and an intercept of B3. This is bad! The “constants” have been pulled down by when the formula is copied down the column.

The relative cell references act like boats that can be pulled along wherever the formula is pasted. But if the formula contains a constant cell reference you need to anchor that boat on a specific cell.

Use the dollar sign symbol to anchor the cell references so that the formula in B5 is “=$B$1*A5+$B$2”. Now, the formula will use the slope in B1 and the intercept in B2 no matter where it is pasted.

And that’s how you anchor cells.

Pro-Tip

If you are typing your formulas into Excel, you are prone to make mistakes! Use the mouse to select the cells for your formulas. When you click on a cell that should be anchored, hit the function key “F4” at the top of the keyboard. This automatically adds the dollar signs to the cell reference. Sometimes you only want to anchor the column, or the row, so hit “F4” multiple times, and the anchoring will cycle through all the options.

We learn by doing, so watch the video and try it yourself!

clip-anchoring

Posted in D L Williams, Education, EXCEL BOOT CAMP, Science Education

How to EXCEL in PCHEM?

I have begun a new tutorial series on Excel I’m calling my EXCEL Boot Camp.

Why host an EXCEL Boot Camp for PCHEM?

I’m glad you asked

snip-opening

Many of my students have never used EXCEL. Years ago, I added videos to the lab materials showing them how to complete the many data analysis tasks in Excel, but these were very cook-book in nature. The students were successful in following the recipe, but if they made any small mistakes along the way the process dragged on, consuming the whole FOUR-HOUR lab period (or longer).

In this situation, when it came time for the student to answer some simple and basic questions about the SCIENCE behind the Excel data analysis, they were unable to do so.

Therefore, I am adding some Excel tutorials that are skills-based. This will train the students in the useful spreadsheet skills so that they can bring them to the data analysis exercises and fully participate in the SCIENCE.

The Lessons will be added to this blog as they become available on YouTube. You can subscribe here and on YouTube to be notified when new content is posted.

Happy pchemming!

DW

Posted in D L Williams, Philosophy

DW’s Favorite Quotes (and Why)

A work in progress

I continue to read and be impacted by the thoughts of others.

He who loves to read, and knows how to reflect, has laid by a perpetual feast for his old age. (Uncle Esek, “Scribner’s Monthly”, September 1880)

No idea who Uncle Esek is, but I have been infinitely blessed by the ability to read and reflect. I was extremely proud of my children when they read their first chapter books. I announced that from that day onward they would never be bored!

The underlying physical laws necessary for the mathematical theory of a large part of physics and the whole of chemistry are thus completely known, and the difficulty is only that the exact application of these laws leads to equations much too complicated to be soluble. It therefore becomes desirable that approximate practical methods of applying quantum mechanics should be developed, which can lead to an explanation of the main features of complex atomic systems without too much computation. (http://rspa.royalsocietypublishing.org/content/royprsa/123/792/714.full.pdf , Quantum mechanics of many-electron systems, P. A. M. Dirac, Proc. R. Soc. Lond. A 

This is the charge that I have taken up in preparing a manuscript that teaches Spectroscopy in One Dimension. The book proposal is 90% completed, and I aim to send it to the publisher in late August.

Study without desire spoils the memory, and it retains nothing that it takes in [sic]. (Leonardo DaVinci)

This was an epiphany for me. I realized that a huge percentage (>90%?) of my students could not learn chemistry because they did not desire the CHEMICAL KNOWLEDGE. They desired a grade, a checked box on their transcript, a degree, or a future job, but NONE of those things will help them with point groups, wave functions, phase diagrams, etc. To learn CHEMISTRY they have to desire CHEMISTRY. A large part of my job as a professor is awakening the desire for CHEMISTRY, thus opening their ability to RETAIN what they STUDY.

“Of all tyrannies, a tyranny sincerely exercised for the good of its victims may be the most oppressive.” (C.S. Lewis, God in the Dock, 1948)

So much could be written about this quote, but it will get political very quickly. I’ll pare it down to one question. Are policies being made because they actually (with evidence and data) produce good outcomes, or are policies being made because they make the policy makers FEEL good?

I believe in Christianity as I believe that the sun has risen. Not only because I see it, but because by it I see everything else.” (C.S. Lewis, Is Theology Poetry? 1945)

Christianity is a World View as is Naturalism, Deism, Confucianism, etc. World Views are lenses through which one sees EVERYTHING. What is your world view? Why do you have it? How did you get it? Do you have reasons for keeping it? And what would it take for you to change it?

I have told you these things so that in Me you may have peace. You will have suffering in this world. Be courageous! I have conquered the world.” (Jesus of Nazareth, John 16:33)

I love this quote even though it promises suffering. The best quotes tell you something true. They are like an accurate compass. They point North ALL THE TIME, and as long as you have North, you can get un-lost.

Posted in Contact Angle, Critical Solution Temperature, D L Williams, DSC, Education, Forensics, FTIR, Hansen Solubility Parameters, LIF, Physical Chemistry, Raman, RER, Science Education, Solubility, Solvent Blending, Spectroscopy, Thermal Analysis, UV-VIS-NIR, XPS

PCHEM and Forensic Chem Lecture Videos

I frequently have seniors who want to revisit the concepts in pchem sit in my 8AM lectures the year after they have had my course. It’s a privilege to have them and an encouragement to see their natural curiosity in action. They seek to firm up their understanding of the quantum world and how we interact with it (i.e. spectroscopy).

In the fall of 2017, I put these students to work videoing the lectures and posting them on the Physical Chemistry at Sam Channel. These videos are essentially raw footage of lecture. The videos could have been greatly improved by adding in the PowerPoint Slides, captioning, cleaning up the audio, and cutting out my “ums” and “uhs”. But these volunteers did not have time to do that, nor did I. I had a CLEANING WORKSHOP to plan and execute!

CHEM 4448 – Physical Chemistry 1
– Quantum Mechanics and Spectroscopy

CHEM4448-Playlist-Snip

CHEM 4449 – Physical Chemistry 2 -Thermodynamics

4449Lecture-Playlist-Snip

CHEM 4380 – Forensic Chemistry

The students appreciated the fall lecture videos so much, there was a great amount of interest in capturing the Forensic Chemistry Lectures. So we created a Forensic Chemistry at Sam Channel, too.

CHEM4380-Playlist-Snip

The lecture playlist is only one piece. Jessy also created other playlists of videos on the Forensic Chemistry at Sam Channel that should interest Forensic Science and Forensic Chemistry students and enthusiasts. She performed these tasks as an SHSU Honors Contract for the course – an activity that supplements the material for the student and enhances the skills that they take away from the course.

Thanks to the Student Team!

Even raw footage must be stitched together, uploaded, described, tagged, and set up on YouTube. This takes TIME and time is a valuable commodity for our chemistry majors.

I thank William Fernandez for videoing CHEM 4448 and CHEM 4449. His videos were so well-received by the students that Jerome Butler decided to sit in and video my Forensic Chemistry course CHEM 4380. Thanks Jerome!

I thank Matthew Peavy for producing the videos for CHEM 4448 and CHEM 4449, and for uploading them. I thank Jessy Stone for producing and uploading the CHEM 4380 videos for Forensic Chemistry.

You students who are willing to go beyond the minimum give us hope for the future.

You people in industry and in graduate programs, hire these students! You won’t be sorry!

-DW