Simple statistical functions

Excel provides access to a range of simple statistical functions from the AutoSum button drop-down list.

The Sum function

Excel is often used in a business process situation to keep a list of expenses, e.g. travel expenses, coffee, and meeting fees.

A list has been created containing headings and then the relevant receipt details below this:

There is a column for date, area (location) item, and cost in the list.

The task is to add up the costs and create a total at the bottom of the cost column.

Autosum

To create a total, the Autosum button in the Editing group of the Home ribbon is an obvious route.

Excel assumes what to add up by placing a dashed area around the nearest group of values.  Adjusting this is a matter of amending the dashed selection with the mouse or keyboard.

The formula in cell D14, =Sum(D2:D13) suggests adding the cell range from D2 to D13.  Pressing the Enter key would complete the calculation.

Other simple statistical functions

Average, Max, and Min functions

To calculate average, maximum, and minimum costs, access the functions from the AutoSum button drop-down list.

After choosing the appropriate function, be aware that the range selected might need to be adjusted using the mouse or the keyboard.

Format values as currency

The costs in column D need to have a currency symbol in front of the figures.  In the UK, this is a £ sign.   There are some different ways of applying this in Excel.

• The accounting format button in the Number group on the Home tab
• Using the shortcut key [CTRL]+1 to access the format cells dialog box.

The numbers are now in the accounting format, which formats values to two decimal places, adds commas for thousands and replaces a zero with a dash (-).

Have a go and let me know how you get on.

To learn about Microsoft Excel, give us a call on 020 8203 1774 or email enquiries@theittrainingsurgery.com and find out how to help your organisation get better results.  If you are looking for more information on Microsoft Excel, try the following blog Microsoft Excel blog.

Regards

Paul