Purpose Return value Syntax =LOG10(number) number - The positive number for which you want the base-10 logarithm. Using the LOG10 function LOG10 returns the base-10 logarithm of a number. In simple terms, it answers the question: “10 raised to what power gives me this number?” The table below shows how LOG10 works and how it is related to raising 10 to a specific power: Number LOG10 Because 1000 3 10³ = 1000 100 2 10² = 100 10 1 10¹ = 10 1 0 10⁰ = 1 0....
Purpose Return value Syntax =MAXIFS(max_range,range1,criteria1,[range2],[criteria2],...) max_range - Range of values used to determine maximum. 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 MAXIFS function The MAXIFS function returns the largest numeric value in cells that meet multiple conditions, referred to as criteria . Each condition is provided with a separate range and criteria ....
Purpose Return value Syntax =NPER(rate,pmt,pv,[fv],[type]) rate - The interest rate per period. pmt - The payment made each period. pv - The present value, or total value of all payments now. fv - [optional] The future value, or a cash balance you want after the last payment is made. Defaults to 0. type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0....
About This Shortcut This shortcut opens the Macro Dialog Box About This Shortcut This shortcut opens the Visual Basic Editor.
The Pareto Chart is a built-in chart type in Excel 2016. A Pareto chart is a variant of the histogram chart, arranged in descending order for easy analysis. The sequencing is performed automatically, and a separate line is added on a secondary axis to make it easy to see contribution toward 100% as columns are plotted to the left. Pareto charts highlight the biggest factors in a set of data. Following the idea of 80/20 analysis, they try to show which (approximately) 20% of the categories contribute 80% of the data being measured....
Explanation The FILTERXML function can parse XML using XPath expressions. XML is a special text format designed to transport data, with features that allow it to be easily parsed and verified by software. XPath is a query language for selecting the elements and attributes in an XML document. The FILTERXML function uses XPath to match and extract data from text in XML format. In the example shown cell B5 contains XML data that describes 10 music albums....
Pivot tables have a built-in feature to calculate running totals. In the example shown, a pivot table is used group data by month and show both the monthly total and running total over a 6-month period. Fields The source data contains three fields: Date , Sales , and Color . Only two fields are used to create the pivot table: Date and Sales . The Date field has been added as a Row field, then grouped by Months:...
Explanation In this example, the goal is to generate a list of random numbers without duplicates. This involves jumping through a few hoops because although the RANDARRAY function can easily generate a list of random integers, there is no guarantee that the numbers will be unique. In the explanation below, we’ll look first at a simple option with the RANDARRAY function, then at a more complete solution based on the SEQUENCE function....
Explanation In this example the goal is to sum the numbers in the range F5:F16 when corresponding cells in the range C5:C15 are not equal to “Red”. To solve this problem, you can use either the SUMIFS function or the SUMIF function . The SUMIF function is an older function that supports only one criteria. SUMIFS on the other hand can be configured to apply multiple criteria. Both options are explained below....
Explanation In this example, the goal is to sum values in rows that are visible and ignore values in rows that are hidden. The range F7:F19 contains 13 values total, 4 of which are hidden by the filter applied to column C. This is a good job for the SUBTOTAL function , which can distinguish between visible and hidden cells when it applies various calculations. Another way to solve this problem is with the AGGREGATE function ....
Purpose Return value Syntax =TANH(number) number - The input number. Using the TANH function The TANH function returns a number’s hyperbolic tangent. Given input -2, the function returns the number -0.96402758 as output. =TANH(-2) // returns -0.96402758 Explanation Just like the circular tangent, the hyperbolic tangent is defined in terms of the hyperbolic sine and hyperbolic cosine. =SINH(a)/COSH(a) // definition of TANH(a) Geometrically, the hyperbolic tangent of a number can be interpreted as the slope of the line from the origin to the point on the unit hyperbola corresponding to the number’s hyperbolic angle where the hyperbolic angle is half the area under the hyperbola between the origin and that point....
A text value (also called a “text string”) is one of Excel’s primary content types. Other types of content include numbers, dates, times, and Boolean values. As data is entered into a worksheet, Excel makes a “best guess” on the type of content it is, and formats the value automatically. By default, text values are aligned on the left, and numeric values (including dates and times) are aligned on the right....
Transcript In this video we’ll look at how to build a two-way lookup with INDEX and MATCH using an approximate match. Here we have a simple cost calculator which looks up cost based on a material’s width and height. The match needs to be approximate. For example, if the width is 250, and the height is 325, the correct result is $1,800. If the width is 450, and the height stays at 325, the correct result is $3,600....
Explanation In this example, the goal is to perform a two-way lookup based on the name in cell H4 and the month in cell H5 with the VLOOKUP function. Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can create a dynamic column index number by using the MATCH function to locate the right column. This technique allows you to create a dynamic two-way lookup, matching on both rows and columns....
Explanation NETWORKDAYS is a built-in function accepts a start date, an end date, and (optionally) a range that contains holiday dates. In the example shown, we generate the start and end date using the DATE function like this: DATE(D5,1,1) // first day of year DATE(D5,12,31) // last day of year The DATE function returns these dates directly to the NETWORKDAYS function as start_date and end_date, respectively. Holidays are supplied as a list of dates in E5:E14, the named range holidays ....
Purpose Return value Syntax =XOR(logical1,[logical2],...) logical1 - An expression, constant, or reference that evaluates to TRUE or FALSE. logical2 - [optional] An expression, constant, or reference that evaluates to TRUE or FALSE. Using the XOR function The XOR function performs what is called “exclusive OR”, in contrast to the “inclusive OR” performed by the OR function . Whereas the OR function returns true if any input is TRUE, XOR only returns TRUE in specific cases....
Purpose Return value Syntax =BITLSHIFT(number,shift_amount) number - The number to be bit shifted. shift_amount - The amount of bits to shift, if negative shifts bits to the right instead. Using the BITLSHIFT function For any bit shift that results in integer overflow, where the result is larger than 2^48 -1, the function returns the #NUM! error. How It Works The shift_amount can either be positive or negative. If a negative number is provided, the bits are shifted to the right instead....
Explanation In this example the goal is to count numbers longer than 15 digits with a formula. The COUNTIF function may seem like this logical choice. However, if you try to count very long numbers (16+ digits) in a range with the COUNTIF function, you may see incorrect results, due to a bug in how RACON functions handle long numbers, even when the numbers are stored as text. You can see this problem in the worksheet below....
Explanation In this example, the goal is to count the number of names in the range B5:B16 (Invited) that are missing from the range D5:D12 (Attended). This problem can be solved with the COUNTIF function or the MATCH function, as explained below. Both approaches work well. The advantage of the MATCH approach is that it will work with arrays or ranges . The COUNTIF function is limited to ranges only, like other functions in this group ....
Explanation In this example, the goal is to count rows in a set of data using multiple criteria and “not equals to” logic. Specifically, we want to count males that are not in group A or B. All data is in an Excel Table named data in the range B5:D15. This problem can be solved with the COUNTIFS function or the SUMPRODUCT function. Both approaches are explained below. COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?...
