Original data Data with helper columns 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 an Excel Table in the range B5:D16, and the quarters in column E are generated by another formula explained below. Background study What is an Excel Table (3 min. video) Introduction to structured references (3 min. video) Excel Tables (overview) Calculating quarters The first step in this problem is to generate a quarter for each date in column B. In the table shown, column D is a helper column with quarter numbers calculated with a separate formula....
The result is $21,875. This is the total of amounts in column C that occur in the last 30 days, based on a current date of December 30, 2022. Excel date logic To understand the logic of the formulas explained below, the first step is to understand Excel’s date system, where dates are serial numbers beginning on January 1, 1900. January 1, 1900 is 1, January 2, 1900 is 2, and so on....
What the heck is that, and what is it doing? The double negative (sometimes called the even more nerdy “double unary”) coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. It’s used in formulas where numbers are needed for a particular math operation. That might sound pretty vague, so I’ll illustrate with the example above. Let’s say you have a list of words in a range, and you want to count how many contain more than 5 characters....
Note: this is an array formula that must be entered with Control + Shift + Enter across the entire range H5:I9, except in Excel 365. However, if a source cell is blank (empty) TRANSPOSE will output a zero. To fix that problem, this formula contains an IF function that checks first to see if a cell is blank or not. When a cell is blank, the IF function supplied an empty string ("") to transpose....
It’s going to save you time and effort if you put in the small amount of time to learn it. The best part is, it’s built right into Excel 2016 or later. It’s also the same technology that’s used in Power BI, so you’re learning two in demand data skills at the same time! If you haven’t heard of power query and the awesome things it can do, or you want to fully understand it better, then check out my Complete Guide to Power Query beforehand....
This means a lot of people are working from home now and need to use some sort of tool to collaborate with their teams. If your company is on Microsoft Office 365, then you’re likely using Microsoft Teams. In this post you’ll learn the top 25 tips and tricks for working with Microsoft Teams. Video Tutorial Allow External Guest Users in Teams Teams is an amazing tool for collaborating on your work, but by default you can only invite internal users to a team....
However, a random number generator does have a huge use when working with different scenarios on a set of data or when performing various statistical analyses. A financial model may use a stochastic simulation that is dependent on probabilities. The model may need to be run thousands of times, but with the random number generator providing the parameters of each simulation. Whatever your need for random numbers, Excel has several ways to generate them....
This post is going to show you all the ways you can use Microsoft Excel for free in a totally legitimate and legal way from Microsoft. Microsoft Excel is a spreadsheet program that is part of the Microsoft Office suite of productivity software. Excel allows users to import, manipulate and visualize data. It’s a powerful tool for data analysis and can be used to create charts, graphs, and pivot tables....
which returns the average pay per week, excluding weeks where no hours were logged. This is an array formula, but it is not necessary to enter with control + shift + enter because the SUMPRODUCT function can natively handle most array operations. Working from the inside out, we first calculate total pay for all weeks: This is an array operation that multiplies hours by rates to calculate weekly pay amounts....
where “sales” is the named range C5:C11. The core of this formula is the RANK function, which is used to generate a rank of sales values, where the highest number is ranked #1: Here, RANK uses the named range “sales” (C5:C11) for convenience. By default, RANK will assign 1 to the highest value, 2 to the second highest value, and so on. This works perfectly as long as numeric values are unique....
Next, break time is subtracted from work time to get “net work hours”. This formula uses the the MOD function to handle times that cross a day boundary (midnight). By using MOD with a divisor of 1, positive results are unchanged, but negative results (which occur when start time is greater than end time) are “flipped” to get a correct duration. For more details, see: How to calculate number of hours between two times...
Note: This example assumes that today is the issue date, so the next payment will occur in exactly six months. See note below on finding the value of a bond on any date. The value of an asset is the present value of its cash flows. In this example we use the PV function to calculate the present value of the 6 equal payments plus the $1000 repayment that occurs when the bond reaches maturity....
When a ticket is open, the result is the number of days since the ticket was opened using the “Opened” date in column C. When a ticket is closed, the result is the number of days between the “Opened” and “Closed” date in column D. Note: this example was authored on April 18, 2021, so the calculations seen in the screen above are relative to that date. The core operation of this formula is controlled by the IF function....
which returns a value of 1. Because each hour can be represented as 1/24, you can convert an Excel time into decimal hours by multiplying the value by 24. For example, with the time value 6:00 cell A1, you can visualize the conversion like this: Format result as number When you multiply a time value by 24, Excel may automatically format the result using a time format like h:mm, which will display the value incorrectly....
When you copy and paste formulas in Excel, it automatically adjusts the cell references. For example, suppose I have the formula =A1+A2 in cell B1. When I copy the cell B1 and paste it in B2, the formula automatically becomes =A2+A3. This happens as Excel automatically adjusts the references to make sure the rows and columns now refer to the adjusted rows and columns. Note: This adjustment happens when you’re using relative references or mixed references....
And yet, a huge amount of time is wasted every day by intelligent people trying to solve typical business problems with Excel formulas. It’s not their fault. Excel is complicated, and almost no one gets good training on formulas. Core Formula is designed to give you the formula training you should have had to begin with. What’s in the course? Core Formula covers a lot of ground. Using bite-sized videos in razor-sharp HD format, Core Formula teaches you how to use formulas for text, dates and times, VLOOKUP, INDEX & MATCH, summing and counting, nested IFs, dynamic ranges, troubleshooting, and a wide variety of techniques for referencing cells....
where dates is the named range B5:B15. In this case, we are giving YEAR and array of dates in the named range dates: Because dates contains 11 cells, we get back 11 results in an array like this: Each date is compared to the year in column D, which creates a new array of TRUE FALSE values: In this array, TRUE corresponds to dates in the year 2017, and FALSE corresponds to dates in different years....
Same month as today If you need to test a date to see if has the same month as the current date (today), you can use this formula: Same month and year To test that a date is the same month and year is another date, you can use this clever formula proposed by reader Eric Kalin: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
