Skip to content

excel

Excel Cube functions explained: Cubevalue, Cubeset, Cubemember

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 file. How to use Cube functions In my experience, when using Cube functions, I prefer… Read More »Excel Cube functions explained: Cubevalue, Cubeset, Cubemember

Excel Function – INDIRECT()

  • ilyar126 

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 in double quotations “” You can also use a combination of ADDRESS function to pass… Read More »Excel Function – INDIRECT()

Excel Function – ACOTH()

  • ilyar126 

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()

  • ilyar126 

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 how to measure Earth’s distances between any 2 points here.

Excel Function – ACCRINTM()

  • ilyar126 

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 of security; if blank then defaults at $1000 [basis]: 5 types of day counts; this… Read More »Excel Function – ACCRINTM()

Excel Function – ACCRINT()

  • ilyar126 

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 stock was sold rate: annual coupon rate frequency: how often coupon pays per year [basis]:… Read More »Excel Function – ACCRINT()

Excel Function – AGGREGATE()

  • ilyar126 

Excel Aggregate Function – This is a very useful function that actually combines 19 subfunctions that can be activated by choice.

Excel Function – ABS()

  • ilyar126 

Description Provides absolute number of any positive or negative number. The results is always a positive number. An absolute number means simple a number without a negative sign, so its always positive. Syntax ABS(number) Examples

Excel CUBEVALUE vs SUMIFS & GETPIVOTDATA

Once CUBEVALUE formula came out about 10 years ago along with PowerPivot, I was delighted because it had filled the gap that was missing in SUMIFS and GETPIVOTDATA formulas for certain usage scenario.