Excel Formulas- free tutorial

Time 2020-07-17 20:02:49

Web Name: Excel Formulas- free tutorial

WebSite: http://excel-formula.fotopulos.net

ID:27475

Keywords:

Formulas,Excel,tutorial,excelformula,excelformulas,excelformulatutorial

Description:

Excel is a wonderful aid when calculating and analyzing data.In this chapter, we shall discuss procedures that order Excel tocalculate numbers for us. This is done by writing a formula. For theformula to work, it must meet several rules. We therefore begin thischapter with the topic of Excel formula rules. The formula is displayed in the Formula bar, where it mayalso be easily modified (it is labelled with the fx symbol). A formula must always begin with an equal (=) sign. A formula is created on the basis of mathematical rules. After pressing the Enter key, Excel only displays theresult in the cell and the formula in the formula bar.We showed how to add two values in the example. We used the +sign, which is well known in mathematics. Remaining mathematicaloperations use the following signs in formulas:+......................adding-..............subtraction*..........multiplication/......................divide%.................percents^.....................powerMore informations about ExcelDemands change over time in every occupation, and we must similarlyadapt Excel formulas to meet these demands (calculating expenses,monitoring revenues, tax calculations, etc.).Example:Revising a formula directly in the formula bar is the easiest. Forexample, we would like to add the number 10 to the formula.Modifynig a formula in Excel step-by-step: We first click on the cell, to which the formula applies,using the mouse – i.e. cell D3. This displays the formula in theformula field. We click on the field and we can modify it using thefamiliar text cursor that appears. We simply add + 10 to the end of theformula. After revising the formula, we press the Enter key and therevised formula is saved, and the formula in cell D3 is immediatelycalculated.In this chapter, we will show how Excel can be used to make workeasier. It makes our work easier by calculating large quantities ofnumbers for us, without the need of entering them manually into acalculator. If we place the calculator aside and calculate everythingusing the formulas in the spreadsheet, we have significantly reducedour efforts in the future. If any situation changes, we need only tochange the data and Excel calculates everything automatically. If werely on a calculator, we would have to calculate everything from thebeginning.Assume that we are a car salesman and we create the followingtable, which we will also apply in the following examples (therefore,save it):Example:The end of the quarter is approaching and our boss asks us for thesales results of individual car models. Specifically then, how muchcars of each model were sold over the last quarter.First, we calculate the sales of Ford vehicles for the months ofJanuary + February + March, and we enter the result in cell E2: After pressing the Enter key, the formula is calculated andthe value 160 appears in cell E2.Our table is finished. The first formula for calculating the sales ofFord vehicles is also finished. Now we only need to finish the salesfor the remaining vehicles. Here is where Excel makes the work easier.We do not need to enter the formula for each vehicle separately;instead, we only copy the formula for Ford vehicles into the fields ofremaining vehicles: The cell border is displayed in bold, and a small boxappears in its bottom right corner. We click on the box twice, and theformula calculates the result also for the other rows.If we only wish to calculate certain rows, then we only clickonce on the box while holding the button and dragging the mouse intothe row we select. We then release the button, and the formula iscalculated.More informations about ExcelOur sales results are finished and we wish to send them to our boss. Asales representative, however, informs us that he sold one extra Ford.The solution is easy. We do not need to recalculate anything, but onlyrevise the number in the appropriate cell. We only need to revise theinput data and the formula automatically recalculates the result.Table prior to revision:Revised table:More informations about ExcelExcel formulas often use functions. We will thus demonstrate how toinsert them into formulas.Example:Our boss would like us to calculate how many cars were sold in total.The easiest way of accomplishing this is by using the SUM function:The SUM function has its own tutorial. The explanation of theformula will thus be brief. SUM adds together the selected cells. Inour case, it adds the column that begins with cell E2 and ends withcell E6 (outlined in blue on the picture).More informations about ExcelTables and calculations are easier to view when we label the cellsourselves. With Excel, we can name a cell as we wish and use this namewhen calculating formulas.Let us label the cell G2 as sum: Overwrite G2 to sum in the name box and press Enter. Thename box is located left of the formula bar.We can use the labelled cell in formulas for calculations justas any other. Instead of G2, we write sum in the formula. Wedemonstrate in a simple formula. We wish to multiply the total numberof sold cars (given in the cell, labelled sum) twice, e.g. by thenumber in cell H2:=2*(sum)In Excel, we can label not only a cell, but even an entire row, ifdesired. We can also label anentire column, or a series of cell thatwe select directly. A superior feature is dynamic labelling, whichautomatically expands the area by new entries in the table.An additional note in conclusion: Destination range is used in celllabelling of formulas. This means that the referenced cell remainsunmodified when copying a formula.More informations about ExcelDestination range means that a cell never changes when copying aformula. We demonstrate the principle on the following example:Your boss asked you to prepare a daily current price list of vehiclesin several currencies (for example, in dollars, Swiss francs, andrubbles).We are using Excel, so we are not going to slave every day over acalculator. It suffices that we know how to work with a table and howto create formulas, so we may begin our task without fear.Prepare and save the following table:We shall label cells A9 to A11 according to the names ofcurrencies, and we shall fill in the current values of these currenciesinto these cells.We have prepared our table of currencies, and we can thereforebegin writing formulas:We apply the formula in cell C2 (cost calculation of Fords indollars) to all vehicles. We notice that the cell labelled dollar doesnot change in the formulas of remaining cars. That is the advantage ofthe destination range. If instead we use the source range, then thecost of the Toyota in the third line will be calculated in francsinstead of dollars.We calculate the prices for other currencies in the samemanner:The spreadsheet is now ready. We only need to come to workevery morning and find out the current exchange rate. We insert thecurrent exchange rate into the correct cell, and Excel calculateseverything by itself. We can use the time we saved to make some coffeeor to learn a second method of assigning destination ranges using the $(dollar) sign. We will demonstrate this in the next example:While our boss is satisfied that he has a current price list ofvehicles every day in several currencies, he just remember that hewants a list of quarterly revenues. In fact, he needs the revenues ofindividual vehicle models for every month separately.We copy our original table into our spreadsheet and create thefollowing table below it:In cell B23 we write the formula =B2*B15, which we apply tothe remaining cells:The result in cell B23 is correct, but the other cells displayoutright nonsense. This happened because we did not use the dollar $ tofix column B, which lists the costs of vehicles. We therefore revisethe formula into the correct form and again copy it across all months:=$B2*B15All sales revenue values in the table are now correct and we finishcalculating the entire table:This picture shows how to select a column. We accomplishedthis by using the symbol $B2; however, it occasionally happens that weneed to set a row instead of a column when performing the calculation.This can be done very easily. We move the dollar sign between thedollar and number so it reads B$2. In this manner, we effectively setthe row.We did not select this final example by chance. The calculated resultsshow us how much work Excel can save us. We can now easily create pricelists in individual currencies. We already know how to do this -- weonly need to overwrite the exchange rate of each currency in the cells,which are labelled according to their names of currencies. We haveprepared a table for calculating revenues, which saves us further work.Here comes the best part. The calculations in the tables areinterconnected, and this will save us a great deal of additional work.Imagine that the cost of cars changes every day. A manual calculationwould take up an unbelievable amount of time, but since thecalculations in the table are only related within individual cells, werevise the current costs of vehicles and Excel calculates everythingfor us. It calculates for us not only the foreign currency conversion,but it also calculates the revenues.More informations about Excel© Fotis Fotopulos, 2009Pictures © Fotis Fotopulos, 2009

TAGS:Formulas Excel tutorial excelformula excelformulas excelformulatutorial

<<< Thank you for your visit >>>

Excel Formula - free tutorial for beginner.

Hot Websites