=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
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d9...
