Infs

Scenarios

A Scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios and then switch between these scenarios to view the different results. If several people have specific information that you want to use in scenarios, you can collect the information in separate workbooks, and then merge the scenarios from the different workbooks into one. After you have all the scenarios you need, you can create a scenario summary report that incorporates information from all the scenarios. Scenarios are managed with the Scenario Manager wizard from the What-If Analysis group on the Data tab.

If you know IPMT &PPMT how do you find pmt

Add PPMT and IPMT to get PMT

Pivot tables and pivot charts

After your source data is prepared, you can create a pivot table. First, see which pivot table layouts are suggested by Excel. Select any cell in the source data table. On the Ribbon, click the Insert tab. In the Tables group, click Recommended PivotTables. recommended pivot tables In the Recommended PivotTables window, scroll down the list, to see the suggested layouts. Click on a layout, to see a larger view. recommended pivot tables Click on the layout that you want to use, then click OK.

Formatting

All cell content uses the same formatting by default, which can make it difficult to read a workbook with a lot of information. Basic formatting can customize the look and feel of your workbook, allowing you to draw attention to specific sections and making your content easier to view and understand. You can also apply number formatting to tell Excel exactly what type of data you're using in the workbook, such as percentages (%), currency ($), and so on

What is the range for the Source data for a pie chart

Anatomy of a Data Range The data range can contain headings, data, and a spaceholder cell. This is probs wrong

What is the range of the median formula

Assuming your categories are in cells A1:A6 and the corresponding values are in B1:B6, you might try typing the formula =MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")) in another cell and then pressing CTRL+SHIFT+ENTER. Using CTRL+SHIFT+ENTER tells Excel to treat the formula as an "array formula". In this example, that means that the IF statement returns an array of 6 values (one of each of the cells in the range $A$1:$A$6) instead of a single value. The MEDIAN function then returns the median of these values. See http://www.cpearson.com/excel/arrayformulas.aspx for a similar example using AVERAGE instead of MEDIAN.

How to calculate average median and sum on different sheets? And same sheets?

Average This is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5. =AVERAGE (number1, [number2], ...) Median The middle number of a group of numbers. Half the numbers have values that are greater than the median, and half the numbers have values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4. =MEDIAN(1,2,3,4,5) Sum is all your numbers added together = cell 1 + cell 2 On multiple sheets would look like =AVERAGE(Sheet1:Sheet5!A1:A10),

What will be entered in the by changing cells Box in the solver parameter dialogue window

By Changing Cells permits you to indicate which cells are the adjustable cells (i.e., endogenous variables). As in the Set Target Cell box, you may either type in a cell address or click on a cell in the spreadsheet. Excel handles multivariable optimization problems by allowing you to include additional cells in the By Changing Cells box. Each noncontiguous choice variable is separated by a comma. If you use the mouse technique (clicking on the cells), the comma separation is automatic.

What is entered in the by changing cell box in the goal seek dialogue window

By changing cell - the reference for the input cell that you want to adjust (B3).

What is the formula of the cost of mortgage calculation

Calculate the monthly payment. To figure out how much you must pay on the mortgage each month, use the following formula: "= -PMT(Interest Rate/Payments per Year,Total Number of Payments,Loan Amount,0)". For the provided screenshot, the formula is "-PMT(B6/B8,B9,B5,0)". If your values are slightly different, input them with the appropriate cell numbers. The reason you can put a minus sign in front of PMT is because PMT returns the amount to be deducted from the amount owed. 7 Calculate the total cost of the loan. To do this, simply multiply your "payment per period" value by your "total number of payments" value. For example, if you make 360 payments of $600.00, your total cost of the loan would be $216.000.

Conditional functions

Conditional Formula If you only want to perform a calculation when a cell contains a specific value you can create a conditional formula. Allows you to perform calculations on only those numbers that meet a certain condition. A conditional formula returns one value if a condition is True and a different value if the condition is False. You can create a conditional formula by using the IF function. This function requires three arguments.

Creating graphs and charts

Enter your data into Excel. Choose one of nine graph and chart options to make. Highlight your data and 'Insert' your desired graph. Switch the data on each axis, if necessary. Adjust your data's layout and colors. Change the size of your chart's legend and axis labels. Change the Y axis measurement options, if desired. Reorder your data, if desired. Title your graph.

What is macros and how does it work

If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes. After you create a macro, you can edit it to make minor changes to the way it works. In the Code group on the Developer tab, click Record Macro. Optionally, enter a name for the macro in the Macro name box, enter a shortcut key in the Shortcut key box, and a description in the Description box, and then click OK to start recording. Record Macro Perform the actions you want to automate, such as entering boilerplate text or filling down a column of data. On the Developer tab, click Stop Recording. Stop Recording

Goal seek

If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal.

What is entered into the value of box on the solver parameter dialogue window

If you want the objective cell to be a certain value, click Value Of, and then type the value in the box.

Cell ranges

In a spreadsheet a cell range is a collection of selected cells. This cell range is usually symmetrical (square), but can exist of separate cells just the same. A cell range can be referred to in a formula.

What is the entry into the logical test

Logical_test this must be an argument that returns a logical value, either True or False. For example you could use A1>50 to check if the value in cell "A1" is greater than 50. You can use any combination of cell references, oeprators, constants and even other functions to create the arguments. You could use SUM(A1:A1)>200

What is the entry into the NPER box for mortgage

NPER (number of periods) Required: The number of loan payments

What is entered into the set target sell box in the solver parameter dialog box

Objective The Objective cell (Target cell in earlier Excel versions) is the cell containing a formula that represents the objective, or goal, of the problem. The objective can be to maximize, minimize, or achieve some target value.

What is the entry into the PV box for mortgage

PV (present value) Required: The amount borrowed

What is the entry into the rate box for mortgage

RATE Required: Interest rate of the loan

Relative, absolute, and mixed reference

Relative cell references This is the standard type of reference. Look at the following examples: Example 1: If cell A1 contains value 2, and cell B1 contains formula =A1+2 (referring to cell A1), then the formula B1 contains value 4. If you change the value in cell A1 to 5, then the value in cell B1 automatically changes to 7. Absolute cell references Absolute cell reference always points to the same place, even if you change the position of any of those cells. In other words, if you have cell A1 which refers to the contents of cell B1 (=$B$1) and then you change the position of A1 it will still refer to cell B1. If you drag cell B1 to another location, for example, B3, then A1 will point to the new location of the same cell (=$B$3). Mixed cell references A mixed reference is a reference that refers to a specific row or column. For example, $A1 or A$1. If you want to create a mixed reference- press the F4 key on the formula bar two or three times depending on whether you want to refer to row or column. Press F4 one more time to go back to the relative cell reference.

Copying formulas

Select the cell containing the formula you want to copy. Click Home > Copy, or press Ctrl+C. Copy and Paste buttons on the Home tab On the same sheet or another sheet, click onto the cell into which you'll paste the formula. To paste the formula with its formatting, click Home > Paste or press Ctrl+V. For other formula paste options, click the arrow below Paste and choose either: Formulas Button image to paste only the formula. Values Button image to paste the value only (result of the formula).

Naming cell ranges

Select the cell(s) to be named Click in the Name box, to the left of the formula bar Type a valid one-word name for the list, e.g. FruitList. Press the Enter key.

What is entered in the set cell box In the goal seek dialog box

Set cell - the reference to the cell containing the formula (B5)

Solver

Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of cells, called decision variables or simply variable cells that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell. Put simply, you can use Solver to determine the maximum or minimum value of one cell by changing other cells. For example, you can change the amount of your projected advertising budget and see the effect on your projected profit amount.

Fv

The Excel FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate. =FV (rate, nper, pmt, [pv], [type])

Ipmt

The Excel IPMT function can be used to calculate the interest portion of a given loan payment in a given payment period. For example, you can use IPMT to get the interest amount of a payment for the first period, the last period, or any period in between. =IPMT (rate, per, nper, pv, [fv], [type])

IRR

The Excel IRR function is a financial function that returns the internal rate of return (IRR) for a series of cash flows that occur at regular intervals. =IRR (values, [guess]) Arguments

Nper

The Excel NPER function is a financial function that returns the number of periods for loan or investment. You can use the NPER function to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount. =NPER (rate, pmt, pv, [fv], [type])

Npv

The Excel NPV function is a financial function that calculates the net present value (NPV) of an investment using a discount rate and a series of future cash flows. =NPV (rate, value1, [value2], ...)

PMT

The Excel PMT function is a financial function that returns the periodic payment for a loan. You can use the NPER function to figure out payments for a loan, given the loan amount, number of periods, and interest rate. =PMT (rate, nper, pv, [fv], [type])

Ppmt

The Excel PPMT function can be used to calculate the principal portion of a given loan payment. For example, you can use PPMT to get the principal amount of a payment for the first period, the last period, or any period in between. =PPMT (rate, per, nper, pv, [fv], [type])

Pv

The Excel PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate. =PV (rate, nper, pmt, [fv], [type])

Rate

The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. The RATE function calculates by iteration. =RATE (nper, pmt, pv, [fv], [type], [guess])

What is entered in the to value box of the goal seek dialogue window

To value - the formula result you are trying to achieve (1000).

Vlookup

VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right. Purpose Lookup a value in a table by matching on the first column Return value The matched value from a table. Syntax =VLOOKUP (value, table, col_index, [range_lookup])

What is the entry into the value if true box

Value_if_true This is the value that will be returned by the IF function is the logical test evaluates to True. If you want to return a text string you must enclose in qutation marks.