If the function you want to use exists in Excel VBA, use that. It will be faster and cleaner, and work in all Microsoft Office programs, not only in Excel. Here is a full list of Excel VBA Functions. If the function you want is not there, but exists in the Worksheet, you may be…
In some cases, VBA does not have a specific function, but the Excel Worksheet does. This Excel Visual Basic (VBA) code example demonstrates: How to call that Worksheet Function from a Macro. In this particular example, how to call the Worksheet Function =PROPER() from VBA. The function to downcase parts of surnames that normally appear…
When you want to look up data in Excel, you can choose from various functions: LOOKUP (in two forms), VLOOKUP, HLOOKUP, and the combination of INDEX(MATCH). Which should you use? Here’s a flowchart to help guide you: The first and most important question is, “do you want an exact match or an approximate match?” If…
For some lookups, the INDEX and MATCH functions used together may be the solution. Together, they are more robust and versatile than VLOOKUP, HLOOKUP, and LOOKUP. MATCH returns the position of the matched item in a one-dimensional list. It can do an exact or approximate match. MATCH(lookup_value, lookup_array, 0) does an exact match. INDEX returns…
The LOOKUP Function comes in two forms, the Vector form and the Array form. The former is by far the most useful and distinctive. Both forms search for an approximate match in an ordered range, and then return a value. The Vector form uses one-dimensional ranges (one or two of them). The Array form uses…
The VLOOKUP function in Microsoft Excel searches for a value down the leftmost column of a range. It then returns a value in the same row from a column number you specify in the range. You can find an exact match or the nearest match. Use VLOOKUP when your comparison values are in a column…
The HLOOKUP function in Microsoft Excel searches for a value in the top row of a range. It then returns a value in the same column from a row number you specify in the range. You can tell it to find an exact match or the nearest match. Use HLOOKUP when your comparison values are…
Do you need a random number? Or lots of them? Then RAND and RANDBETWEEN are your functions. Both are “volatile” functions: They produce a new random number each time the worksheet recalculates. Don’t want that? Then Copy and Paste Values is your friend. Before we compare them, let’s make sure we understand both. What is…
Date and time: Returns the last day of the month a specified number of months after a given date. The EOMONTH Function returns the date serial number for the last day of the month specified. That can be an indicated number of months before or after a specified date (the start_date). Use it to calculate…
EDATE is a Date function. It returns the date a specified number of months after a given date. The EDATE Function returns the date serial number that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same…
SUM is the best-known formula in Excel. It is versatile, easy to use and even has its own icon on the ribbon. However, there are times when SUM can lead you astray. Take for example where you need SUMs of other SUM formulas. This can happen in any sort of accounting with subtotals. You may…
What are Data Tables for? I’m glad you asked. Data Tables are a subset of “What if” analysis. You can try different data inputs and see the results in a Data Table. Data Tables in Excel are on the Data ribbon, in the Forecast group, in the What-if Analysis dropdown list. Goal Seek and Scenario…