Excel Fanatics  

Countif, Counta, Daverage, Dcount, Dget, Dvarp, PMT, Indirect, Vlookup, Replace, Trim, Pivot Table, Max,  Offset, Proper, Match, Compare, Today(), INT, Tranpose, MMULT, MINVERSE, Index, Goal Seek, Frequency, Date

Blogger

fotos de bebitas desnudas

bebitas follando gratis

bebitas lindas follando

videos bebitas follando

Quote of the day

"A person who is successful has simply formed the habit of doing things that unsuccessful people will not do."

bebitas brasilenas con viejos

videos gratis de bebitas haciendo el amor

bebitas desnuda

fotos de bebitas

"A man must be big enough to admit his mistakes, smart enough to profit from them, and strong enough to correct them."

"Failure can be divided into those who thought and never did and into those who did and never though."

"Failure to prepare is preparing to fail."

follando bebitas

"Luck is the residue of design."

"Never explain yourself to anyone.  Because the person who likes you doesn't need it, and the person who dislikes you won't believe it."

bebitas es

www bebitasfollando

viejos follando bebitas

cogiendo bebitas en casa pic free

"Time is like a river.  You cannot touch the same water twice, because the flow that has passed will never pass again.  Enjoy every moment of life....."

"Don't make promise when you are in joy.  Don't reply when you are angry.  Think twice....Act Wise."

"When we wake up in the morning, we have two simple choices.  Go back to sleep and dream, or wake up and chase those dreams.  Choice is yours........"

"Don't let someone become a priority in your life, when you are just an option in their life.....Relationships work best when they are balanced."

"In the Land of the Blind, the One-Eyed Man is King"

"Without problems to solve, natural growth may take place gradually, But with problems to solve, growth is accelerated"

"A good mindset leads us. A bad mindset misleads us."

"I hear, I forget. I see, I remember. I do, I understand."

"If you cannot measure it, then you cannot manage it."

"Out of clutter, find simplicity.  From discord, find harmony.  In the middle of difficulty, lies opportunities."

"To build may have to be the slow and laborious task of years.  To destroy can be the thoughtless act of a single day."

Reading Lists

videos gratis bebitas rusas

lindas bebitas follando

fotos de bebitas angeles

bebitas argentinas

"We know too much and are convinced of too little"

"A person is the sum of all the books he has read"

"Knowledge is nothing without understanding"

You see knowledge alone isn't power. If that were true, libraries would be guarded with the strongest armies in the world! But they're not. Knowledge isn't power.

Only APPLIED knowledge is power.

Welcome to my Excel Learning Universe

Enter your search terms Submit search form

Our Mission

To share my Knowledge and experience of using Excel in the Hedge Fund industry.

Millions of users create and share Excel spreadsheets every day, but few go deeply enough to learn the techniques that will make their work much easier. Yet there are many ways to take advantage of Excel’s sophisticated capabilities without spending hours on advanced study.

Due to the nature of my job in Hedge Fund industry, I have opportunities in exploring many different formulas and functions to expedite my work processes.  In many instances, formulas and function provides provide the logic that powers spreadsheets.  Managing programming logic is always a challenge, but keeping track of programming logic across multiple cells, sheets, and workbooks can be particularly difficult, especially as spreadsheets grow and are reused.

Using Excel's built-in language, VBA (Visual Basic for Applications), you can bring a lot of automating power to your spreadsheets and become more productive by delegating many time-consuming and task using Excel programming.

The intent of this webpage is to show some useful Excel Function and  VBA (Visual Basic for Applications) examples that I have picked up in the process of creating my own applications.

Hot Tips on Navigation

• Press the "Home" key.  It takes your cursor to column A of the row that you now located.
• To go to the first filled cell on the left, press "End" and then press the left arrow "<-".
• Press "Ctrl" and "Home" to return to the upper left-hand corner, cell A1.
• Press "Home" to go the left-hand column of the worksheet.
• To go to the last entry in a column, press "End", then press the down arrow
• To go to the last entry in a row, press "End" and then press the right arrow.
• Press "End" and then press the up arrow to go to the first entry at the top of a column.

History Eraser and Cleaner. Please Click Here!

Cell and Range References

• Relative :  The reference is fully relative.  When the formula is copied, the cell reference adjusts to its new location.  Example: "A1"
• Absolute : The reference is fully absolute.  When the formula is copied, the cell reference does not change.  Example: "$A$1"
• Row Absolute : The reference is partially absolute.  When the formula is copied, the column part adjusts, but the row part does not change.  Example: "A$1"
• Column Absolute :
  
  
  
  
  
  
  
  
  
  
  The reference is partially absolute.  When the formula is copied, the row part adjusts, but the column part does not change.  Example: "$A1"
• When you create a formula by pointing to cells, all cell and range references are relative.
• To change a reference to an absolute reference, you must do so manually by adding the dollar signs
• Shortcut:  When you enter a cell or range address, press "F4" key to cycle among all possible reference modes.

Registry Repair, Please Click Here!

Case Sensitive

• "UPPER"  converts the text to ALL UPPERCASE
• "LOWER" convert text to all lowercase
• "PROPER" converts the text to "proper" case (The First letter in each word is capitalized)

One Click Privacy. Please Click Here!

Functions

Inserting Functions
Excel comes with a wide array of functions that can easily be inserted into a spreadsheet. Adding a function is as easy as clicking on the "Insert Function" button on the top tool bar.

bebitas nalgonas mexicanas

bebitas tiins fotos desnudas

bebitas virgenes amateurs fotos

bebitas follandogratis

If you don't see the "Insert Function" button on your tool bar, you can easily add it by clicking:

Tools > Customize > Commands > Find and click on Insert in the left-hand scroll-down menu. Then, find "Insert Function" in the right-hand scroll-down menu and click and drag it to your tool bar.
 

Clicking on the "Insert Function" button allows you to search for a function by typing a brief description of what you want, or by category.

For example, if you click on the "Function" icon, the following window will be displayed:

Adding a particular function to your spreadsheet is then as simple as following the on-screen commands, but if you are ever in doubt, press the F1 key on your keyboard to access Excel's "Help" program.

In addition to financial functions - such as present value, future value, payment and internal rate of return - that are of use to financial professionals, Excel has many functions that are useful in cleaning up and reconciling large data sets, a task frequently encountered by many in finance. Some of these functions are:
 

EXACT - Checks whether two strings of text are precisely the same and will return either True or False. LEFT, RIGHT or MID - Returns the characters from a text string given a starting position and length, such as the left side, middle of the text string or the right side. TRIM - Will remove, with the exception of single spaces in between different words, all spaces in a text string. TRANSPOSE - This function will convert a range of cells aligned vertically to a range of cells aligned horizontally, or vice versa. "RAND( )" is a worksheet function that returns an evenly distributed random number greater than or equal to 0 and less than 1. bebitas culiando free videos gratis bebitas folladas fotos bebitas putas how much does a pella door cost with insulation porn de bebitas cojidas dormidas "INT" is a worksheet function that rounds a number down to the nearest integer. "MATCH" returns the relative position of an item in a range that matches a specified value. bebitas putas fotos gratis bebitas virgenes bebitas maduras "OFFSET" returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. "COUNTIF" returns the number of cells in a range that meet a specified criteria. "DCOUNTA" returns the number of nonblank records in a worksheet database that meets specified criteria. The above is just a very brief list of what some of the functions that Excel offers. The best way to discover what functions are available and what can be done to make spreadsheets and data manipulation more efficient is simply to play around with Excel's "Insert Function" feature or to take one of Microsoft's courses or tutorials. If you browse by the Text or Lookup & Reference categories, you will have a great head start on using Excel's features to their fullest potential.

Look-Up tables
Look-up tables are usually a part of any financial spreadsheet or model. The "Look-Up" function in Excel searches for values in a table based on a certain condition. For example, if you use on-the-run Treasury yields as a benchmark pricing for other bonds, a "Look-Up" table could be used to pull in the appropriate Treasury yield. (To help you learn this function we suggest you open up your own Excel spreadsheet and follow along).

Figure 4

In the example above, Column F contains the "Look-Up" formula in each cell, and pulls the appropriate on-the-run Treasury yield from the "Look-Up" table to the left. In this example, the "Look-Up" table runs from Cell A2 to Cell B6. The first value within the "()" of the formula is a cell reference to the value in the "Look-Up" table that is searched for. This means that the value the "Vlookup" function will search for in the "Look-Up" table is a 10-year on-the-run Treasury. The second value in the "Vlookup" function is the name of the "Look-Up" table ("Look-Up" Tables must be named prior to using the "Vlookup" function and are described below). The third value in the "Vlookup" function is the column in the "Look-Up" table that is returned (there can be multiple columns in a "Look-Up" table). Creating a "Look-Up" table is a simple, two-step process.    A "Look-Up" table must be sorted in ascending order by the first column. Using the cursor, highlight the entire data table. Click on "Data". Click on "Sort". You must give the "Look-Up" table a name. Using the cursor, highlight the entire data table. Click on "Insert". Click on "Name". Click on "Define". Key in a name Using this example to create your "Look-Up" table, sort cells A2:B6 using the instructions above. Now, when the same cells are highlighted, you can name the "Look-Up" table to "Benchmarks", which is the name included in the example above. "Look-Up" tables have many uses beyond pulling in information for securities prices. However, we note that a simple securities pricing spreadsheet, as shown in the example above, can be made even more efficient by using the features offered by most pricing services, such as Bloomberg, which allow spreadsheets to link directly to live price feeds. For example, the Treasury yields in the benchmark "Look-Up" table above could be pulled directly into the table as a live link from a pricing service. Looking Up a Value Based on Two Conditions As demonstrated above, a "Look-Up" table can be used to pull in values based on a certain condition. The user must identify that condition and provide the column in which to find the value that is to be returned. (In the example above, the condition was the name of an on-the-run Treasury bond, and the column was the second column, which contained that particular bond's yield). There is a way to use a "Look-Up" table to return values when the column to be returned is not constant. In other words, based on certain variables, you might want to return the second, third, fourth, etc., columns of a "Look-Up" table. To do this, you must create a "Look-Up" table function within a "Look-Up" table function. For example, the table below shows the value of mortgage servicing based on loan size and loan-to-value ratios (LTV). Let us call this table the "Servicing Table". What if you wanted to pull the value of servicing into a spreadsheet for multiple loans with varying sizes and LTVs? In other words, the column number of the data you want to retrieve is not constant. Figure 5 You must first create a separate "Look-Up" table that identifies the column number for a given LTV. Let's create a "Look-Up" table using the following   Figure 6 This "Look-Up" table is named "LTV" in the formula shown below. The servicing value "Look-Up" table (the original table in our example) is named "Servicing". Now you can write a formula to find serving values based on both the variable of loan size and the variable of LTV as shown below. Figure 7 As you can see from this example, the "Vlookup" function will work when using two different conditions. Keep this information handy, as we will use it in our next section. The "Exact" Statement The "Exact" statement (or function) is very useful in working with large sets of data such as securities where values within a spreadsheet vary by small amounts (for example, CUSIP numbers). You can use the "Exact" function to ensure that you are pulling in the actual value that you need, or to identify why you might not be able to find a value that you believe should be in the data set. For example, when using the "Look-Up" function as described above, you must sort the "Look-Up" table by the first column in ascending order. The "Look-Up" function then searches for values in that first column. If a value in the "Look-Up" function cannot be found in the "Look-Up" table, the "Look-Up" function will find the next closest value - this is generally not good in financial spreadsheets, as exact figures are usually required. For example, if the "Look-Up" function is searching for CUSIP number 912833WZ3, which is not found in the "Look-Up" table, but 912833WZ4 is in the "Look-Up" table, the "Look-Up" function will return the value in the specified column number for the 912833WZ4 CUSIP number. This is simply not the correct CUSIP number. To avoid pulling the closest value when the actual value is not found, use the "Exact" function as shown below. Figure 8 The "Exact" function shown above says: If the value in Cell B2 is exactly the same as a value found in the "Look-Up" table called "Benchmark", then it will return the value in column two from the table. If there is no value in the "Look-Up" table called "Benchmark" that is exactly the same as the value in Cell B2, then it will return the words "not found". Another common use of the "Exact" function is to figure out why you cannot find a value in a set of data when you "know" it is there. For example, you might be trying to reconcile two sets of data by CUSIP number. You know a certain CUSIP number is in both sets of data, but the formulas you have written aren't recognizing one of the CUSIP numbers as being the same as the other. Simply key the CUSIP number into both spreadsheets, and use the "Exact" function to let Excel tell you whether they are the same - there could be an unseen character in the data such as a space before the CUSIP number that is causing the problems, which the "Exact" function will help you identify. Figure 9 As shown above, the "Exact" function is returning a "FALSE". Upon looking closer, the two CUSIPs are different. The CUSIP in A2 ends in "zero" while the CUSIP in A6 ends in "O".     Using Arrays "Arrays" is a powerful feature in Excel that allows you to make calculations within large sets of data based on multiple conditions. For example, using "Arrays", you can sum the value of integrated oil stocks with a certain market cap within a large set of data that consists of stocks from many different industries and with many different market caps, and you can calculate the weighted average price of those stocks. Creating "Arrays" is a simple process, and the first step is to name the "Arrays".    Name the "Arrays". Using the cursor, highlight the entire set of data, including the column headers (the data must have column headers as they become the names of each "Array"). Click "Insert" on the tool bar. Click "Name". Click "Create". Mark the "Top Row" check box only and click "OK". Writing "Array" functions is also relatively simple. The examples below show "Array" functions that sum the market value of stocks within several industries by market capitalization and calculate the weighted average prices of those stocks based on the data set shown immediately below. The "Array" functions are not as complex as they might look at first glance. First, we named the "Arrays" as described above, then we simply use a combination of "If" and "Sum" statements to return values based on the conditions that we set. The weighted average calculation is simply embedded in the "Array" function. The best way to gain expertise using "Arrays" is to practice. Once you master the use of "Arrays", they can be used very effectively to create many different efficiencies. Important: The "{ }" brackets shown in the formulas are not keyed into the "Array" function, but once the "Array" function has been created, you must simultaneously hit Shift, Control, Enter on your keyboard to activate the "Array" - this creates the "{ }" brackets.   Calculating Fractions of Years Summary: Excel provides, as part of the Analysis Toolpak, a worksheet function that can use to determine what percentage of a year is represented by the difference between two dates. This tip explains how to put this function to work. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.) One of the types of data that Excel allows you to store is, of course, dates. At some point you may wish to perform some calculations with the dates in your worksheet. It is not uncommon to need to figure out the percentage of a year represented by the difference between two dates. Excel allows you to calculate this easily using the YEARFRAC worksheet function. This function is part of the Analysis ToolPak provided with Excel. To use the function, all you need to do is provide two dates and a value that specifies how Excel should calculate the fractional year: =YEARFRAC(DateOne, DateTwo, Basis)   The dates used by YEARFRAC can be either static dates, or they can be references to cells that contain dates. The Basis value ranges between 0 and 4, with 0 being the default. The following are the different meanings for the Basis: Basis   Meaning 0   US 30/360 1   Actual/actual 2   Actual/360 3   Actual/365 4   European 30/360 You should note that if the YEARFRAC function does not work on your system, it means you have not installed or enabled the Analysis ToolPak. To enable it, follow these steps: 1.      Choose Add-Ins from the Tools menu. Excel displays the Add-Ins dialog box. (In Excel 2007, click the Office button, then click Excel Options, and finally click Add-Ins.) 2.      Make sure the Analysis ToolPak option is selected. 3.      Click on OK. If you did not see an Analysis ToolPak option in step 2, it means that you did not install the option when you first installed Excel. You can rerun the Excel Setup program and choose to install the option. You must then enable the add-in, and you can use the function.

Send mail to larry@excelfanatics.com with questions or comments about this web site.
Last modified: 10/01/07