This is a tabular list of the inmydata predefined functions and examples.
Function | Parameters | Calc | Example 1 | Example 2 |
Divide | numerator denominator | If denominator is not 0 then divide numberator by denominator Excel : =IF(D3=0,0,C3/D3) | Numerator: 10 Denominator: 5 Calc: 10 / 5 = 2 | Numerator: 10 Denominator: 0 Calc = 0 |
DecimalSubtract | firstvalue secondvalue | Takes second value from first value Excel: =C6-D6 | firstvalue: 10 secondvalue: 5 Calc: 10 - 5 = 5 |
|
Percentage | numerator denominator | If denominator is not 0 then divide numberator by denominator and multiple the result by 100 Excel: =IF(D8=0,0,(C8/D8) * 100) | Numerator: 5 Denominator: 10 Calc: (5 / 10) * 100 = 50 | Numerator: 5 Denominator: 0 Calc: 0 |
MarginPercentage | sales cost |
if sales is not 0 then take the cost from sales and divide this by sales Excel: =IF(D11=0,0,((C11-D11)/C11) ) | Sales: 100 Cost: 80 calc: (100-80) / 20 = 4 | Sales: 110 Cost: 0 Calc: = 0 |
DecimalDifferenceDivided | numerator1 numerator2 denominator |
if denominator is not 0 take numerator 2 from numerator1 and then divide by the denominator Excel : =IF(E13=0,0,(C13-D13)/E13) | numerator 1: 100 numerator1: 80 denominator: 4 calc: (100-80)/4 = 5 | numerator 1: 100 numerator1: 80 denominator: 0 calc : 0 |
DecimalAddAddSubtract | addValue1 addValue2 addValue2 | Add addValue1 to addValue2 then take away subtractValue Excel: =C16+D16-E16 | addValue1: 20 addValue2: 50 subtractValue: 35 Calc: 20+50-35 = 25 | |
| DecimalIfNot | value1 value2 | If the firstvalue is not 0 return first value otherwise return value2 Excel: =IF(C18<>0,C18,D18) | Value1 : 15 Value2: 20 Calc = 15 | Value1: 0 Value3: 20 Calc = 20 |
| DiscountPercentage | sales discountgiven | if discountgiven + sales is not 0, divide discountgiven by the value of sales + discountgiven} Excel: =IF(C21+D21<>0,D21/(C21+D21),0) | sales: 80 discountgiven: 20 Calc = 20 / (80+20) = 0.2 | sales: 100 discountgiven: -100 Calc = 0 |
| ATV | salesgross noinvoices nocredits | if noinvoices - nocredits is not 0, divide salesgross by the value of noinvoices - nocredits | salesgross : 100 noinvoices: 30 nocredits: 20 calc: 100/(30-20) = 10 | salesgross : 100 noinvoices: 30 nocredits: 30 calc:0 |
| WeekDay | dateTime startDay | Takes a date input and the day number of the start of the week and returns the dayname for the date. The startday number os in the range 0-6 with 0 = sunday | ||
| CalendarYearFromDate | dateTime | Returns the year based on the date entered. | ||
| WeekDayFromString | dateTimeString startDay | Takes a string representation of a date and the day number of the start of the week and returns the dayname for the date. The startday number os in the range 0-6 with 0 = sunday | ||
| ANGTransactionType | value | return "sale" or "credit" based on the value entered. Sale if the value is >= 0 Excel: =IF(C27>= 0,"Sale","Credit") | Value : 150.7 Calc : "Sale" Value : 0 Calc : "Sale" Value: -1 Calc: "Credit" | |
| Weeks between two dates | date1 date2 | Takes 2 input dates and determines the number of complete weeks between the 2 values. A value of 0 will result of the input values are invalid. | date1: 10/4/2021 date2: 26/4/2021 Calc: 2 | |
| Days between two dates | date1 date2 | Takes 2 input dates and determines the number of days between the 2 values. A value of 0 will result of the input values are invalid. | ||
| Hours between two dates | date1 date2 | Takes 2 input dates and determines the number of hours between the 2 values. A value of 0 will result of the input values are invalid. | ||
| Minutes between two dates | date1 date2 | Takes 2 input dates and determines the number of minutes between the 2 values. A value of 0 will result of the input values are invalid. | ||
| Seconds between two dates | date1 date2 | Takes 2 input dates and determines the number of seconds between the 2 values. A value of 0 will result of the input values are invalid. | ||
| Millieseconds between two dates | date1 date2 | Takes 2 input dates and determines the number of milliseconds between the 2 values. A value of 0 will result of the input values are invalid. |
