The difference between ROUND and formatted



A formula starts with an equal sign. ??Ex: =(D1+D2) ??or =2+4) ?A formula can use functions. ?Ex: =AVERAGE(A1:A40)All Functions are Formulas. Not all Formulas are Functions.You can identify Functions if you see the reserved-word Function name. ?In this case, AVERAGE.A function is a built-in operation, such as SUM(), AVERAGE() etc. ?Functions are always capitalized. That which you put between the parentheses is called the argument, and it must be the right type of data, etc. ?For example, you wouldn’t want to sum a date, you’d want to sum a number. ?How to debug a function: Formulas > Formula Auditing > Evaluate Formula. This will display the value of each variable right before Excel does its calculations. Order of operation is important: (, ), -, +, * / ?or: parenthesis, minus, plus, multiplication, and divide. If a formula is getting messy, put parenthesis around them to take control of the order of calculations.Exercise #1 – Math tabThe difference between ROUND and formatted %Open the file “ClassWorkbookBudget.xls” and click on the Math tab.$10 divided by 3 is $3.3333333. Sometimes we want to change that number value to really be exactly $3.33 (round it), other times we want it to stay a repeating decimal and make it look like it only has two decimals (format). Figure 1 Home > number, Click on lower right corner 1784351413510Figure 2 ROUNDing changes the data's valueFigure SEQ Figure \* ARABIC 2 ROUNDing changes the data's value178435151130Exercise #2 – Math tabMath!Open the file “ClassWorkbookBudget.xls” and click on the Math tab.Figure 3 - The Order of Operations is important in Excel and math.Sometimes functions get long and complicated, so I put parentheses around operands to help regain control.Figure 4 - some of the symbols are different!The asterisk is a capital 8.The caret is a capital 6.Figure 5 Exercise:In a blank cell in the Math tab, try creating a complicated formula and see if you can predict the answer.Example:Answer=10^110=10^-20.01=2*2+3*4/210=2*(2+3)*4/220=(2*2+(3*4))/28=100(1+.05/12)^(12*5)128.3359Exercise #3 – Wedding TabCreate first BudgetClick on the Wedding tab.Directions:Format Column C as a percent with 2 digits. Home > Number > %Calculate Column D as (overall budget * percent of budget). Use the absolute address of $J$4.Enter your own data in Column E, some numbers higher or lower than Column D.Format Columns D, E, and F as Currency, with 2 digits, and make them show as red if negative.Calculate Column G: =(Actual cost / Budgeted Amount)Sum up Columns C through G in Row 14, using AutoSumright170815Extra Credit:Select cell J4. Formulas > Defined Names> Define Name. Give this field a name, like Budget. Now use that name instead of $J$4 in the Column D calculations.If we go over budget, make the cell red. Select cells G3:G14. Home > Styles > Conditional Formatting > New Rule > Format only cells that contain. Change Edit Formatting Rule dialog box to look like the example. Press EnterThree types of address:Relative address (default) A Relative Reference is one that when copied from one position to another will adjust the formula?cell address to suit the position it is in. Ex: =A1+A2 becomes =B1+B2, =C1+C2, etc.Absolute address The cell reference stays the same if you copy or move the cell to any other cell. This is done by anchoring the row and column, so it does not change when copied or moved. Ex: =A1+$A$2, then =B1+$A$2, =C1+$A$2.Mixed address You can choose to anchor either the row or the column when you copy or move the cell, so that one changes and the other does not.?Defined Name - Bonus!This is a big topic, but today we’ll use Names to represent a constant value similar to an absolute address. For more information, see: #4 - on Functions TabUse Insert Function dialog box to search for a Function ?Select a CellClick on Insert Function button on Formulas Tab (far left side).Enter a search term, such as “loan repayment” to get a list of possible Functions.Click on a function (try PMT) to display Function Arguments dialog box. ?Move it so it isn’t covering your data. ?The Dialog box will walk you through which data needs to be fed to the PMT Function. ?Select cell or range of cells to fill in the Values of the arguments, the formula result will appear in the lower left corner of the dialog box. ?Arguments within brackets are optional.Error Messages:#DIV/0!The denominator is 0#NAME?Range Name doesn’t exist in a worksheet. ?Check for a typo.#NULL!When you enter a space instead of a comma to separate cell references#NUM!Wrong type of argument in a function, or a number is too big or small#REF!Invalid cell reference, happens with clumsy cut & pasting.#VALUE!Wrong type of argument in a function, or if you do math on text#####Not really an error message - the column is too narrow to display the field. ?Adjust column width to display cell. ?Exercise #5 - on Networth tabCreate a Net Worth statement. ?Open the Networth tab.Enter (pretend) data in C9:C15, and C20:C26, format the data as Currency.SUM the data in C16, and in C27. Extend the formulas for the other months using AutoFill.Optional: select the fields in Net Worth (row 29) and make a chartExercise #6 - on Interest tabAll about Financial functions. ?We know that A+B=C, and C=A+B, C-A=B, C-B=A. The financial functions of PMT, RATE, NPER, PV, FV follow that same logic. If we know the starting amount of a loan, the rate of interest, how many payments there are, we can use the function PMT to find out the monthly payment.Open the Interest tab.Follow the examples and directions to work through exercises. Each function is demonstrated on the left side, exercises on the right. Remember the Interest Answer tab has the exercises worked out.Use templates to learn how to do new thingsUse <CTRL `> (the accent grave) or Formulas > Formula Auditing > Show Formulas to display formulasUnlock spread sheets to “see” what’s underneath: Review > Changes > Unlock.Many templates use the NameBox which is right above the cell A1. Use the drop-down arrow to see the list of names. Click on the desired name and it will bring your cursor to the field.Neat Formatting tricks to make it easier to read and maintain your worksheets.Wrap TextHome > Alignment > Wrap Text - Have column headings split into multiple lines with the cell. Great for narrower column headings. Merge & CenterHome > Alignment > Merge & Select - Select two or more cells. Click Merge & Center. Data in the upper left cell will appear to be centered in one big cell. Great for report headings.BordersHome > Font > Border (looks like a little window) > drop down arrow. This will give many choices, including Thick Outside Border.Conditional FormattingHome > Styles > Conditional Formatting identify cells by their value by color or an icon set. Select column of cells to be evaluated, click on the Conditional Formatting button, then “New Rule”, finally “Use a formula to determine which cells to format”. Ex: “=H3>G3”. If this condition is true, it will fill the cell with a selected color.ThemesPage Layout > Themes > Themes – Select one collection of colors to apply to the whole worksheet.Books at SPL: The total money makeover: a proven plan for financial fitness? /?Dave Ramsey 332.024r both audiobook and hardcover.The little book that beats the market?/ Joel GreenblattGreenblatt, Joel. 332.63228 G?The one-page financial plan : a simple way to be smart about your money by Carl Richards332.024RThe index card: why personal finance doesn't have to be complicated By Helaine Olen and Harold Pollack.332.024oHow to make your money last: the indispensable retirement guide?/?Jane Bryant Quinn332.024014 QDatabases at the library:Morningstar Inc - Research thousands of companies and mutual funds.“Value Line remains wholly dedicated to providing independent, unbiased research and opinion that ambitious investors can rely on to act decisively.” – from its website. Tracks 1,700 publicly traded stocks in over 99 industries.Excellent links for budgeting and financial advice. Calculators to help. retirement calculators ask you to enter your current savings and earnings and return a recommended goal range for your retirement. A visual explanation between the two types of IRAs. Learn all about money. In English and Spanish. Learn all about investing from the SEC. In English and Spanish. Money tips from the Australian government. Great credit card debt calculators - one month free trial, $45 a year. - free budget maker, hooks into your financial and credit accounts.Help with math and finance check your math when starting to learn Excel functions. videos about personal finance, investing. From the non-profit with the aim of providing a “free, world-class education for anyone, anywhere”. Learn financial investing using the sources that Warren Buffet uses. Very manageable breakdown of important concepts such as “What is a balance sheet”. with Excel This site has many interesting budgeting templates. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download