Basic Excel Formulas for Estimating:

Name: Marie France Kalonji

Date: $1/10/20248$:$38$

Instructions: This assignment is a crashcourse$/$reminder on how to use many of the basic excel functions that we will be using throughout the semester to complete our estimates. Recreate the formulas in the white columns by typing them exactly as shown into the gray boxes next to them. DO NOT copy and paste equations into the gray boxes, as the equation will not work properly. Submit a completed copy of the excel sheet with your name on it in the dropbox on ilearn by the due date.

$1.$ Using Parenthesis in a Basic Math Equation:

When using excel for basic math functions, the rules of PEMDAS apply as they would if the equation was solved by hand or on another calculator.

A $=2+3-4*5/6$

B $=(2+3-4)*5/6$

C Why were the results for A and B different? Explain.

$2.$ Exponents and Square Roots:

Excel has a built in function for calculating square roots, denoted: $=$SQRT$().$ In this example, we will use it to find the Hypotenus of a right triangle.

Knowns:

Side $1$: $3$

Side $2$: $4$

A $=$SQRT$($F$27+$F$28)^2$

B $=$SQRT$($F$27^2+$F$28^2)$

C Why did formula A and give a different value for the hypotenuse than formula B$?$ Why is formula A incorrect?

$3.$ The Sum Function:

The sum function, denoted as $=$SUM$(),$ allows us to add the values in cells together to get a total faster than if we added them together manually. This is especially useful when we need to know the total amount of something when adding up a long list of numbers or quantities in an estimate.

Numbers to Use:

$1234567$

A $=$D$46+$E$46+$F$46+$G$46+$H$46+$I$46+$J$46$

B $=$SUM$($D$46$:J$46)$

$4.$ Rounding Functions:

There are many different functions we can use to round a number in excel, a few of which are shown below.To round down to the nearest specified multiple, use the FLOOR function. To round up to the nearest specified multiple, use the CEILING function. To round up to the nearest specified place, use the ROUNDUP function.

Number to Round: $7\mathrm{.}21$

A $=$ROUNDUP$($F$57,0)$

B $=$CEILING$($F$57,0\mathrm{.}5)$

C $=$FLOOR$($F$57,0\mathrm{.}5)$

D What are some scenerios in which these rounding methods would be useful? Brainstorm ideas.

$5.$Combining Cells:

The concatenate function in excel is used to combine difference pieces of information, text, or values from different cells into one output cell. There are two different methods to combining cells, but if done correctly they willl give the same result. Spaces are added between the $""($quotation symbols$)$ to increase the space between the data when it is combined.

INPUT: John

Smith

A $=$E$79$&$""$&E$80$

B $=$CONCATENATE$($E$79,"",$E$80)$

$6.$ Cell Formatting

In excel we can format cells based on the data they contain. Cells can be formatted to show values in currency, fractions, as a percentage, and many other ways. This is extremely useful when estimating because we need to keep track of both the amount of the materials and the cost of those materials. By formatting our cells, we can quickly tell which cell is the cost verses the quantity based on the formatting of the cell.

Use the number in cell E$97$ as the value to format by typing $=$E$97$ in each of the gray cells, or clicking on the gray cell, typing the equals sign, $=$ and clicking on the cell E$97.$ Then use the "Number" dropdown on the Hometab to change the format of each of the cells as specified.

Input: $0\mathrm{.}57$

Number

Currency

Accounting

Percentage

Fraction

Scientific

$7.$Basic Excel Formulas for Estimating:

Name: Marie France KalonjiThe VLOOKUP Function

The VLOOKUP Function tells Excel what information you want it to look up$,$ and where to look for it$.$ It is useful when there is data on multiple different sheets within the work book that all needs to be referenced on another sheet. Example: the different total costs for