The RAND function calculates a random real number greater than or equal to 0 and less than 1.

The function is volatile meaning a new random real number is returned every time the worksheet is calculated.

Formula in cell B3:

=RAND()

RAND()

The RAND function has no arguments.

]]>The MULTINOMIAL function calculates the ratio of the factorial of a sum of values to the product of factorials.

Formula in cell D3:

=MULTINOMIAL(B3:B5)

The value is calculated like this:

=FACT(2+3+4)/(FACT(2)*FACT(3)*FACT(4))

See mathematical formula below in comments.

MULTINOMIAL(*number1*, *[number2]*, ...)

number1 |
Required. The number for which you want to calculate the multinomial. |

[number2] |
Optional. Up to 254 additional numbers. |

MULTINOMIAL returns

- #VALUE! if argument is nonumeric.
- #NUM! if argument is less than 0 (zero).

The MULTINOMIAL function has the following mathematical formula:

]]>The MINIFS function calculates the smallest value based on a given set of criteria.

Formula in cell E3:

=MINIFS(C3:C10,B3:B10,"A")

The formula in cell E3 checks if values in column B are equal to "A". If TRUE, corresponding values in column C are evaluated and the minimum value is then returned to cell E3.

MINIFS(*min_range*, *criteria_range1*, *criteria1*, [*criteria_range2*, *criteria2*], ...)

min_range |
Required. A cell reference pointing to the numbers. |

criteria_range1 |
Required. Cells to evaluate based on the criteria. |

criteria1 |
Required. Criteria in the form of a number, expression, or text. |

[criteria_range2] |
Optional. Up to 126 additional arguments. |

[criteria2] |
Optional. Up to 126 additional arguments. |

The MINIFS function returns #VALUE error if the cell range size of the min_range, criteria_range and criteria is **not** the same.

]]>

The MINA function returns the smallest number.

Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE to 0 (zero).

Error values cause the function to return an error.

Formula in cell B13:

=MINA(B3:B10)

MINA(*value1*, [*value2*], ...)

value1 |
Required. Value or cell ref for which you want to find the smallest number. |

[value2] |
Optional. Up to 254 additional arguments. |

MINA function returns 0 (zero) if argument has no values.

Use the MIN function if you don't want to include logical values in the calculation.

]]>The easiest way to check if a cell has a value is, in my opinion, to use the equal sign to compare the cell value with the value you are looking for.

The equal sign is a logical operator that turns the formula a logical expression and returns a boolean value, TRUE or FALSE.

Formula in cell C3:

=B3="Lime"

Use double quotation marks before and after the text value in order to do the comparison. B3 is a cell reference to a specific cell at the intersection of column B and row 3.

Copy cell C3 and paste to cells below and the cell reference changes in each cell. B3 is, therefore, a relative cell reference.

If you forget the double quotation marks Excel will evaluate your text value as a built-in function or a user-defined function. If such function can't be found Excel returns #NAME? error.

Formula in cell D8:

=B8=7

Don't use double quotation marks with numbers, if you do Excel will evaluate your number as a text value and the comparison will never return TRUE.

Example,

=B10="7"

returns FALSE even though cell B10 has number 7.

The comparison made by the equal sign is, however, not case-sensitive. Use the EXACT function to perform a case-sensitive comparison, see picture above.

A formula like this one has one of the values hardcoded into the formula:

=B3="Lime"

If you want to compare another value you must change each formula in every cell which is not at all efficient.

The picture above shows a formula that compares two cell values, however, one of the cell references are locked to cell C9.

How did I enter this formula?

- Double click on cell C3 with left mouse button.
- Type =B3=C9
- Press function key F4 to automatically change C9 to $C$9.
- Press Enter
- Copy cell C3
- Paste to cells below.

If you now examine the formula you will see that the first cell reference B3 in the formula changes in each cell.

This happens with relative cell reference whereas an absolute cell reference doesn't change.

Having cell references in your formulas allows you to quickly change a value without adjusting the cell formulas at all.

Try it yourself, change the value in cell C9 to Banana and check column C for changes. Cell C6 is now TRUE and C5 is FALSE.

The equal sign (=) is one out of 3 different logical operators, the picture above shows you the *greater than* sign (>).

You can combine these three logical operators to perform different logical expressions, see table below.

Logical operator |
Description |
Example |

< | less than | B3<$C$9 |

> | greater than | B3>$C$9 |

<= | less than or equal to | B3<=$C$9 |

>= | greater than or equal to | B3>=$C$9 |

= | equal to | B3=$C$9 |

<> | not equal to | B3<>$C$9 |

The formula in column C returns TRUE if the value in column B is larger than 4.

Now you know what a logical expression is which is an essential part of an IF function.

The MINVERSE function calculates the inverse matrix for a given array.

This function is useful for solving equations with multiple variables.

Array formula in cell B5:C6:

=MINVERSE(B2:C3)

The MINVERSE function must be entered as an array formula.

To enter an array formula, type the formula in a cell range then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Cell range B8:C9 and B11:C12 shows how the MINVERSE function calculates the inverse matrix.

MINVERSE(*array*)

array |
Required. An array containing numbers, the number of rows and columns must be the same. |

The MINVERSE function returns #VALUE if:

- the argument contains text or blanks.
- the number of rows and columns don't match.

]]>

The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even if it overlaps another match.

Formula in cell B6:

=SUMPRODUCT(SIGN(MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3))

A regular count would result in 2 matches, see picture below.

You can find the formula here: Count specific text string in a cell

A count where overlapping is allowed returns 3 matches and this is what is demonstrated in this article.

The LEN function counts the number of characters in cell B3.

LEN(B3) returns 13.

The INDEX function returns a cell reference based on a row number.

ROW(A1:INDEX(A1:A1000, LEN(B3)))

becomes

ROW(A1:INDEX(A1:A1000, 13))

becomes

ROW(A1:A13) and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}

The ROW function returns the row number of a cell. If a cell range is used the ROW function returns an array of row numbers.

MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))

becomes

MID(B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}, LEN(D3))

becomes

MID("nynynynyyynn", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}, 3)

and returns the following array:

MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3

becomes

{"nyn"; "yny"; "nyn"; "yny"; "nyn"; "yny"; "nyy"; "yyy"; "yyn"; "ynn"; "nn "; "n "; " "}=D3

and returns {TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

The SUMPRODUCT function can't handle boolean values so the SIGN function converts them into numbers. TRUE = 1 and FALSE = 0.

SIGN(MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3)

becomes SIGN({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})

and returns {1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0}.

SUMPRODUCT(SIGN(MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3))

becomes

SUMPRODUCT({1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0})

and returns 3 in cell B6. 1 + 0 + 1 + 0 + 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 = 3.

The stacked column chart is great for comparing parts of a whole and how they change over time or categories.

Unfortunately, the stacked column chart becomes more cluttered as you add more series to the chart.

This makes it a lot harder to compare parts across the x-axis, see picture below.

The first series "North" is easy to compare as each part is aligned to the x-axis, however, the remaining series are a lot more difficult to discern and also compare.

Here are the instructions on how to insert a stacked column chart to you workbook:

- Select the data you want to chart.
- Go to tab "Insert" on the ribbon.

- Click on "Column" chart button.

- Click on "Stacked Column Chart" button.

To move the chart to the location you want simply click and hold with left mouse button on the chart and drag to the location.

Now resize or perhaps align the chart to the cell grid if you prefer.

]]>To be able to resize a chart you must first select it, you do that by left-click on the chart with the mouse.

Click and hold with left mouse button on dots, then drag to resize.

The corner dots behave differently, they change two sides of the chart simultaneously.

Hold SHIFT key while dragging to keep the chart aspect ratio. The aspect ratio is the proportional relationship between its width and its height.

If you change the width the height must also change in order to keep the same aspect ratio.

An aspect ratio of 1:1 means that the height and width have the same size.

If you happen to change the row height or column width the chart will resize and "follow" the cell grid.

Follow these steps if you don't want the chart to move or size with cells.

- Right-click on the chart.

- Click on "Format Chart Area..."
- Click on "Properties" to expand settings.

- Select "Don't move or size with cells".

You can also change the chart size by going into the chart settings and change the height or width.

- Right click on a chart with the mouse.
- Click on "Format Chart Area..."

- Click on "Size & Properties" button.

- Change the height and width.

There are two ways to resize multiple charts, going into the chart settings or resize using the mouse.

To resize multiple charts you must select all charts with left mouse button while holding the CTRL key.

Release everything, now right-click on one of the charts with the mouse.

Click on "Size and Properties...".

Enter the height and width.

All charts now have the same height and width.

Select all charts with left mouse button while holding the CTRL key.

Now click and hold with left mouse button on a dot and then drag, the other charts will follow.

The following macro loops through each chart on sheet1 and changes the chart height and width.

Sub Macro1() For Each chrt In Worksheets("Sheet1").ChartObjects chrt.Height = 144 chrt.Width = 216 Next chrt End Sub

The NORM.INV function calculates the inverse of the normal cumulative distribution for a given mean and standard deviation.

Formula in cell C7:

=NORM.INV(C2,C3,C4)

NORM.INV(*probability*, *mean*, *standard_dev*)

probability |
Required. A probability corresponding to the normal distribution. |

mean |
Required. The average of the distribution. |

standard_dev |
Required. The standard deviation of the distribution. |

NORM.INV returns

- #VALUE! if argument is non-numeric.
- #NUM! if standard_dev ≤ 0
- #NUM! if probability <= 0 or if probability >= 1
- the standard normal distribution if mean = 0 and standard_dev = 1