Although not visible on the surface, ConvertCalculator offers a lot of power when it comes to adding simple and more complex calculations. This article explains some of these calculations.
How does a formula work?
Formulas are the most important feature in ConvertCalculator. If you haven't created a formula already, check out this article to get started.
A formula consists of the following:
- numbers, (
1, 12, 1.5, 20.5)
- operators (
+, -, *, /, ^)
- question values (QA, QB, QC)
- variable values (VA, VB, BC).
The first two offer no explanation. Question values are the values users input themselves and are based on calculator questions. So if you ask for desired "width" or "finish", you can add values to these questions an d use them to calculate something (price, savings etc.).
Variables are a pro feature in ConvertCalculator. They are very similar to formulas. The only difference is that they can be re-used in formula's. With variables you can thus create re-usable calculations that you can use again in formula's.
An example of a simple formula is this:
QA * 100 + 100
In this formula we multiple the value of Question A by 100 and add 100. If a user changes the value of QA (for example by sliding the range slider), the value of the formula changes.
The power of the IF
ConvertCalculator becomes really powerful when adding IF to a formula. Let's explain if by using an example:
I ask the user of two things:
QA. Desired finish of the product (Silver or Gold) QB. Amount of products they need
If the user wants more then 5 products I want to give a 20% discount. A silver product costs $ 100,- and a gold one $ 200,-.
To calculate the total price we need to do the following:
QA * QB
The problem is, if I need 10 products, the discount of 20% is not calculated. In order to apply the discount we need to change the formula to the following:
IF(QB > 5, QA * QB * 0.8, QA * QB)
The IF statement works like this:
IF(logical_test, value_if_true, value_if_false)
We can also chain IF statements together. Let's build on top of the previous example. We want to calculate the total weight of the products given the fact that silver products are 1 kg and gold products are 2 kg. We can do this a couple of ways. Let's use IF chaining to accomplish this:
IF(QA = 100, 1 * QB, "") & IF(QB = 200, 2 * QB)
You can read the above as this: "If the product is silver, return Product Amount _ 1kg, otherwise return "". If the product is gold, return Product Amount _ 2kg".
For this example it's maybe a bit overkill to using IF chaining, but there are a lot of use-cases for this.
Using AND or OR
You can use AND or OR functions in a formula to make it even more powerful. Let's take the above example but add an extra finish to QA (platinum). The discount we want to take of (when you buy more then 5 products) is not valuable on platinum. We need to use both AND and OR to accomplish this:
IF(AND(QB > 5, OR(QA = 100, QA = 200)), QA * QB * 0.8, QA * QB)
The above formula does this: If Product Amount is greater then 5 and product finish is either silver or gold, apply the discount, else don't apply the discount.
Coming soon, send a chat message if you have a specific question.
You can use all of these formulas. They are based on Excel's formula's and you can find explanations about all of these formula's on Microsoft's support site:
ABS ACCRINT ACOS ACOSH ACOT ACOTH ADD AGGREGATE AND ARABIC ARGS2ARRAY ASIN ASINH ATAN ATAN2 ATANH AVEDEV AVERAGE AVERAGEA AVERAGEIF AVERAGEIFS BASE BESSELI BESSELJ BESSELK BESSELY BETA.DIST BETA.INV BETADIST BETAINV BIN2DEC BIN2HEX BIN2OCT BINOM.DIST BINOM.DIST.RANGE BINOM.INV BINOMDIST BITAND BITLSHIFT BITOR BITRSHIFT BITXOR CEILING CEILINGMATH CEILINGPRECISE CHAR CHISQ.DIST CHISQ.DIST.RT CHISQ.INV CHISQ.INV.RT CHOOSE CHOOSE CLEAN CODE COLUMN COLUMNS COMBIN COMBINA COMPLEX CONCATENATE CONFIDENCE CONFIDENCE.NORM CONFIDENCE.T CONVERT CORREL COS COSH COT COTH COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS COUNTIN COUNTUNIQUE COVARIANCE.P COVARIANCE.S CSC CSCH CUMIPMT CUMPRINC DATE DATEVALUE DAY DAYS DAYS360 DB DDB DEC2BIN DEC2HEX DEC2OCT DECIMAL DEGREES DELTA DEVSQ DIVIDE DOLLAR DOLLARDE DOLLARFR E EDATE EFFECT EOMONTH EQ ERF ERFC EVEN EXACT EXP EXPON.DIST EXPONDIST F.DIST F.DIST.RT F.INV F.INV.RT FACT FACTDOUBLE FALSE FDIST FDISTRT FIND FINV FINVRT FISHER FISHERINV FIXED FLATTEN FLOOR FORECAST FREQUENCY FV FVSCHEDULE GAMMA GAMMA.DIST GAMMA.INV GAMMADIST GAMMAINV GAMMALN GAMMALN.PRECISE GAUSS GCD GEOMEAN GESTEP GROWTH GTE HARMEAN HEX2BIN HEX2DEC HEX2OCT HOUR HTML2TEXT HYPGEOM.DIST HYPGEOMDIST IF IMABS IMAGINARY IMARGUMENT IMCONJUGATE IMCOS IMCOSH IMCOT IMCSC IMCSCH IMDIV IMEXP IMLN IMLOG10 IMLOG2 IMPOWER IMPRODUCT IMREAL IMSEC IMSECH IMSIN IMSINH IMSQRT IMSUB IMSUM IMTAN INT INTERCEPT INTERVAL IPMT IRR ISBINARY ISBLANK ISEVEN ISLOGICAL ISNONTEXT ISNUMBER ISODD ISODD ISOWEEKNUM ISPMT ISTEXT JOIN KURT LARGE LCM LEFT LEN LINEST LN LOG LOG10 LOGEST LOGNORM.DIST LOGNORM.INV LOGNORMDIST LOGNORMINV LOWER LT LTE MATCH MAX MAXA MEDIAN MID MIN MINA MINUS MINUTE MIRR MOD MODE.MULT MODE.SNGL MODEMULT MODESNGL MONTH MROUND MULTINOMIAL MULTIPLY NE NEGBINOM.DIST NEGBINOMDIST NETWORKDAYS NOMINAL NORM.DIST NORM.INV NORM.S.DIST NORM.S.INV NORMDIST NORMINV NORMSDIST NORMSINV NOT NOW NPER NPV NUMBERS NUMERAL OCT2BIN OCT2DEC OCT2HEX ODD OR PDURATION PEARSON PERCENTILEEXC PERCENTILEINC PERCENTRANKEXC PERCENTRANKINC PERMUT PERMUTATIONA PHI PI PMT POISSON.DIST POISSONDIST POW POWER PPMT PROB PRODUCT PROPER PV QUARTILE.EXC QUARTILE.INC QUARTILEEXC QUARTILEINC QUOTIENT RADIANS RAND RANDBETWEEN RANK.AVG RANK.EQ RANKAVG RANKEQ RATE REFERENCE REGEXEXTRACT REGEXMATCH REGEXREPLACE REPLACE REPT RIGHT ROMAN ROUND ROUNDDOWN ROUNDUP ROW ROWS RRI RSQ SEARCH SEC SECH SECOND SERIESSUM SIGN SIN SINH SKEW SKEW.P SKEWP SLN SLOPE SMALL SPLIT SPLIT SQRT SQRTPI STANDARDIZE STDEV.P STDEV.S STDEVA STDEVP STDEVPA STDEVS STEYX SUBSTITUTE SUBTOTAL SUM SUMIF SUMIFS SUMPRODUCT SUMSQ SUMX2MY2 SUMX2PY2 SUMXMY2 SWITCH SYD T T.DIST T.DIST.2T T.DIST.RT T.INV T.INV.2T TAN TANH TBILLEQ TBILLPRICE TBILLYIELD TDIST TDIST2T TDISTRT TEXT TIME TIMEVALUE TINV TINV2T TODAY TRANSPOSE TREND TRIM TRIMMEAN TRUE TRUNC UNICHAR UNICODE UNIQUE UPPER VALUE VAR.P VAR.S VARA VARP VARPA VARS WEEKDAY WEEKNUM WEIBULL.DIST WEIBULLDIST WORKDAY XIRR XNPV XOR YEAR YEARFRAC