How to Use Excel Lines in Report Sets
- 1 Minute to read
- Print
- DarkLight
- PDF
How to Use Excel Lines in Report Sets
- 1 Minute to read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
The full range of Excel functions can be used in a report set line (row set or column set). After selecting Excel as the line type, start typing the function, and a function menu will appear. Hover over each function to view its description, as shown below.
You might use Excel lines in Report Sets to round or sum cells or to use IF statements so that certain criteria is met. Consider the business use case mentioned below, where the ROUND and SUM functions are used.
Business Use Case
In this use case, a row set is created to total assets and liabilities. The SUM and ROUND functions are used to total assets.
In the image below, line 21 is an Excel line that provides the total amount of assets. In the Formula/Rule column, the Excel function is:
=SUM(ROUND(F15,0)+ROUND(F19,0))
This means that the value in cell F21 is the sum of values in cells F15 (Total Current Assets) rounded to 0 decimals and F19 (Total Non-Current Assets), both rounded to 0 decimals. If you entered F15,2 the value would round to 2 decimals.
When this row set is used on the row axis of a Dynamic Report, the results show Total Current Assets (436,035) plus Total Non-Current Assets (53,598), equaling Total Assets of (489,533) for 2018, as shown below.
Supported Excel basic functions
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 |
Was this article helpful?