Purpose Return value Syntax =TYPE(value) value - The value to check the type of. Using the TYPE function The TYPE function returns a numeric code representing “type” in 5 categories: number = 1, text = 2, logical = 4, error = 16, and array = 64. The TYPE function takes one argument, value , which can be a reference, a formula, or a hardcoded value. The table below shows the possible type codes returned from TYPE and the meaning of each:...
Purpose Return value Syntax =ASIN(number) number - The value to get the inverse sine of. The number must be between -1 and 1 inclusive. Using the ASIN function The ASIN function, also called arc-sine, returns the inverse sine of a value. The input number must be between -1 and 1, inclusive. Geometrically, given the ratio of a triangle’s opposite side over its hypotenuse, the function returns the angle of the triangle....
Purpose Return value Syntax =AVERAGEIFS(avg_rng,range1,criteria1,[range2],[criteria2],...) avg_rng - The range to average. range1 - The first range to evaluate. criteria1 - The criteria to use on range1. range2 - [optional] The second range to evaluate. criteria2 - [optional] The criteria to use on range2. Using the AVERAGEIFS function The AVERAGEIFS function calculates the average of cells in a range that meet multiple conditions, referred to as criteria . Each condition is provided with a separate range and criteria ....
Purpose Return value Syntax =BINOMDIST(number_s,trials,probability_s,cumulative) number_s - The number of successes. trials - The number of independent trials. probability_s - The probability of success on each trial. cumulative - TRUE = cumulative distribution function, FALSE=probability mass function. Using the BINOMDIST function The BINOMDIST function returns the individual term binomial distribution probability. You can use BINOMDIST to calculate probabilities that an event will occur a certain number of times in a given number of trials....
Explanation In this example, the goal is to count the number of cells in data (B5:B15) that are not equal to “red” or “blue”. This problem can be solved with the COUNTIFS function or the SUMPRODUCT function, as explained below. Not equal to The not equal to operator in Excel is <>. For example, with the number 10 in cell A1: =A1<>5 // returns TRUE =A1<>10 // returns FALSE The first formula returns TRUE since A1 is indeed not equal to 5....
Explanation In this example, the goal is to count odd numbers in the range B5:B15, which is named data . This can be done with the SUMPRODUCT function together with the ISODD function. Instead of ISODD, the MOD function can also be used. Both approaches are explained below. SUMPRODUCT with ISODD The SUMPRODUCT function works directly with arrays. One thing you can do quite easily with SUMPRODUCT is perform a logical test on a range, then count the results....
Explanation In this example, the goal is to count the number of cells in a range that contain positive numbers. For convenience, the range B5:B15 is named data . This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both methods are explained below. COUNTIF function The COUNT function counts the number of cells in a range that meet supplied criteria. For example, you can use COUNTIF like this:...
Explanation In this example, the goal is to count the number of columns in the data that contain 19 (the value in cell I4). The main challenge in this problem is that the value might appear in any row, and more than once in the same column. If we wanted to simply count the total number of times a value appeared in a range, we could use the COUNTIF function ....
Explanation In this example, the goal is to decrease the prices shown in column C by the percentages shown in column D. For example, given an original price of $70.00, and an decrease of 10% ($7.00), the result should be $63.00. The general formula for this calculation, where “x” is the new price, is: x=old*(1-percentage) x=70*(1-10%) x=70*0.90 x=63.00 Converting this to an Excel formula with cell references, the formula in E5 becomes:...
About This Shortcut By default, drag and drop “cuts” cells like Edit > Cut, and overwrites values at the new location. Use this shortcut to copy values and to insert cells at the new location. This also works when dragging entire rows or columns. This is a good way to duplicate a row in an Excel Table, since Tables do not allow you to copy and insert paste entire rows....
Excel Tables are one of the most interesting and useful features in Excel. If you need a range that expands to include new data, and if you want to refer to data by name instead of by address, Excel Tables are for you. This article provides an introduction and overview. 1. Creating a table is fast You can create an Excel Table in less than 10 seconds. First, remove blank rows and make sure all columns have a unique name, then put the cursor anywhere in the data and use the keyboard shortcut Control + T....
Excel hours In Excel, dates are just serial numbers, so a single day has a numeric value of 1. This means that 6 hours is one-quarter of a day (0.25), 12 hours is half a day (0.5), 18 hours is three-quarters of a day (0.75), and 24 hours is 1 day. In the same way, 6:00 AM has a numeric value of 0.25, 12:00 PM has a value of 0.5, and 6:00 PM has a value of 0....
Transcript In this video we’ll look how to use the FILTER function with Boolean logic to apply multiple criteria. In this worksheet we have some sample order data in a table called “data”. Let’s use the FILTER function to find all “blue” orders in June. To visualize how this works I’m going to set up the logic in helper columns first. Then, I’ll move that logic into the FILTER function, to make an all-in-one formula....
Explanation In this example, the goal is to convert the percentages shown in column C to amounts, where the total of all amounts is given as $1945. In other words, if we know Rent is 36.0%, and the total of all expenses is $1945, we want to calculate that Rent is $700. With “x” as the number we want to find, we have: =36.0%*1945=x =0.36*1945 =700.20 Note: in the example, the percentage values in column C are unrounded decimal numbers like 0....
Explanation The WORKDAY function returns a date in the future or past, based on a start date, workdays, and optional holidays. WORKDAY automatically excludes weekends, and counts only Monday through Friday as workdays. In the example shown, WORKDAY is configured to get a project midpoint date by adding half of the days value to the start date: =WORKDAY(C5,E5/2,holidays) Project A is 5 workdays, so E5/2 = 2.5 days. The WORKDAY function ignores fractional values and uses only the integer portion of days, so it uses the value 2 to return a date of May 8....
Explanation At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings, Sunday=1 and Saturday = 7. The trick to this formula is assembling an array of dates that you can feed into the WEEKDAY function. This is done with ROW with INDIRECT:...
Explanation Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, cell B3 in this case. To highlight matching rows, we use this logical expression: $B4=$K$5 The reference to B4 is mixed , with the column locked and row unlocked, so that only values in column B are compared to the country in cell K5. The reference to K5 is absolute , to prevent changes when the conditional formatting is applied to every cell in the range B4:H9....
Transcript In this video, we’ll take a closer look at how table ranges work. One of the most useful features of Excel Tables is that they create a dynamic range. A dynamic range automatically expands to handle new data, so it works well for reports, pivot tables, or charts that need to show the latest information. To illustrate, I’ll add some formulas to this worksheet to report on the size of this table....
Transcript In this lesson, we’ll look at how to create a custom date format. Excel provides a good selection of date codes that can be used to assemble a wide variety of custom date formats. Let’s take a look. Let’s look first at the Date code reference table. This table shows the date codes available for custom date formats. There are several codes each for days, months, and years, including abbreviated and non-abbreviated forms, and the option to add leading zeros to day and month numbers....
Transcript In this lesson, we’ll look at the fill handle’s most powerful feature—its ability to recognize and repeat custom patterns that you specify. To use the fill handle to enter data following a custom pattern, start the pattern by entering data in at least two cells. Then, select those cells, and drag the fill handle to repeat the pattern. Let’s take a look. To use the fill handle to enter a regular series of numbers, enter the first two numbers, select both cells, and then drag the fill handle....
