Frequency

Frequency - This function counts the number of values in the Data array that are within the values given by the Classes array.


Syntax:

FREQUENCY(Data; Classes)
Data - represents the reference to the values to be counted.
Classes - represents the array of the limit values.


Screen shot. You can click the picture to enlarge.



Cells B14:B20 contain the formula {=FREQUENCY(A1:J10;A14:A20)}

Notice the {} around the formula. This indicates an array formula. To enter an array formula you must press the key combination Ctrl+Shift+Enter. However, I would recommend using the function wizard.
Cells A1:J10 contain the data values. I randomly generated numbers ranging form 50 to 120. Cells A14:A20 contain the classes. I broke the classes into intervals of length 10. You can click on the image to see a more detailed description of the classes.

SUMPRODUCT()

SUMPRODUCT() - Multiplies corresponding elements in the given arrays, and returns the sum of those products.

Syntax

SUMPRODUCT(Array 1; Array 2...Array 30)

Array 1, Array 2...Array 30 represent arrays whose corresponding elements are to be multiplied.

At least one array must be part of the argument list. If only one array is given, all array elements are summed.

I really like this SUMPRODUCT() function. Below is an example spreadsheet. The spreadsheet contains formulas in the Turquoise, Yellow, and Green cells. The formulas for each color are similar. So, I will explain one formula from each color group.

Turquoise Colored Cells
In cell D14 I want to find the number of Ford's sold during the month of June.
Cell D14 contains the formula =SUMPRODUCT((A4:A12="Ford")*(B4:B12="June")*(C4:C12))

The first part of the formula looks through cells A4:A12 and returns a value of 1 (True) or (0) False. The second part of the formula looks through cells B4:B12 and returns a value of 1 (True) or 0 (False). It then multiplies the these results and the values from column C and then sums the products. See the screen shot below for a better representation of how the formula is calculating the results.



Yellow Colored Cells
In cell D18 I want to know how much money I made during the month of June on Ford's
Cell D18 contains =SUMPRODUCT((A4:A12="Ford")*(B4:B12="June")*(C4:C12)*(D4:D12))

The first part of the formula looks through cells A4:A12 and returns a value of 1 (True) or (0) False. The second part of the formula looks through cells B4:B12 and returns a value of 1 (True) or 0 (False). It then multiplies the these results and the values from columns C and D and then sums the products. The screen shot for how this is calculated would be similar to the above table.

Green Colored Cells
In cell D22 I want to know how many cars I sold in June.
Cell D22 contains the formula =SUMPRODUCT((B4:B12="June")*(C4:C12))

The first part of the formula looks through cells B4:B12 and returns a value of 1 (True) or 0 (False). It then multiplies the these results and the values from column C and then sums the products. The screen shot for how this is calculated would be similar to the above table.


Here is a screen shot of my spreadsheet. You can click on "Spreadsheet Function Files" under "Links" on the right hand side of the page.

MINVERSE and MMULT

Looking for an easy way to solve a system of equations? Calc can can do this for you.

Functions used:

MINVERSE(Array)
MMULT(Array,Array)

Let's look at each function individually:

MINVERSE(Array) - This function calculates the inverse of a square array. Square array means the array has the same number of rows as it does columns.

MMULT(Array,Array) = This function calculates the product of two arrays. The number of columns in the first array must be the same as the number of rows in the second array.

Take a look at the screen shots below. I have posted this file. It is called system of equations. You download by clicking on "Spreadsheet Function Files" under "Links" on the right hand side of the page.

This screen shot is for cells A13:C15



This screen shot is for cells B18:B20



And here is a screen shot of my results. The answers are in cells B18:B20