ROUNDUP takes two arguments, number and num_digits. Number is the number to be rounded, and num_digits is the place at which number should be rounded. When num_digits is greater than zero, the ROUNDUP function rounds on the right side of the decimal point. When num_digits is less or equal to zero, the ROUNDUP function rounds on the left side of the decimal point. Use zero (0) for num_digits to round to the nearest integer....
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
The SORT function takes four arguments: array, sort_index, sort_order, and by_col. The first argument, array, is the range or array to be sorted. This is the only required argument. By default, the SORT function will sort values in ascending order using the first column in array. Use the optional arguments sort_index and sort_order to control which column to sort by, and the order to sort by (ascending or descending). Sort_index should be a number corresponding to the column (or row) used for sorting....
The TOCOL function takes three arguments: array, ignore, and scan_by_column. Array is the only required argument and represents the array or range to be transformed. The ignore argument controls what values TOCOL will optionally ignore. The options for ignore are as follows: The scan_by_column argument is a boolean value that controls how TOCOL reads values from the source array. By default, scan_by_column is FALSE and TOCOL will read values “by row” from left to right....
A lot of Excel functionalities are also available to be used in VBA – and the Autofilter method is one such functionality. If you have a dataset and you want to filter it using a criterion, you can easily do it using the Filter option in the Data ribbon. And if you want a more advanced version of it, there is an advanced filter in Excel as well. Then Why Even Use the AutoFilter in VBA?...
What it Returns It returns a number between 1 and 7 that represents the corresponding day of the week. Syntax =WEEKDAY(serial_number, [return_type]) Input Arguments serial_number: It is the serial number of the date for which you want to find the weekday number.[return_type]: (Optional) You can choose the week’s start day and the value it should return. By default, the week starts on a Sunday (which means that if the day of the specified date is Sunday, then it returns 1....
where data (B5:D15), color (C5:C15), and list (J5:J7) are named ranges. MATCH is configured to look for each color in C5:C15 inside the smaller range J5:J7. The MATCH function returns an array like this: Notice numbers correspond to the position of “found” colors (either “red”, “blue”, or “black”), and errors correspond to rows where a target color was not found. To force a result of TRUE or FALSE, this array goes into the ISNUMBER function, which returns: The array above is delivered to the FILTER function as the include argument, and FILTER returns only rows that correspond to a TRUE value....
One such example is to find the closest match of a lookup value in a dataset in Excel. There are a couple of useful lookup functions in Excel (such as VLOOKUP & INDEX MATCH), which can find the closest match in a few simple cases (as I will show with examples below). But the best part is that you can combine these lookup functions with other Excel functions to do a lot more (including finding the closest match of a lookup value in an unsorted list)....
The result in column D is the fiscal year for each date, based on a fiscal year start in July. On the left, the YEAR function first returns the year from the date in B5: To adjust this value to return the fiscal year for a July start, the following boolean expression is added to the year value: Here, the MONTH function returns the month from the date in B5 (1), and this result is compared to the start month in C5 (7)....
Where “names” is the named range C4:C11. Note: this is an array formula and must be entered with control + shift + enter. Working from the inside out, this part of the formula will generate a relative set of row numbers: The result of the above expression is an array of numbers like this: Notice we get 8 numbers, corresponding to the 8 rows in the table. See this page for details on how this part of the formula works....
In the example shown, the formula in cell B5 is: Same date in previous year To get the same date in a previous month, use -12: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. Once you save the rule, you’ll see only values that are integers (whole numbers) highlighted. We use this fact to construct a simple formula that tests the result of MOD. When the result is zero (i.e. when the number is an integer) the formula returns TRUE, triggering the conditional formatting....
Here we have some sample data and, in column B, we have text values with a number at the end. What we want to do is increment these numbers using the value in column C. Now if I try to do this directly, with a formula that adds C5 to B5, I’ll get an error because the value in B is text, and Excel won’t let you add numbers and text....
How can you make one chart look like another? You have several options. One option is to copy formatting, and use Paste Special. For example, let’s say we want the chart on the right to look like the chart on the left. First, select the chart on the left and copy. Next, navigate to the home tab of the ribbon and, under the Paste menu, select Paste Special. Then choose Formats....
In some cases, you may want to create a chart that shows more than one level of axis labels. This is easy to do as long as you structure the source data in a particular way. Here we have a list of the busiest US airports. We have columns for region, city, airport code, and activity, which represents total take offs and landings. Holding down the control key, it’s a simple matter to plot activity by city in a column or bar chart....
Excel has a great built in function called SUBSTITUTE which allows you to find one bit of text within another text string and substitute it for another bit of text. Copy and paste this table into cell A1 in Excel In the above example we can use the SUBSTITUTE function to clean the text of all the percentage signs by replacing them with an empty string (“”) in the following formula....
If you ever need to insert multiple blank rows into your data, doing it manually could be very time consuming if you have a large data set. Here’s a quick way to do this by inserting a blank row into your data after every Nth record. I realize it’s a bit of cluster bomb and I might have put a bit too much in it 🙁 To accomplish our task we’re going to need to add a helper column....
