Do you need to find out how many working days –excluding weekends and holidays– there are in a given period? Or find out on what date a given number of work days will end? You need NETWORKDAYS and WORKDAY. The NETWORKDAYS function in Microsoft Excel returns the number of whole working days inclusive from a…
The Excel functions INT, TRUNC, and ROUND all do similar things. What’s the difference? INT Truncates a number down to the nearest integer TRUNC Truncates a number towards zero. It truncates to an integer or an optional number of decimals. INT and TRUNC produce the same result with positive numbers. They differ in the way…
Nothing to do with Cowboys really (nor Monsanto either), just couldn’t resist the pun. ROUND(number, num_digits) rounds to the nearest digit (up or down as required)ROUNDUP(number, num_digits) always rounds up* to the nearest digitROUNDDOWN(number, num_digits) always rounds down* to the nearest digit The functions take the following arguments: Number (required): Any real number that you…
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…