# SAP PaPM Cloud: Calculation Workbook Formula

=SUMIFS(EXPECTED!E\$83:E\$104;EXPECTED!\$C\$83:\$C\$104;”405″;EXPECTED!\$D\$83:\$D\$104;”10″)+SUMIFS(EXPECTED!E\$109:E\$130;EXPECTED!\$C\$109:\$C\$130;”405″;EXPECTED!\$D\$109:\$D\$130;”10″)+SUMIFS(EXPECTED!E\$136:E\$157;EXPECTED!\$C\$136:\$C\$157;”405″;EXPECTED!\$D\$136:\$D\$157;”10″)

Excel enthusiasts will definitely know how to read and formulate formula like above.

What if you can use the same skill in modeling calculations connected to live stored data through SAP Profitability and Performance Management Cloud (SAP PaPM Cloud) via this so called Workbook Calculation? Very intriguing and very exciting, isn’t it?

I felt the same way when I learned about this new function and it’s capability. At the very first chance that I could have my hands on it, I started playing around. But since I am not an excel expert myself, I started thinking….

### What kind of formula can I use here?

Now, I know the answer! And I wish to share with you too the functions and operators that you may use through this short but sweet blog post.

## Basic Functions

Below are the basic functions that you may use in Workbook Calculation

**scroll to the right to see the complete list**

 ABS ACOS ASIN ATAN ATAN2 COS CEILING ODD EVEN FLOOR LN SQRT SIN TAN SIGN GCD LCM PRODUCT POWER MOD QUOTIENT SUBTOTAL INT MROUND ROUND ROUNDDOWN ROUNDUP TRUNC EXP LOG LOG10 SUM SUMIF SUMIFS SUMPRODUCT SUMSQ SUMX2MY2 SUMX2PY2 SUMXMY2 SERIESSUM PI SQRTPI DEGREES RADIANS COSH ACOSH SINH ASINH TANH ATANH MDETERM MINVERSE MMULT FACT FACTDOUBLE MULTINOMIAL RAND RANDBETWEEN COMBIN ROMAN CEILING.PRECISE ISO.CEILING FLOOR.PRECISE MUNIT AND OR NOT IF IFERROR TRUE FALSE DATE TIME DATEVALUE TIMEVALUE NOW TODAY HOUR MINUTE SECOND DAY MONTH YEAR WEEKNUM WEEKDAY EDATE EOMONTH WORKDAY WORKDAY.INTL DAYS360 NETWORKDAYS NETWORKDAYS.INTL YEARFRAC DATEDIF CLEAN TRIM DOLLAR FIXED TEXT VALUE LOWER UPPER PROPER CHAR CODE REPLACE SUBSTITUTE CONCATENATE LEFT MID RIGHT REPT LEN FIND SEARCH EXACT T ISERROR ISERR ISNA ERROR.TYPE ISNUMBER ISEVEN ISODD N ISBLANK ISLOGICAL ISTEXT ISNONTEXT ISREF TYPE NA REFRESH DAVERAGE DCOUNT DCOUNTA DGET DMAX DMIN DPRODUCT DSTDEV DSTDEVP DSUM DVAR DVARP BESSELI BESSELJ BESSELK BESSELY BIN2DEC BIN2HEX BIN2OCT DEC2BIN DEC2HEX DEC2OCT HEX2BIN HEX2DEC HEX2OCT OCT2BIN OCT2DEC OCT2HEX ERF ERF.PRECISE ERFC ERFC.PRECISE DELTA GESTEP COMPLEX IMABS IMAGINARY IMARGUMENT IMCONJUGATE IMCOS IMDIV IMEXP IMLN IMLOG10 IMLOG2 IMREAL IMSIN IMSQRT IMSUB IMPOWER IMPRODUCT IMSUM RANK.AVG FV FVSCHEDULE NPV PV RECEIVED XNPV CUMIPMT CUMPRINC IPMT ISPMT PMT PPMT COUPDAYBS COUPDAYS COUPDAYSNC COUPNCD COUPNUM COUPPCD DURATION MDURATION NPER YIELD YIELDDISC YIELDMAT AMORDEGRC AMORLINC ODDFYIELD ODDLYIELD ODDLPRICE TBILLEQ TBILLYIELD IRR XIRR RATE VDB ACCRINT ACCRINTM DISC EFFECT INTRATE NOMINAL DB DDB SLN SYD DOLLARDE DOLLARFR PRICE PRICEDISC PRICEMAT ODDFPRICE TBILLPRICE EURO EUROCONVERT RRI ADDRESS INDEX OFFSET ROW COLUMN ROWS COLUMNS TRANSPOSE LOOKUP HLOOKUP VLOOKUP CHOOSE MATCH INDIRECT TREND GROWTH FORECAST AVERAGE STDEV STDEV.S PERCENTILE PERCENTILE.INC MAX MAXA MIN MINA LARGE SMALL AVERAGEA AVERAGEIF AVERAGEIFS MEDIAN MODE MODE.SNGL GEOMEAN HARMEAN TRIMMEAN FREQUENCY RANK RANK.EQ KURT PERCENTRANK PERCENTRANK.INC PERCENTRANK.EXC QUARTILE QUARTILE.INC COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS AVEDEV STDEVA STDEVP STDEV.P STDEVPA VAR VAR.S VARA VARP VAR.P VARPA COVAR COVARIANCE.P DEVSQ CONFIDENCE CONFIDENCE.NORM CONFIDENCE.T INTERCEPT LINEST SLOPE LOGEST STEYX BETADIST BETA.DIST BETAINV BETA.INV BINOMDIST BINOM.DIST NEGBINOMDIST NEGBINOM.DIST CRITBINOM BINOM.INV CHIDIST CHISQ.DIST.RT CHISQ.DIST CHIINV CHISQ.INV.RT CHISQ.INV CHITEST CHISQ.TEST CORREL EXPONDIST EXPON.DIST FDIST F.DIST F.DIST.RT FINV F.INV.RT F.INV FISHER FISHERINV FTEST F.TEST GAMMADIST GAMMA.DIST GAMMAINV GAMMA.INV GAMMALN GAMMALN.PRECISE HYPGEOMDIST HYPGEOM.DIST LOGNORMDIST LOGNORM.DIST LOGINV LOGNORM.INV NORMDIST NORM.DIST NORMINV NORM.INV NORMSDIST NORMSINV NORM.S.INV NORM.S.DIST PEARSON RSQ POISSON POISSON.DIST PROB SKEW STANDARDIZE TDIST T.DIST T.DIST.RT T.DIST.2T TINV T.INV.2T T.INV TTEST T.TEST WEIBULL WEIBULL.DIST ZTEST Z.TEST PERMUT ACOT ACOTH ARABIC BASE COMBINA COT COTH CSC CSCH DECIMAL FLOOR.MATH SEC SECH BINOM.DIST.RANGE GAMMA MAXIFS GAUSS MINIFS PERMUTATIONA PHI SKEW.P BAHTTEXT CONCAT FINDB LEFTB LENB MIDB REPLACEB RIGHTB SEARCHB TEXTJOIN UNICHAR UNICODE BITAND BITLSHIFT BITOR BITRSHIFT BITXOR IMCOSH IMCOT IMCSC IMCSCH IMSEC IMSECH IMSINH IMTAN DAYS ISOWEEKNUM IFNA IFS SWITCH XOR PDURATION RRI ISFORMULA AREAS FORMULATEXT HYPERLINK ENCODEURL CEILING.MATH CONVERT XMATCH XLOOKUP LET OBJECT PROPERTY WEBSERVICE FILTERJSON TIMEAGO SPELLNUMS

## Aggregation:

Aside from the basic functions, Aggregation is also possible through Workbook Calculation.

The same formula as with the excel is what you can also use here.

## Function Wildcards:

Wildcard characters (?, *, ~) are also available in Workbook Calculation as comparison criteria for functions when searching.

 Asterisk (*) Zero or more characters Question Mark (?) Any single character Tilde (~) In case a wildcard character is part of the string or word, a ~ must be used to have the wildcard considered to be part of the string

Take note that not all functions can be combined or accepts wildcards; it can also just be used in comparison strings that use Equals (=).

Below are some of the functions which wildcard works:

 AVERAGEIF DSTDEVP AVERAGEIFS DSUM COUNTIF DVAR COUNTIFS DVARP DAVERAGE HLOOKUP DGET MATCH DMAX SEARCH DMIN SUMIF DPRODUCT SUMIFS DSTDEV VLOOKUP

## Array Formulas:

The useful array formula or so called CTRL + SHIFT + ENTER formula is also available in Workbook Calculation. Just the same as with excel instead of choosing ENTER to complete a formula a mechanism of CTRL + SHIFT + ENTER is necessary to lock in the desired formula.

 Curly Bracket ({}) Encloses the array Comma (,) Delimiter of elements within the row Semicolon (;) Delimiter of rows within an array

## Iterative Calculation:

Iterative calculations can be enabled or disabled in Workbook Calculation depending if you need to  run calculations over and over using the previous result.

## XMatch:

Familiar with Match? XMatch is a more sophisticated version of MATCH which can perform lookups in vertical or horizontal ranges. This functionality is also available in Workbook Calculation using the formula below.

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

## XLookup:

During matching functions you were getting N/A and wants to give meaning to it? if so then this is what you can use, and this is also available in Workbook Calculation

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

I know, I know, I promised to give you a short and sweet blog post! But what can I do there are lots of functionalities that can be used in Workbook Calculation and I cannot help but share them with you.. (^^,)

I hope this helps!!! Happy calculating!

https://wiki.openoffice.org/wiki/Documentation/How_Tos

https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

### Assigned Tags

You must be Logged on to comment or reply to a post.

Thanks for this post, Justine!

Do you know if the Workbook type of Calculation function will be available in on-premise PaPM in the future?

Best regards,

Denis

Hi Ravi,

Maybe you know the answer to my question above?

Denis

Hi Denis,

The Workbook Calculation Function was realized as a micro-service that always runs in PaPM Cloud. However, also on-premise customers will be able to call this micro-service as embedded calculation step in an environment. This specific call will become available soon, probably in Q1 2022, but as always no garantee. On-premise customers will have to subscribe for a small PaPM Cloud tenant for that.

Thomas.

Thanks a lot, Thomas!

I am looking forward to this new feature! Together with flexible input forms, those features will allow us to cover any use-case on clients that sits on Excel-based solutions now, which we still facing here and there.

Best regards,

Denis