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

Author:

Dr. Darren L. Williams (@pchem4all) is an Associate Professor of Physical Chemistry at Sam Houston State University. When he is not blending solvents, he is tinkering with contact angle measurements, FTIR microspectroscopy, etc. http://www.shsu.edu/chm_dlw/ . He welcomes your comments and questions by phone (936-294-1529) and by email (williams “at” shsu.edu).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s