Simple statistical functions

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:

Excel expense list

Excel expense list

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.

Using AutoSum to add up costs

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.

Using the average function on costs

Using the average function to return the average costs

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

 

Using the max function to return the highest cost

Using the max function to return the highest cost

 

Using the min function to return the lowest cost

Using the min function to return the lowest cost

 

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.
Formatting cells using the Accounting format

Formatting cells using the Accounting format

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

Related Posts Plugin for WordPress, Blogger...

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge
 
Textbox Section
 

Contact us

Would you like to talk through your IT Training needs? Just send through your details and we'll get back to you.