The AVERAGE function allows you to average the values in a range of cells. It is written in much the same way as the SUM function, for example,
=AVERAGE(range of cells to average). Theaverage function can be applied using the Functions Wizard, a part of Excel that steps you through the process of creating a function or you can type it in yourself if you are comfortable with it.
3
Try This Yourself: |
|
Same File |
Continue using the previous file with this exercise, or open the file E710 Formulas_6.xlsx... |
|
Click on B29 then click on the Insert Function tool |
|
Click on AVERAGE in Select a function then click on [OK] to display the Function Arguments dialog box |
|
Click on the Range Selector |
|
and select the following ranges |
|
B6:B8 |
|
B11:B13 |
|
B16:B18 |
|
B21:B23 |
|
Press |
|
Click on B34, then click on the drop arrow for the Sum command |
|
Click on B9, hold down |
The Minimum or MIN function allows you to extract the lowest value from a range of values. It is written in much the same way as the SUM function. For example, =MIN(range of cells).
The function can be applied using the Function Wizard, or by typing the function in detail directly into the cell.
Microsoft Excel has some in-built messages that can assist you when something goes wrong with a formula. These messages appear in the cell that contains the formula, and sometimes also
other formula cells that depend upon it. The messages are always prefixed with a hash sign (#) and appear with a code. The more common error messages are listed below.
Sometimes referred to as “tramlines”, a line of hash signs usually occurs because a column is not wide enough to display the numbers in the cell or formula. Widening the column will correct this problem – you can drag the column heading until the value in the cell appears as it should.
This message means you are trying to divide a value by zero – this is mathematically impossible. In the example at the left we are trying to find the average number of persons per household. All is fine as long as there is a value greater than zero in cell B3 (Houses). As soon as we change this to a zero an error message appears in the formula cell (B5).
To prevent the error you will need to enter a value greater than zero into cell B3, the divisor cell.
In this message Excel is advising that something in the formula is not a value and therefore a calculation can’t be made.
A close examination of the example at the left shows cell B3 contains the word “three”. Therefore the formula in cell B5 is trying to divide 192,664 (in cell B2) with a word, which doesn’t make sense.
To fix the error, a value (a number) will need to be entered in cell B3.
This message appears when text is found in a formula that can’t be matched to either a legitimate function or range name.
In the example to the left, the formula has been entered as =SOME(B3:B7) – there is no such function as SOME, and presumably the author should have typed =SUM(B3:B7).
Formulas And Functions
Tasks: |
Completed: |
|
|
Before starting this exercise you MUST have completed all of the topics in the chapter Formulas And Functions… |
|
|
Open the workbook called PE_Formulas And Functions.xlsx (it can be found in the same folder as the student files) |
c |
|
Create a formula that calculates the gross pay for each employee, then use a function to calculate the total of the gross pay |
c |
|
Create a formula that calculates the tax as being 20% of the gross pay for each employee, then create a total for the tax |
c |
|
Create a formula to calculate the net pay for each employee and then a total of the net pay |
c |
|
Create a formula that calculates the superannuation as being 8% of the gross pay for each employee, then create a total for superannuation |
c |
|
Use functions to determine the average, maximum and minimum values for each column, setting the number of decimal places to 2 |
c |
|
Use the Save As command to save the workbook as PE_Formulas And Functions (Completed).xlsx |
c |
Source: https://www.sgul.ac.uk/about/our-professional-services/information-services/library/documents/training-manuals/Excel-Fundamentals-Manual.pdf
Web site to visit: https://www.sgul.ac.uk
Author of the text: indicated on the source document of the above text
If you are the author of the text above and you not agree to share your knowledge for teaching, research, scholarship (for fair use as indicated in the United States copyrigh low) please send us an e-mail and we will remove your text quickly. Fair use is a limitation and exception to the exclusive right granted by copyright law to the author of a creative work. In United States copyright law, fair use is a doctrine that permits limited use of copyrighted material without acquiring permission from the rights holders. Examples of fair use include commentary, search engines, criticism, news reporting, research, teaching, library archiving and scholarship. It provides for the legal, unlicensed citation or incorporation of copyrighted material in another author's work under a four-factor balancing test. (source: http://en.wikipedia.org/wiki/Fair_use)
The information of medicine and health contained in the site are of a general nature and purpose which is purely informative and for this reason may not replace in any case, the council of a doctor or a qualified entity legally to the profession.
The texts are the property of their respective authors and we thank them for giving us the opportunity to share for free to students, teachers and users of the Web their texts will used only for illustrative educational and scientific purposes only.
All the information in our site are given for nonprofit educational purposes
to display the Insert Function dialog box
for Number1 to roll up
to complete the range specifications, then click on [OK] to complete the process
on the Editing group, then select Average
and click on B14, B19 and B24, then press 
to complete the formula