=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.
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 |
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.
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 |
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 calculations can be enabled or disabled in Workbook Calculation depending if you need to run calculations over and over using the previous result.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Links I personally used to get some understanding about the formulas:
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d9...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
4 | |
3 | |
3 | |
3 | |
3 | |
2 | |
1 | |
1 | |
1 | |
1 |