js02 mohamad qhiril fikri bin ahmad

8/9/2019 Js02 Mohamad Qhiril Fikri Bin Ahmad

1/24

QUESTION (1) HOW TO CREATE THE TABLE

In addition to its other spreadsheet features, Microsoft Excel offers you the ability to create

tables within a spreadsheet. Known as “lists” in Excel 2003, they can be managed separately

from data you have elsewhere on that spreadsheet page or any other page in the spreadsheet.

See Step 1 below for instructions to make and manipulate tables in Microsoft Excel.


2/24

QUESTION (1) HOW TO CREATE THE TABLE

1Select a range of cells. The cells can contain data or they can be empty, or a

combination of both. You don’t have to pick your cells before you create the table if you

aren’t sure yet.


3/24

2Insert the table. To start the table creation process, you will need to insert a table into

your spreadsheet.

• In Excel 2003, click the Data menu and select List.

• In Excel 2007, 2010, and 2013 select either "Table" on the Insert menu ribbon or

"Format as Table" in the Styles group on the Home menu ribbon. (The former option

applies Excel's default table style, while the other lets you choose a style when youcreate the table. You can later apply or change the table style by selecting one of the

options from the Table Styles group in the Table Tools Design menu ribbon.)[1]

http://www.wikihow.com/Make-Tables-Using-Microsoft-Excel#_note-1http://www.wikihow.com/Make-Tables-Using-Microsoft-Excel#_note-1


4/24

3Provide a data source for your table. If you did not previously select a group of cells,

you will need to select the cells now. After you pick your range, a dialog box will appear,

either the Create Table dialog (Create List dialog in Excel 2003) or the Format As Table

dialog.

• The "Where is the data for your table?" field displays the absolute reference(s) for

the current cell(s) selected. If you want to change this information, you can type in a

different cell or range reference.


5/24

4Indicate whether your table has headers. If your table has headers, check the "My

table has headers" box. If you don't check this box, the table will display default header

names ("Column 1," "Column 2," etc.).

• You can change a column name by selecting the header and typing in your own

name in the formula bar.


6/24

QUESTION (2) GIVE EXAMPLE FOR MATHEMATICS FORMULA USING EXCEL

Excel formula rules

1. Formulas are entered into cells in the same way as numbers or text.

2. The formula is displayed in the Formula bar, where it may also be easilymodified (it is labelled with the fx symbol).

3. formula must always be!in with an e"ual (#) si!n.

$. formula is created on the basis of mathematical rules.

%. fter pressin! the Enter &ey, Excel only displays the result in the celland the formula in the formula bar.


7/24

'ore informations about Excel

Excel formula and mathematics

e showed how to add two alues in the example. e used the * si!n, whichis well &nown in mathematics. +emainin! mathematical operations use the

followin! si!ns in formulas

*......................addin!

-..............subtraction

..........multiplication

/......................diide

0.................percents

.....................power


'odifyin! a formula in Excel

emands chan!e oer time in eery occupation, and we must similarly adapt

Excel formulas to meet these demands (calculatin! expenses, monitorin!

reenues, tax calculations, etc.).

Example

Revising a formula directly in the formula bar is the easiest. For example, we

would like to add the number 10 to the formula.

http://excel-formula.fotopulos.net/excel.htmlhttp://excel-formula.fotopulos.net/excel.htmlhttp://excel-formula.fotopulos.net/excel.htmlhttp://excel-formula.fotopulos.net/excel.html


8/24

'odifyni! a formula in Excel step-by-step

1. e first clic& on the cell, to which the formula applies, usin! the mouse i.e. cell 3. This displays the formula in the formula field.

2. e clic& on the field and we can modify it usin! the familiar text cursor that appears. e simply add * 14 to the end of the formula.

3. fter reisin! the formula, we press the Enter &ey and the reisedformula is saed, and the formula in cell 3 is immediately calculated.


+elationship between cells

5n this chapter, we will show how Excel can be used to ma&e wor& easier. 5t

ma&es our wor& easier by calculatin! lar!e "uantities of numbers for us,

without the need of enterin! them manually into a calculator. 5f we place the

calculator aside and calculate eerythin! usin! the formulas in the

spreadsheet, we hae si!nificantly reduced our efforts in the future. 5f any

situation chan!es, we need only to chan!e the data and Excel calculates

eerythin! automatically. 5f we rely on a calculator, we would hae to calculate

eerythin! from the be!innin!.

ssume that we are a car salesman and we create the followin! table, which

we will also apply in the followin! examples (therefore, sae it)

http://excel-formula.fotopulos.net/excel.htmlhttp://excel-formula.fotopulos.net/excel.html


9/24

Example

The end of the uarter is approaching and our boss asks us for the sales

results of individual car models. !pecifically then, how much cars of each

model were sold over the last uarter.

First, we calculate the sales of Ford ehicles for the months of 6anuary *

February * 'arch, and we enter the result in cell E2

1. e clic& on cell E2 (selection indicated by bold frame).

2. e write the formula #72*82*2.

3. fter pressin! the Enter &ey, the formula is calculated and the alue 194appears in cell E2.


8opyin! a formula within the table



10/24

:ur table is finished. The first formula for calculatin! the sales of Ford ehicles

is also finished. ;ow we only need to finish the sales for the remainin!

ehicles.


11/24

8han!in! the contents of the cell will also chan!e the result of the formula

:ur sales results are finished and we wish to send them to our boss. sales

representatie, howeer, informs us that he sold one extra Ford. The solution

is easy. e do not need to recalculate anythin!, but only reise the number in

the appropriate cell. e only need to reise the input data and the formula

automatically recalculates the result.

Table prior to reision

+eised table


Excel formula with function

Excel formulas often use functions. e will thus demonstrate how to insert

them into formulas.

Example



12/24

"ur boss would like us to calculate how many cars were sold in total.

The easiest way of accomplishin! this is by usin! the >?' function

1. e select cell @2.

2. e type in it the formula #>?'(E2E9).

The >?' function has its own tutorial. The explanation of the formula will thus

be brief. >?' adds to!ether the selected cells. 5n our case, it adds the column

that be!ins with cell E2 and ends with cell E9 (outlined in blue on the picture).


Aabellin! a cell

Tables and calculations are easier to iew when we label the cells ourseles.

ith Excel, we can name a cell as we wish and use this name when

calculatin! formulas.

Aet us label the cell @2 as sum

1. e select the cell @2 by clic&in! on it (boldly outlined).

2. :erwrite @2 to sum in the name box and press Enter. The name box islocated left of the formula bar.



13/24

e can use the labelled cell in formulas for calculations Bust as any other.5nstead of @2, we write sum in the formula. e demonstrate in a simple

formula. e wish to multiply the total number of sold cars (!ien in the cell,

labelled sum) twice, e.!. by the number in cell


14/24

#our boss asked you to prepare a daily current price list of vehicles in several

currencies $for example, in dollars, !wiss francs, and rubbles%.

e are usin! Excel, so we are not !oin! to slae eery day oer a calculator.

5t suffices that we &now how to wor& with a table and how to create formulas,so we may be!in our tas& without fear.

Crepare and sae the followin! table

e shall label cells D to 11 accordin! to the names of currencies, and we

shall fill in the current alues of these currencies into these cells.

e hae prepared our table of currencies, and we can therefore be!in writin!

formulas

1. Formula for conertin! to the dollar amount is written #72/dollar.

2. e place the formula into cell 82.


15/24

e apply the formula in cell 82 (cost calculation of Fords in dollars) to all

ehicles. e notice that the cell labelled dollar does not chan!e in theformulas of remainin! cars. That is the adanta!e of the destination ran!e. 5f

instead we use the source ran!e, then the cost of the Toyota in the third line

will be calculated in francs instead of dollars.

e calculate the prices for other currencies in the same manner


16/24

The spreadsheet is now ready. e only need to come to wor& eery mornin!

and find out the current exchan!e rate. e insert the current exchan!e rateinto the correct cell, and Excel calculates eerythin! by itself. e can use the

time we saed to ma&e some coffee or to learn a second method of assi!nin!

destination ran!es usin! the (dollar) si!n. e will demonstrate this in the

next example

&hile our boss is satisfied that he has a current price list of vehicles every day

in several currencies, he 'ust remember that he wants a list of uarterly

revenues. (n fact, he needs the revenues of individual vehicle models for

every month separately.

e copy our ori!inal table into our spreadsheet and create the followin! table

below it


17/24

5n cell 723 we write the formula #7271%, which we apply to the remainin!

cells

The result in cell 723 is correct, but the other cells display outri!ht nonsense.

This happened because we did not use the dollar to fix column 7, which lists


18/24

the costs of ehicles. e therefore reise the formula into the correct form and

a!ain copy it across all months

#7271%

ll sales reenue alues in the table are now correct and we finish calculatin!

the entire table

This picture shows how to select a column. e accomplished this by usin! the

symbol 72= howeer, it occasionally happens that we need to set a row

instead of a column when performin! the calculation. This can be done ery

easily. e moe the dollar si!n between the dollar and number so it reads

72. 5n this manner, we effectiely set the row.

e did not select this final example by chance. The calculated results show

us how much wor& Excel can sae us. e can now easily create price lists in

indiidual currencies. e already &now how to do this -- we only need to

oerwrite the exchan!e rate of each currency in the cells, which are labelled

accordin! to their names of currencies. e hae prepared a table for

calculatin! reenues, which saes us further wor&.


19/24

QUESTION(3) HOW TO INSERT SYMBOL RM WITH DECIMAL PLACES

1. f!" #$%#&$%#" "#' *+' ", +' -,'"'/ ", /'-$*0&


20/24

2. R$%#"-&$- 0/ ,' "#' 4,*0" -'&&!


21/24

3. WHEN THE SETTING FORMAT CELLS COME OUT5GO TO NUMBER AND

CLIC6 IT


22/24

7.AFTER CLIC6 THE NUMBER CHANGE THE DECIMAL PALCES TO (2 DECIMALPLACES) THEN CLIC6 O6

T#$! '!&" 80! 8#0" 8' 8$!#'/ 4,


23/24

9. T#' %, +0- ", "#' 4,*0" 0/ $%#" -&$- 0/ f/ "#' -'-: 0/

-,'"'/ ", RM

T#$! $! "#' '!&" 04"' 8' -&$-

C,-&!$,!


24/24

I -0 &'0 !$% *$-,!,4" ';-'& -,'-"&: 0/ !$% "#'

4,*&0

js02 mohamad qhiril fikri bin ahmad

Documents