The GAMMADIST function calculates the gamma often used in queuing analysis in probability statistics.

This function is outdated and has been replaced by the GAMMA.DIST function which was introduced in Excel 2010.

Formula in cell C7:

=GAMMADIST(C3,C4,C5,C6)

GAMMADIST(*x,alpha,beta,cumulative*)

x |
Required. |

alpha |
Required. |

beta |
Required. 1 returns the standard gamma distribution. |

cumulative |
Required. A boolean value. TRUE - cumulative distribution function FALSE - probability density function |

The GAMMADIST function returns

- #VALUE! error value if
*alpha, beta*or*x*is non-numeric. - #NUM! error value if:
- x < 0 (zero)
- alpha <= 0 (zero)
- beta <= 0 (zero)

The equation to calculate the gamma probabiltiy density function:

The standard gamma probability function:

]]>The image above demonstrates the DATEDIF function calculating the number of complete years between two dates. Column B and column C contains random dates.

Formula in cell D3:

=DATEDIF(B3,C3,"Y")

Copy cell D3 and paste to cells below. The DATEDIF function requires the start date to be less than (earlier than) the end date, the function returns #NUM if not. It is the third argument that determines which unit to use. "Y" is the abbreviation for the year unit.

Note that the DATEDIF function does not appear when you start typing the function, however, it is there don't worry. The same thing happens when you start typing the arguments, you get no guidance from Excel.

* Why can't I divide the number of days between dates with 365?*Some years are leap years and those years have 366 days.

The image above shows top 10% values using conditional formatting applied to cell range B2:G13. Cell range B2:G13 contains 72 numbers, 10% of 72 is roughly 7. These seven values are then highlighted.

- Select cell range.
- Go to tab "Home" on the ribbon if you are not already there.
- Click on "Conditional Formatting" button.
- Click on "Top/Bottom Rules".

- Click on "Top 10 %..."

- A dialog box appears see image above, choose how many top items you want to highlight, the default value is 10.

The drop-down list contains preconfigured formattings. "Custom Format.." opens another dialog box with more detailed settings, see image below.

Chart gridlines are great for making the chart data more readable and detailed, Excel allows you to add major and minor gridlines to a chart. The major gridlines coincide with axis values and major tick marks.

- Select the chart.
- Click "plus" sign.
- Click checkbox "Gridlines".

- Click the arrow to expand options.

- Now select the gridlines you want on your chart:
- Primary Major horizontal
- Primary Major vertical
- Primary Minor horizontal
- Primary Minor vertical
- Secondary Major horizontal
- Secondary Major vertical
- Secondary Minor horizontal
- Secondary Minor vertical

The following chart shows both major and minor gridlines in an x y scatter chart.

- Double-click with left mouse button on axis values to open the task pane on the right side of your Excel window.

Double click the x axis values if you want to change the interval of vertical major and minor gridlines and vice versa. - Go to tab "Axis Options" on the task pane.
- Click on "Axis Options" arrow to expand settings.
- Change the major and minor units in order to change the gridline interval.

The F.TEST function calculates the two-tailed probability from an F-test, the value shows if the variances from two data sets are not significantly different.

This function was introduced in Excel 2010 has replaced the FTEST which is now outdated.

Formula in cell D12:

=F.TEST(B3:B9,D3:D9)

F.TEST(*array1, array2*)

array1 |
Required. The first data set. |

array2 |
Required. The second data set. |

The arguments must contain numbers or cell references to numbers.

Text, boolean and empty values are ignored.

The F.TEST function returns

- #DIV/0! error value if the number of items in each data set don't match.
- #VALUE! error value if any argument is non-numeric.

The LINEST function calculates the F statistic whereas the F.TEST function calculates the probability.

]]>The FTEST function calculates the value from an F-test. The value shows if the variances from two data sets are not significantly different.

This function is outdated and is replaced with the F.TEST which was introduced in Excel 2010.

Formula in cell D12:

=FTEST(B3:B9,D3:D9)

FTEST(*array1, array2*)

array1 |
Required. The first data set. |

array2 |
Required. The second data set. |

The arguments must contain numbers or cell references to numbers.

Text, boolean and empty values are ignored.

The FTEST function returns

- #DIV/0! error value if the number of items in each data set don't match.
- #VALUE! error value if any argument is non-numeric.

The LINEST function calculates the F statistic whereas the FTEST function calculates the probability.

]]>The Lbound and Ubound functions calculate the size of of an array. The Lbound returns the lower limit of an array and the Ubound function returns the upper limit.

Sub Macro4() Rng = Range("D2:F6").Value MsgBox "Lower bound(1): " & LBound(Rng, 1) & vbNewLine & _ "Upper bound(1): " & UBound(Rng, 1) & vbNewLine & _ "Lower bound(2): " & LBound(Rng, 2) & vbNewLine & _ "Upper bound(2): " & UBound(Rng, 2) End Sub

The macro above populates variable Rng with values from range D2:F6, the Lbound and Ubound functions then return the lower and upper limits for the first dimension (rows) and the second dimension (columns).

LBound( *arrayname, * [*dimension*] )

UBound( *arrayname, * [*dimension*] )

arrayname |
Required. The array variable. |

[dimension] |
Optional. Which dimension to use, the default value is 1. |

The following macro creates array variable TestArray with one dimension, lower limit of 1 and upper limit of 3.

Sub Macro1() Dim TestArray(1 To 3) MsgBox LBound(TestArray) & vbNewLine & UBound(TestArray) End Sub

This macro creates array variable TestArray with two dimensions, the first dimension has the lower limit of 1 and an upper limit of 3, the second dimension has the lower limit of 1 and the upper limit of 10.

Sub Macro2() Dim TestArray(1 To 3, 1 To 10) MsgBox "Lower bound(1): " & LBound(TestArray, 1) & vbNewLine & _ "Upper bound(1): " & UBound(TestArray, 1) & vbNewLine & _ "Lower bound(2): " & LBound(TestArray, 2) & vbNewLine & _ "Upper bound(2): " & UBound(TestArray, 2) End Sub

This macro populates array variable TestArray with values from cell range B3:B8, the first dimension has the lower limit of 1 and an upper limit of 6, the second dimension has the lower limit of 1 and the upper limit of 1.

Sub Macro3() Rng = Range("B3:B8").Value MsgBox "Lower bound(1): " & LBound(Rng, 1) & vbNewLine & _ "Upper bound(1): " & UBound(Rng, 1) & vbNewLine & _ "Lower bound(2): " & LBound(Rng, 2) & vbNewLine & _ "Upper bound(2): " & UBound(Rng, 2) End Sub]]>

The image above shows top 10 values using conditional formatting applied to cell range B2:G13.

- Select cell range.
- Go to tab "Home" on the ribbon if you are not already there.
- Click on "Conditional Formatting" button.
- Click on "Top/Bottom Rules".

- Click on "Top 10 Items..."

- A dialog box appears see image above, choose how many top items you want to highlight, the default value is 10. The drop-down list contains preconfigured formattings. "Custom Format.." opens another dialog box with more detailed settings.

Enable error bars when you want to show, for example, standard deviations in a chart, Excel lets you insert error bars to the following charts:

- 2-D area chart
- Bar chart
- Column chart
- Line chart
- x y scatter chart
- Bubble chart

Scatter and bubble charts let you show error bars for both x and y values.

- Select chart.
- Click on "plus" sign next to chart.
- Click on checkbox "Error Bars" to enable it.
- The arrow next to the "Error Bars" checkbox lets you choose between 3 different error bars:

- Double-click on one of the error bars to open the task pane.

- Click "Error bars options" tab to display the settings above.

Excel lets you change the direction of the error bar, the end style and the error amount.

]]>The NETWORKDAYS.INTL function calculate the number of working days between two dates, excluding weekends. It also allows you to ignore a list of holiday dates that you can specify. You may specify which days are weekend days.

Formula in cell D3:

=NETWORKDAYS.INTL(B3,C3,"1110011",B6:B7)

NETWORKDAYS.INTL(*start_date, end_date, [weekend], [holidays]*)

start_date |
Required. |

end_date |
Required. |

[weekend] |
Optional. Allows you to specify which days are weekend days using a number o a string. |

[holidays] |
Optional. Excludes date(s) from being counted. |

Number |
Weekend days |

1 (default value) |
Saturday, Sunday |

2 | Sunday, Monday |

3 | Monday, Tuesday |

4 | Tuesday, Wednesday |

5 | Wednesday, Thursday |

6 | Thursday, Friday |

7 | Friday, Saturday |

11 | Sunday only |

12 | Monday only |

13 | Tuesday only |

14 | Wednesday only |

15 | Thursday only |

16 | Friday only |

17 | Saturday only |

You may also specify weekend days using a string containing only 1 and 0 (zero). Example, 1110011 considers only Thursdays and Fridays as workdays, all other days in the week are weekend days.

]]>