where n=5 because each week contains 5 rows of data. Calculating ranges The OFFSET function returns a reference to a range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns. In the example shown, we use OFFSET inside of SUM like this: The starting reference is C5, since this is the first value in the data....
When you create a chart in Excel, you’re plotting numeric data organized into one or more “data series”. A data series is just a fancy name for a collection of related numbers in the same row, or the same column. For example, this data shows yearly sales of shorts, sandals, t-shirts, and hoodies for an online surf shop. If I create a column chart with the default options, we get a chart with three data series, one for each year....
If you don’t know how to do this already, you may scratch your head and look for options. Unfortunately, it’s not as straightforward as you want it to be. There is no key on the keyboard to do this, or any direct option to do this using a click of a button. Don’t worry! There are plenty of ways to insert or type a degree symbol in Excel. Inserting Degree Symbol in Excel In this tutorial, I will show you these easy ways to do it (including a keyboard shortcut)....
You will often need to scroll down or to the right to view a particular record or field in a large dataset. But when you scroll too far, you will lose track of the row and column headings! When working with large datasets, you can use the Freeze Panes feature to ensure the top rows or first few columns will remain visible no matter where you scroll in your sheet....
Sometimes, it’s easy to get rid of the extra flab and keep the excel file size in check, and sometimes, it isn’t. And since Excel is not a web-based tool, most of us work and collaborate by sending Excel files via email. And a large Excel file that weighs 50 MB can be hard to share with your colleagues/clients. In my last job, we used Lotus Notes email client, which is snail-slow, and sending heavy Excel files over that was torture....
It’s so common that almost everyone knows the keyboard shortcuts to copy Ctrl + C and to paste Ctrl + V. When using this in Excel, it will copy everything including values, formulas, formatting, comments/notes, and data validation. This can be frustrating as sometimes you’ll only want the values to copy and not any of the other stuff in the cells. In this post, you’ll learn 8 ways to copy and paste only the values from your Excel data....
This Call Center Performance Dashboard enables the user to analyze the performance on a weekly basis and track key call center metrics. Call Center Performance Dashboard Here’s a how the dashboard looks: Click here to download the Call Center Performance Dashboard Here are the metrics that you can track using this dashboard: Call center level data: This includes total call, average answer speed, abandon rate, average calls/minute, overall satisfaction score (chart), SLA limits....
Convert negative numbers in place If you only need to convert negative numbers once, you can convert in-place with Paste Special: The video on this page shows this technique and many other paste special shortcuts. 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....
This blog post has a downloadable file (at the end) that has an Excel Formula Crossword in it. Go ahead and test your formula knowledge. Since I am a nice guy, I did not put a timer in it. Solve it at your own pace, and I am sure you would also discover some cool excel features that I have used to create this. Have Fun!! Want to Prep: Refresh Excel Formula basics before jumping into the crossword?...
Where data is the named range B5:B15. COUNTIF function One way to count individual errors is with the COUNTIF function like this: This is an odd syntax since technically errors are not text values. But COUNTIF is in a group of eight functions that have some quirks, and this is one of them. During calculation, COUNTIF is able to resolve the text into the given error and return a count of that error....
The result is 8, since there are eight cells in the range B5:B15 that contain numeric values. COUNT function The COUNT function counts the number of cells in a range that contain numeric values. In this example, we simply need to give COUNT the range B5:B15: The COUNT function is fully automatic, so there is nothing to configure. The result is 8, since there are eight cells in the range B5:B15 that contain numeric values....
Date configuration By default, ACCRINT will calculate accrued interest from the issue date. If the settlement date is in the first period, this works. However, if the settlement date is not in the first period, you probably don’t want total accrued interest from the issue date but rather accrued interest from the last interest date (previous coupon date). As a workaround, based on the article here: set issue date to the previous coupon date set first_interest date to the previous coupon date...
The purpose of the AND function is to evaluate more than one logical test at the same time and return TRUE only if all results are TRUE. For example, if A1 contains the number 50, then: The AND function will evaluate all values supplied and return TRUE only if all values evaluate to TRUE. If any value evaluates to FALSE, the AND function will return FALSE. Note: Excel will evaluate any number except zero (0) as TRUE....
Note: Excel won’t help you fill out the arguments for DATEDIF like other functions, but it will work when configured correctly. Time units The DATEDIF function can calculate the time between a start_date and an end_date in years, months, or days. The time unit is specified with the unit argument, which is supplied as text. The table below summarizes available unit values and the result for each. Time units can be given in upper or lower case (i....
where pd = total depreciation in all prior periods. The factor argument is optional and defaults to 2, which specifies the double-declining balance method. You can change factor to another value to influence the rate of depreciation. This is why DDB is sometimes defined as “double-declining method” or “other method”. In the example shown, the formula in D7 copied down, is: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of cells with headers that match those in database. Using the example above, you can get the variance of heights for the group “Fox” with either of these formulas: The variance for the entire data set, shown in F5, is calculated with the VAR....
Example In the example shown the formula in D4 is: As this formula is copied down column D, it changes as follows: At each new row, the range is expanded to include one new row. As a result, the SUM function calculates an accurate running total. 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....
For example, a test score greater than or equal to 80% of all test scores is said to be at the 80th percentile. In this case PERCENTRANK.INC will assign a rank of .80 to the score. In the example shown, the formula in C5 is: where “data” is the named range C5:C12. Note: The PERCENTRANK.INC function replaces PERCENTRANK which is now classified as a “compatibility function”. Interpolation When x does not exist within the array, the function interpolates a value between data points....
When entering a formula, you can can click the Select All button to enter a reference an all cells in a worksheet. For example, if you create a formula based on the COUNTA function, and click Select All in Sheet2 when entering the first argument, you’ll get: The reference Sheet2!1:1048576 is a range that includes every row in Sheet2, automatically entered by Excel when you click Select All. Author...
Note: In Excel 365, the original “comment” was renamed to “note” and a new “threaded comment” was added to allow back and forth comments by reviewers. 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.
