When to use Excel SUMIFS Function SUMIFS function can be used when you want to add the values in a range if multiple specified criteria are met. What it Returns It returns a number that represents the sum of all the numbers for which the specified criteria are met. Syntax =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) Input Arguments range – the range of cells against which the criteria is evaluated....
INDEX function | MATCH function | INDEX and MATCH | 2-way lookup | Left lookup | Case-sensitive | Closest match | Multiple criteria | More examples The INDEX Function The INDEX function in Excel is fantastically flexible and powerful, and you’ll find it in a huge number of Excel formulas, especially advanced formulas. But what does INDEX actually do? In a nutshell, INDEX retrieves the value at a given location in a range....
Let’s take a look. Here we have one table that lists sales by salesperson, and another that shows the commission that should be earned based on the sales amount. Let’s add a VLOOKUP formula in column D to calculate the appropriate commission rate based on the sales figure shown in column C. As usual, I’ll start off by naming the range for the lookup table. I’ll call it “commission_table”. This will make our VLOOKUP formula a lot easier to read and copy....
This formula combines the two ranges provided (B5:C12 and E5:F10), by appending the second range to the right of the first range. The result is returned as an array of values that spill into H5:K12. The third argument provides a default value to use when the formula runs into errors combining ranges, for example when ranges have different column or row counts. In the example below, the formula in cell C5 is: This is a custom function created with LAMBDA, based on several Excel functions, including INDEX, SEQUENCE, IFERROR, ROWS, COLUMNS, and MAX....
Note this formula is an array formula and must be entered with control + shift + enter. This formula also uses three named ranges: location = B5:B13, amount = D5:D13, date = C5:C13 where row_num is worked out with the MATCH function and some boolean logic: In this snippet, the location in F5 is compared with all locations, and the date in G4 is compared with all dates. The result in each case is an array of TRUE and FALSE values....
Pie charts should be avoided when there are many categories, or when categories do not total 100%. The human eye has trouble comparing the relative size of slices in a pie chart, so pie charts should also be avoided when slices are similar, unless similarity is the point. Pros Simple, compact presentation Can be read “at a glance” with limited categories Excel can calculate % values automatically Cons Difficult to compare relative size of slices Become cluttered and dense as categories are added Limited to part-to-whole data Poor at showing change over time...
The data used for this pie chart is below: Note data does not include percentage breakdown — this is handled directly in the chart. How to make this chart 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 data consists of Temperature readings taken in a greenhouse over a period of days. We have Date, Time, temperature in Celsius, and temperature in Fahrenheit. So, first, how many readings do we have? You can see that we have over 9000 readings total. How many reading per day is this? If I add Date as a row label, we get a breakdown by day. And you can see that on most days, we have 720 readings....
The result is a list of 12 random numbers greater than 10000, in multiples of 10. By design, the numbers are unique and contain no duplicates. Note: RANDARRAY and RAND are volatile functions and will recalculate with each worksheet change. Background study New dynamic array functions in Excel - 3 min video How to perform a random sort - 3 min video RANDARRAY option The RANDARRAY function makes it easy to generate a list of random integers....
The result in column D is the date only from the timestamps in column B. The time in the original timestamps is discarded. Note: This example requires valid dates. If you have dates in Excel that don’t seem to be dates, try formatting the cells with the General format. If the date is really a date, you’ll see a number. If the date is being treated as text in Excel, nothing will change....
The text argument comes B5, and 1 is specified for the number of characters to extract. With the string “ABCD” in B5, the output from MID is an array that looks like this: This array is fed into the TEXTJOIN function as the text1 argument, with delimiter set to an empty string (""), and ignore blank set to TRUE (entered as 1): The TEXTJOIN function concatenates each element in the array together, ignoring blanks, and returns the final result, “DCBA”...
With this setup, VLOOKUP finds the option associated with a cost of 3000, and returns “C”. Note: this is a more advanced topic. If you are just getting started with VLOOKUP, start here. Introduction A key limitation of VLOOKUP is it can only lookup values to the right. In other words, the column with lookup values must be to the left of the values you want to retrieve with VLOOKUP....
So, to create sequential row numbers beginning with 1, we subtract 4: This formula will continue to work as long as rows are not added or deleted above the first row of data. If rows are added or deleted above the data, the hardcoded offset value 4 will need to be adjusted as needed. Row numbers in a Table If we convert the data to a proper Excel Table we can use a more robust formula....
Before we start, I want to show you an option related to cursor movement in Excel. On Windows, press Alt + F, then T for Options, and A for Advanced. On a Mac, type command + comma to go to Preference, then click Edit. When you press Return, the cursor normally moves down, but note that you can specify another direction if you like, and you can even turn off cursor movement altogether, if you want the cursor to stay in the same place after pressing enter....
When creating a drop-down list in Excel, it takes a range of cells as input and shows it as a list. There is not much you can do with how your list looks in the drop down. For example, you can not give a background color or change font color, font type, font size, etc. Despite these limitations, there are a couple of things you can do in a drop down list....
which returns the surface area of a cylinder with the radius given in column B and the height given in column C. Units are indicated generically with “u”, and the resulting volume is units squared (u2). In essence, this formula first calculates the area of the side of the cylinder, based on the circumference of the circle times the height of the cylinder, then adds two times the area of circle to account for the ends of the cylinder....
Whenever you enter data in an Excel worksheet, Excel checks the data type you’ve entered and classifies it according to four basic data types: Numbers Dates and times Boolean values Text Excel figures out the data type based on formatting and other information it uses to guess at your intention. Text is the category that Excel uses when Excel isn’t able to classify content as one of the other data types....
Let’s take a look. First and foremost is the worksheet. Each Excel workbook can have an unlimited number of worksheets. Worksheets appear as tabs at the bottom of an Excel workbook window. The worksheet is the main work area in Excel. Worksheets are made up of cells, displayed in a grid created by the intersection of rows and columns. At the bottom and right edges of worksheets, are scroll bars....
You can also use data validation functionality to create an Excel drop down list (which is definitely one of the coolest and most powerful features in Excel) Data Validation in Excel can be accessed through the Data tab in the Ribbon. In most cases, there are three situations where you would want to use Data Validation in Excel: When you want to restrict data entry to certain numbers/text/dates. Data that does not meet the validation criteria is not allowed....
which calculates the area of a triangle given height from column B and base from column C. where b represents the base of the triangle, and h represents the height, measured at right angles to the base. In Excel, the same formula can be represented like this: So, for example, to calculate the area of a triangle with a base of 4 and a height of 3: In the example shown, the goal is to calculate the area for eleven triangles with base given in column B and height given in column C....
