Personal Insights
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!
Links I personally used to get some understanding about the formulas:
https://wiki.openoffice.org/wiki/Documentation/How_Tos
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?
Thanks in advance,
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