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
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.
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 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
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
