GETPIVOTDATA is one of my favorite functions in Excel when it comes to retrieving and aggregating data. Its similar to SUMIFS and CUBEVALUE, but it always references a pivot table. What is the GETPIVOTDATA function? The GETPIVOTDATA function is a built-in function in Excel that allows you to retrieve data from a pivot table based…
Tag: excel
Excel XLOOKUP function
You want to ride the track with a super power?! Go get XLOOKUP, it will definitely serve you well. The XLOOKUP function in Excel is a versatile and powerful tool designed to replace older functions like VLOOKUP, HLOOKUP, and LOOKUP. It offers more flexibility and functionality, making it a favorite among Excel users for data retrieval tasks….
Excel Cube functions explained: Cubevalue, Cubeset, Cubemember, Cuberankedmember
One of my favorite Excel functions to do business modeling are Cube functions. They act as a more efficient replacement for SUMIFS, GETPIVOTDATA functions that serve to sum aggregated results with various filters applied. Cube functions (CUBEVALUE, CUBEMEMBER, CUBESET) will only work if you have a PowerPivot model or live cube connection in your Excel…
Excel – measure distance between any points on Earth
If you want to measure a distance between any two points, there is an Excel formula that does just that. For that you solely need the coordinates of locations (Latitude, Longitude) and a blank Excel worksheet.
Excel Function – INDIRECT()
Description Indirect function extracts the value of the referenced cell. Syntax INDIRECT(ref_text, [a1]) ref_text: cell address such as A1 or R1C1 format [a1]: if blank then assumes TRUE(), using standard cell reference. If FALSE() then assumes R1C1 format Examples Below are few examples how to use INDIRECT. Note that cell references have to be written…
Excel Function – ACOTH()
Description To calculate the hyperbolic arctangent of a hyperbolic cotangent(number). The Hyperbolic Arctangent is an inverse of a hyperbolic cotangent is a number greater or equal to 1. Syntax ACOTH(number) Examples The results are as follows: References How to calculate Arctangent or ACOT(). If you want to see details what Hyperbolic Tangent is, check here.
Excel Function – ACOS()
Description To calculate the arccosine of a cosine (number). The Arccosine is an inverse of a cosine ranging from 0 to pi (3.14). Syntax ACOS(number) Examples The results are as follows: The DEGREES() function is the same as 180/PI(), which basically tells us Arccosine in Degrees References Want to see ACOS() in action? Check out…
Excel Function – ACCRINTM()
Description To calculate the accrued interest for a stock/bond or other security that pays interest at maturity Syntax ACCRINTM(issue, settlement, rate, par, [basis]) Issue: when the security was issues. Date is in entered as DATE() function or a sequential serial number ex: 8/29/2001 is 44437 Settlement: maturity date rate: annual coupon rate par: par value…
Excel Function – ACCRINT()
Description To calculate the accrued interest for a stock/bond or other security with periodic interest Syntax ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_method]) Issue: when the security was issues. Date is in entered as DATE() function or a sequential serial number ex: 8/29/2001 is 44437 First_interest: when first interest is due Settlement: when the…
Excel Function – AGGREGATE()
Excel Aggregate Function – This is a very useful function that actually combines 19 subfunctions that can be activated by choice.