Let’s take a look. The SUMIF function sums cells that satisfy a single condition that you supply. It takes three arguments: range, criteria, and sum range. Note that sum range is optional. If you don’t supply a sum range, SUMIF will sum the cells in range instead. For example, if I want to sum the cells in this range that contain the number 15, I enter B7:B12 for the range, and 15 for the criteria....
where points (B5:B25), age (C4:I4), and data (C5:I25) are named ranges. The formula returns is 89 points, based on an age of 28 in L5, and a lift of 295 in L6. Notes: this is an advanced formula. For step-by-step introduction to INDEX and MATCH, see this article. This is also an array formula that must be entered with control + shift + enter in Legacy Excel. In Excel 365, the formula does not need special handling....
If you have seen Matrix, there is no way you wouldn’t remember the falling code sequence. I thought of creating this in Excel, but someone beat me to it. It has already been created and is available here. Nitin Mehta, who created this, used a couple of Excel functions, conditional formatting, and a scroll bar to create this effect. I have modified it to remove the scroll bar and have used a VBA code instead....
Pivot Table Fields In the pivot table shown, there are four fields in use: Date, Location, Sales, and Weekday. Date is a Row field, Location is a Column field, Sales is a Value field, and Weekday (the helper column) is a Filter field, as seen below. The filter is set to include Mondays only. Helper Formula The formula used in E5, copied down, is: This formula uses the TEXT function and a custom number format to display an abbreviated day of week....
By convention, the X axis represents arbitrary values that do not depend on another variable, referred to as the independent variable. Y values are placed on the vertical axis, and represent the dependent variable. Pros Can show the relationship of one variable to another Visual display of correlation Ideal for many types of scientific data Cons Not as well understood as many other chart types Suitable only for data where correlation is expected...
This formula assigns a grade to the score in cell E7 based on the table seen in B6:C10, but the table used for the lookup is hard-coded into the formula. Normally, the second argument for VLOOKUP is provided as a range like B6:C10: When the formula is evaluated, the reference B6:C10 is converted internally to two-dimensional array like this: Each comma indicates a column, and each semi-colon indicates a row....
Excel provides a dedicated dialog box to access special groups of cells, called “Go To Special”. To access this dialog with the keyboard, type Control + G, then click the Special button (or use Alt + S) on Windows. There you’ll find a large list of options. As with the Paste Special dialog, on Windows you can use the underlined letters to select different options. So, let’s go through these options....
Let’s take a look. To enter the current date in Excel, use the shortcut control-semicolon. To enter the current time, use the shortcut control-shift-semicolon. If you want to enter both the current date and the current time, type control-semicolon, a space, then type control-shift-semicolon. 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....
Notice data is sorted in ascending order (A-Z) by default. With this formula in F5, the SORT function outputs the sorted array in F5:H14. Ascending vs. Descending Data is sorted in ascending order (A-Z) by default. This behavior is controlled by an optional third argument, sort_order. The default value for sort_order is 1, so both formulas below return the same result as shown in the example above: To sort in descending (Z-A) order, set sort_order to -1....
where “data” is the named range B5:B14 In the example shown, “data” is the named range B5:B14. In this example, the main challenge is to increment a value for nth. This is done by using an expanding range inside the ROWS function: As the formula is copied down the table, the range expands and the number or rows increases, with supplies an incrementing value. Sort numbers in descending order To sort numbers in descending order, simply replace the SMALL function with the LARGE function: Like SMALL, the LARGE function extracts an “nth” value....
Pros Simple presentation can be red at a glance Can show part to whole changes over time Cons Generally harder to add data labels since there is less white space available Area charts can imply more data than actually available Author 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....
where data is the named range B5:G14, and headers is the named range B4:G4. The formula sums columns where headers begin with “A” and returns 201. Note: In the current version of Excel you can also use the FILTER function, as explained below. To apply a filter by matching column headers – columns with headers that begin with “A” – we use the LEFT function like this: This expression returns TRUE if a column header begins with “a”, and FALSE if not....
Of course you do! If you want to create interactive dashboards in Excel and visually explore your data, then you need to start using slicers and timelines. They’ll really take your workbooks to the next level… and they’re easy! In this post, we’re going to learn everything there is to know about slicers and timelines in Microsoft Excel. There’s a lot to cover, so let’s get started. What Is A Slicer In Excel?...
In this video we’ll look how to avoid and handle that situation. Here we have a VLOOKUP example we’ve looked at previously which uses VLOOKUP to retrieve employee information based on an ID. When you’re using VLOOKUP this way you’ll probably run into situations where VLOOKUP can’t find the value you’re trying to look up. The first thing to check is your lookup table. Make sure the table is properly defined and the lookup value is in the left-most column....
Just like any object in VBA, worksheets have different properties and methods associated with it that you can use while automating your work with VBA in Excel. In this tutorial, I will cover ‘Worksheets’ in detail and also show you some practical examples. So let’s get started. If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training. Difference between Worksheets and Sheets in VBA In VBA, you have two collections that can be a bit confusing at times....
Something, as shown below, could then have been possible: Until the time it is not made available by the Microsoft Office team, this tip can help you link the maximum value for a scroll bar to a cell. Adjust Scroll Bar Maximum Value in Excel Note that since the file has a macro, you need to save the file in either .xls or .xlsm format. Once you are done setting this up, and IF the scroll bar is not working, follow these steps:...
where split_table is the named range G7:I11 The commission amount is split between the agent and broker, starting with a 50/50 split, and changing at fixed intervals as shown in the named range split_table (G7:I11). The commission amount is calculated in column C with this formula: The Agent and Broker amounts in columns D and E are calculated like this: Notice these formulas are identical except for the column index....
Note: conditional formatting rules are evaluated relative to the upper left cell in the selection at the time the rule is created, in this case D5. Calculating variance in days The variances in column E are calculated by subtracting the original date from the current date with this formula: The result is the difference in days between the original date and the current date. A positive difference represents a late date (i....
The trick is to first convert the Excel time to a decimal time by multiplying by 24. Then you can multiply by the hourly rate: Note: technically, the parentheses in the formula above are not needed and added for clarity only. Formatting time durations By default, Excel may display time, even time that represents a duration, using AM/PM. For example, if you have a calculated time of 6 hours, Excel may display this as 6:00 AM....
COUNTIF returns 2, since there are two cells in C5:C16 with numbers greater than 90. All test scores are in the range C5:C16 and we want to count scores greater than 90, so we configure COUNTIF like this: COUNTIF returns 2, since there are two scores in C5:C16 that are greater than 90. Notice that criteria is given as a text value in double quotes (""). This is a requirement of the COUNTIF function which is in a group of eight functions that use a special syntax for criteria....
