Consulting | Excel Examples

Advanced Formulas Tutorial

Using a List Box to Change Data Displayed on a Worksheet
Learn to use a List Box to update values displayed on your worksheet. Use the OFFSET function to return values from a table. Select an item in the List Box and see values for that item appear on a worksheet.

IRR, XIRR, NPV, XNPV Functions
You can use IRR and NPV to calculate internal rate of return and net present value for even cash flows. For uneven cash flows, use the XIRR and XNPV functions found in the Analysis Pak Add-In.

Maximum Sustainable Growth
You can use Excel's iteration feature to solve a circular reference. In a maximum sustainable growth model Excel can iterate to solve the circular formulas where maximum sales growth depends on profit generated and profit depends on sales. If you set the iteration parameters when the workbook opens with a Visual Basic Macro, Excel will iterate to find the maxium sustainable growth. You can use the Tools, Auditing features to trace the circular references.

Four Methods of Adding Totals to a List
You can add totals to a list of data on a worksheet in many ways. You can use an AutoFilter and sort and subtotal the data. Excel provides array formulas that can be used to process an entire list of data. Database functions return totals according to a criteria range. A Pivot Table automatically creates totals.



[HRule Image]

Last modified: September 16, 2010