Search

Excel Equivalent Formulas

Some Excel functions can be replicated in Anaplan using Formulas.

Excel Function Anaplan Formula

YEARFRAC

tested to 3 decimal places

Create three line items

  • Two date-formatted line items for the two dates End Date & Start Date
  • One number-formatted line item with the formula

To replicate US (NASD) 30/360 (Basis 0)

(YEAR(End Date) - YEAR(Start Date) + 30 * (MONTH(End Date) - MONTH(Start Date)) + IF DAY(Start Date) > 29 AND DAY(End Date) > 30 THEN 30 ELSE IF MONTH(Start Date) = 2 AND DAY(Start Date) = 28 AND DAY(End Date) = 28 THEN 30 ELSE DAY(End Date) - IF DAY(Start Date) = 31 THEN 30 ELSE IF MONTH(Start Date) = 2 AND DAY(Start Date) = 28 THEN 30 ELSE DAY(Start Date)) / 360

To replicate Actual Days/Actual of days in year (Basis 1)

No of Days * 360 / 365 / 360 (Accurate to 2 decimal places)

To replicate Actual Days/360 days in year (Basis 2)

(End Date - Start Date) / 360

To replicate Actual Days/365 in year (Basis 3)

(End Date - Start Date) / 365

To replicate European 30/360 (Basis 4)

((MONTH(End Date) - MONTH(Start Date) - 1) * 30 + 30 - (IF DAY(Start Date) < 30 THEN DAY(Start Date) ELSE 30) + IF DAY(End Date) < 30 THEN DAY(End Date) ELSE 30 + (YEAR(End Date) - YEAR(Start Date)) * 12 * 30) / 360
NETWORKDAYS

Create four line items

  • Two date-formatted line items for the two dates -End Date & Start Date;
  • One number-formatted line item for an integer representing the total days holiday - Holidays

Create fourth number-formatted line item with the formula.

End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - (End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - MOD(End Date - Start Date + 1 - (IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0) - (IF WEEKDAY(End Date) > 5 THEN WEEKDAY(End Date) - 5 ELSE 0) - IF WEEKDAY(Start Date) > 5 THEN ABS(WEEKDAY(Start Date) - 8) ELSE 0, 7)) / 7 * 2 - Holidays
MMULT

Create three lists for the matrix dimensions: I, J, K.

  • Add a parent item in list K to sum intermediate products.

Create two modules Matrix A and Matrix B to use as the input matrices.

  • Configure Matrix A with one line item (Value) set as Pages and the lists I set as Rows and & K as Columns.
  • Configure Matrix B with one line item (Value) set as Pages and the lists K set as Rows and & J as Columns—the column count of Matrix B must match the row count of Matrix A.

Create a module Matrix C as the output matrix.

  • Configure Matrix C with one line item (Output) as Pages and the list I as Row, and lists J & K as Columns. In the module, hide every occurrence of the K dimension, except the parent (Sum) column.

The formula for the line item (Output) in Matrix C is

Matrix A.Value * Matrix B.Value