EDUCAÇÃO E TECNOLOGIA

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 can 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 it 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 char
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 Bracker ({}) 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!

Links I personally used to get some understanding about the formulas: 

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

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