lecture 4 - creating simple formula
Post on 28-Feb-2018
219 Views
Preview:
TRANSCRIPT
-
7/25/2019 Lecture 4 - Creating Simple Formula
1/30
Ms-Excel
Lecture 4
-
7/25/2019 Lecture 4 - Creating Simple Formula
2/30
Agenda
Use functions
Use the AutoSum button
Use the AutoSum list Use Formula AutoComplete
Insert functions in formulas
Edit functions
Use the AutoCalculate feature
Use range borders to modify formu
Check formula errors
-
7/25/2019 Lecture 4 - Creating Simple Formula
3/30
What is a Formu
Formulas are used to percalculations on values entered intocells of a worksheet. hey consist o
addresses of the cells containingvalues! constant values and appropriate mathematical operatorsFor e"ample! to add
the numbers in cellsC# and $#! youwould type theformula %C#&$#.
-
7/25/2019 Lecture 4 - Creating Simple Formula
4/30
Mathematical Operato
Operator Performs
& Addition
' Subtraction( )ultiplication
* $ivision
+ ,ercentage
- E"ponentiation ,ower/
-
7/25/2019 Lecture 4 - Creating Simple Formula
5/30
Using Formula
Since formulas use cell addresses!automatically recalculate whenevevalue in any cell used in the for
changes. 0hen a cell containinformula is selected! the forappears in the formula bar andcalculated results of the for
appear in the cell.
-
7/25/2019 Lecture 4 - Creating Simple Formula
6/30
Mathematical Operato
Operat
or
Performs
/ Controls the order of mathe
operations1 calculations parentheses are performed 2
+ Converts a number i
percentage1 for e"ample! fE"cel reads the value as .34
- E"ponentiation1 for e"amp
5-#! E"cel reads the value as
-
7/25/2019 Lecture 4 - Creating Simple Formula
7/30
Precedence
In case of using more than one opein a formula! it is calculated usingstandard mathematical order
precedence. he order of precedenas follows6
parentheses
e"ponentiation
multiplication and division
addition and subtraction
For e"ampresult of $4% on thhand the re&!"'#4 is
-
7/25/2019 Lecture 4 - Creating Simple Formula
8/30
Auto*orrect
E"cel7s Auto*orrect feature idenand o8ers suggestions on commistakes made while entering form
If an error is found! either accepcorrection provided or correct formula manually.
For e"ample!if a formula is entered +A$!,$!!Auto*orrect will suggethe formula+A$!,$.
-
7/25/2019 Lecture 4 - Creating Simple Formula
9/30
Anatom of a Formu
Formula begins with an e9ual sign the cell where result is re9uired.
Cell addresses can be either type
selected using the mouse or keybEach referenced cell have colborders with s9uares at their corner.
Enter arithmetic operators in the fo
wherever appropriate.
-
7/25/2019 Lecture 4 - Creating Simple Formula
10/30
*onstructing a Formu
ype an e9ual sign +/ to begin theformula.
Enter the 2rst cell referenced in the
formula.
Enter the 2rst mathematical operat
Enter the ne"t cell referenced in th
formula. Continue entering cell references a
mathematical operators as needed
0hen 2nished creating the formulaEnter
-
7/25/2019 Lecture 4 - Creating Simple Formula
11/30
Entering Formula
*ellcontainingFormula
*ell contentsin theFormula ,ar
*ells referredin the formula
Cell addrescase sensitaddress can
as either:owever! .Enter/ keyE"cel auchanges references
uppercase.
-
7/25/2019 Lecture 4 - Creating Simple Formula
12/30
Using Functions
E"cel has built'in functions thatshortcuts for formulas. Functionsspecial pre'written formulas that per
an operation on values or rangevalues and return the result to a cthe worksheet. Functions can be use
simplify and shorten formulasworksheets! especially those perform lengthy or comple" calculat
e a ormu a a unc on a so s ar s
-
7/25/2019 Lecture 4 - Creating Simple Formula
13/30
Using Functions
e a ormu a! a unc on a so s ar san e9ual sign %/ followed by function's name and! its argumenclosed in parentheses. he funuses the arguments in its calculatArguments may be none! cell addrevalues! labels! or even other functio
formulas! or a combination of these.FU1*23O11AMEA5UME12&6'
-
7/25/2019 Lecture 4 - Creating Simple Formula
14/30
Using Functions
Functions are most commonly used to pecalculations on a range of cells. For e"amis easier to use the %6UM&A$7A8' functadd the numbers in cellsA$ throughA8to type the fo+A$!A!A"!A4!A0!A9!A8
E"cel provides help in the form function Screenip. It displays structure of the function i.e.!function name and the order ore9uired arguments/.
he most commonly used functio
-
7/25/2019 Lecture 4 - Creating Simple Formula
15/30
Using Auto6um
he most commonly used functiothe 6UM function! which calculatetotal of the values in a range of Since the 6UM function is fre9uently! there is an Autobutton on the Formulas ta: ini::on that enters the formula in
active cell. Auto6umis an easy wsum values in a row or column worksheet.
n c c ng t e uto um utto
-
7/25/2019 Lecture 4 - Creating Simple Formula
16/30
n c c ng t e uto um uttosuggested range for the funappears. A blinking! colored bocalled a range 2nder de2nes suggested range.
If clicked at the end of a rowcontiguous row of values to the le
the active cell is suggested. If clicked at the bottom of a column
contiguous column of values abovactive cell is suggested.Using Auto6um
If there are values both above and
-
7/25/2019 Lecture 4 - Creating Simple Formula
17/30
If there are values both above andleft of the active cell! the concolumn of values above the activesuggested.
If the suggested range is incorrectbe changed by dragging to select tcontaining the values to calculate.
he Auto6um button provides anwhich! when clicked! displays a other functions can be used on thwithin the selected range.Using Auto6um
-
7/25/2019 Lecture 4 - Creating Simple Formula
18/30
Using Auto6um
3. Select the cell whereto enter the formula.
5. Click the left'hand partof the Auto6umbutton in theFunction Li:rargroup on theFormulas ta:)
#.,ress .Enter/)
arrow which displays a list of o
-
7/25/2019 Lecture 4 - Creating Simple Formula
19/30
Using Auto6um List
arrow! which displays a list of ofunctions that can be performeconsecutively 2lled cells in a colum
row. Each of these functions automat
use the cell range immediad=acent to the active cell for
suggested range. If suggested range is incorrect! it can be changedragging to select the cells contathe values
FU1*23 E;AMPLE
-
7/25/2019 Lecture 4 - Creating Simple Formula
20/30
FU1*23
O1
E;AMPLE E?A@
E
%A>E?A@EA36A
54!4/
?eturns the average of
numbers1 if a cell in th
empty! it is not used in calc
average1 if a cell in
contains the number ero!
calculating the average
)AB %)ABA36A54/ ?eturns the highest value of numbers
)I %)IA36A54/ ?eturns the lowest value in
numbersC
-
7/25/2019 Lecture 4 - Creating Simple Formula
21/30
Auto6um
3. Select the cell where to enter the form
5. Select the arrow on the Auto6um :utthe Function Li:rar group oFormula ta:)
#. Select the desired function.
D. $rag to select the range! if necessary.
. ?elease the mouse button.
. ,ress .Enter/)Click the More Functionscommand in the Auto6um toaccess all E"cel functions!opening 3nsert Function$ialo .
-
7/25/2019 Lecture 4 - Creating Simple Formula
22/30
he Formula Auto*omplete fedisplays valid functions! names and strings that match the letters in a dyn
drop'down list. ?e2ne the range of opby continuing to type trigger letters or through the list using keys on the keybo
Formula Auto*omple
-
7/25/2019 Lecture 4 - Creating Simple Formula
23/30
2ips
.entering the endpoints of a rangefunction A3.A/.
-
7/25/2019 Lecture 4 - Creating Simple Formula
24/30O tional Ar uments
argument and some have optarguments.
-
7/25/2019 Lecture 4 - Creating Simple Formula
25/30
3nsert FunctionsWiard
o open the 3nsert Functiondialog bo".
Click the 3nsert Function
:utton in the formula bar. Select the on the 3nsert
Function :utton in theFunction Li:rar group on
the Formula ta: ,ress .6@3F2/ ! .F"/ from
keyboard.
-
7/25/2019 Lecture 4 - Creating Simple Formula
26/30
3nsert FunctioWi
Search bo" help 2ndingthe function needed.
Function category
;ist of the functionswithin selected
category.
i l l l i l d
-
7/25/2019 Lecture 4 - Creating Simple Formula
27/30
Auto*alculate
simple calculation on a selected racells without making you supply a fo
he result of the calculation
temporary! appears on the Status are not placed in the worksheet.
y default! three of the Auto*alfunctions Aerage! *ountand 6uenabled in the Status ar and will their results as soon as a range ocontaining values is selected)
"ce ent es ts range re erenc
-
7/25/2019 Lecture 4 - Creating Simple Formula
28/30
ange ,orders -ModiFormulas
"ce ent es ts range re erencthe formula by displaying themdi8erently colored borders! with shandles at each corner.
User can change the argumentsformula by $ragging a range border to inclu
di8erent group of range. )oving the range border to referen
entirely di8erent range.
-
7/25/2019 Lecture 4 - Creating Simple Formula
29/30
ange ,orders -ModiFormulas
Changing the sie ofthe border to includemore or fewer cells.
he correspondingrange reference inthe formula changes
accordingly.
E"cel provides an error check
-
7/25/2019 Lecture 4 - Creating Simple Formula
30/30
E"cel provides an error checkfeature that automatically checformulas against a pre'e"isting set
rules. If a formula breaks a rule! error indicator appears in the forma colored triangle in the corner of tcell containing the suspect formula
*hecBing Formula Erro
top related