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....
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....
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....
Note: this is an array formula and myst be entered with control + shift + enter. Results in an array containing seven values: Each number in the array is the result of subtracting the “low” from the “high” in each of the seven rows of data. This array is returned to the MAX function: And MAX returns the maximum value in the array, which is 32. MIN change To return the minimum change in the data, you can substitute the MIN function for the MAX function: As before, this is an array formula and myst be entered with control + shift + enter....
If B5 or C5 or D5 is blank, the formula returns TRUE and triggers the rule. Note: conditional formatting formulas should be entered relative to the “active cell” in the selection, which is assumed to be E5 in this case. As the formula is evaluated, formula references change so that the rule is testing for blank values in the correct row for each of the 10 cells in the range: If any cell in a corresponding row in column B, C, or D is blank, OR function returns TRUE and the rule is triggered and the green fill is applied....
Note the text string is always 9 characters long, and each component is 2 digits. The core of this formula is the TIME function, which assembles a valid time using individual hour, minute, and second components. Since these values are all together in a single text string, the MID function is used to extract each component: The results are fed directly to the TIME function as arguments. The code below shows how the formula is solved in cell F5: Notice MID, as a text function, returns text instead of actual numbers....
The result is 4, since there are four cells in the range D5:D16 that contain “red”. COUNTIF function One way to solve this problem is with the COUNTIF function, which is designed to count cells in a range that meet one specific condition. COUNTIF takes two arguments: range and criteria: For this problem, range is D5:D16 and criteria is “red”. We place double quotes (“red”) because “red” is a text value....
The ARRAYTOTEXT function takes two arguments: array and format. Array is the array or range to convert to text. Array can be provided as a range like A1:A3 or an array like {1,2,3}. The format argument controls the structure of the output. By default, format is zero and ARRAYTOTEXT will output a “concise” format that displays plain unquoted values and no curly braces. Essentially, concise format is a plain, human-readable format....
Examples Use the COLUMNS function to get the column count for a given reference or range. For example, there are 6 columns in the range A1:F1 so the formula below returns 6: The range A1:Z100 contains 26 columns, so the formula below returns 100: You can also use the COLUMNS function to get a column count for an array constant: Although there is no built-in function to count the number of cells in a range, you can use the COLUMNS function together with the ROWS function like this: More details here....
