When creating products and services on eymanage, you have the option to calculate the rates using your own custom formulas. This is helpful if your nursery uses a more complex system for determining product/service rates. We go over the basics on using formulas here. For this article, the focus is specifically on the use of functions.
Functions work by allowing you to modify the output of real numbers, which in turn gives more options for creating formulas. The modification applied depends both on the type of function, and the numbers that have been inputted into the formula.
List of functions
You can use the functions MAX, MIN, FLOOR, CEIL, ABS and IFS to alter the formula in different ways.
MAX/MIN
These functions will return the highest or lowest number value, respectively, from a group of numbers. As a very basic example, if you type in MAX(1, 2, 3, 4), it will return the number 4, and that is the number that will be used for the rest of the formula. If you use the same example, but replace MAX with MIN, it will return 1.
FLOOR/CEIL
These functions are used to round fractional numbers to the nearest whole integer. FLOOR rounds down, while CEIL rounds up. For example, if you type FLOOR(4.78), it will return 4. Meanwhile, typing in CEIL(4.78) will return 5.
ABS
ABS returns the absolute value of a number. In practical terms, this means that any negative numbers will return positive, and any positive numbers will also return positive. ABS(-1) = 1, and ABS(1) = 1.
IFS
IFS is a versatile function that returns different results depending on the data provided. The IFS function is split into two parts which are separated by a comma (,): the condition, and the result. By adding an additional comma after the result, you're able to set up alternative actions. See the usage example for a more detailed breakdown.
Usage examples
By using formulas, you are able to vary the amount charged to parents based on their session rates, funding rate, weekly/monthly funded hours, and weekly/monthly scheduled hours. Let's take a look at how you might use each of the functions described above in a formula for calculating a weekly or monthly consumable charge.
MAX
The MAX function will return the highest value from a set of data. This can be useful if you want to ensure calculations cannot go below a certain number. For example:
MAX(1, [Average Weekly Hourly Session Rate] - [Local Authority Term/Funding Rate]) * [Weekly Funded Hours]
Explanation
- The Local Authority Term/Funding Rate is subtracted from the child's Average Weekly Hourly Session Rate to get the parent's chargeable hourly rate. For example, 9.25 - 9.03 = 0.22.
- The MAX function ensures that the formula will never be calculated using a chargeable hourly rate below 1. MAX(1, 0.22) = 1.
- The result is then multiplied by the Weekly Funded Hours. 1 * 15 = £15.
MIN
The MIN function will return the lowest value from a set of data. This can be useful if you want to ensure calculations cannot go above a certain number. For example:
MIN(1, [Average Weekly Hourly Session Rate] - [Local Authority Term/Funding Rate]) * [Weekly Funded Hours]
Explanation
- The Local Authority Term/Funding Rate is subtracted from the child's Average Weekly Hourly Session Rate to get the parent's chargeable hourly rate. For example, 10.25 - 9.03 = 1.22.
- The MIN function ensures that the formula will never be calculated using a chargeable hourly rate above 1. MIN(1, 1.22) = 1.
- The result is then multiplied by the Weekly Funded Hours. 1 * 15 = £15.
FLOOR
Since the FLOOR function allows you to round numbers down, you can use it in situations where you need to calculate charges using whole numbers. For example:
FLOOR([Weekly Funded Hours] / 3.5) * 0.25
Explanation
[Weekly Funded Hours] / 3.5 splits the child's weekly funded hours into blocks of 3.5. If the child receives 15 hours of funding a week, then 15/3.5 = 4.29.
The FLOOR function rounds this result down, so only complete blocks are counted. If the result is 4.29, it becomes 4.
Each complete 3.5-hour block is worth £0.25. 0.25*4 = £1.
CEIL
The CEIL function allow you to round up numbers, and can be used in a similar way to the FLOOR function seen above. Whether you choose CEIL or FLOOR for a particular formula depends on the context of what you're trying to calculate. For example:
CEIL([Weekly Funded Hours] / 4) * 0.50
Explanation
- Weekly Funded Hours] / 4 splits the child's weekly funded hours into blocks of 4. If the child receives 15 hours of funding a week, then 15/4 = 3.75.
The CEIL function rounds this result up, so only complete blocks are counted. If the result is 3.75, it becomes 4.
Each complete 4-hour block is worth £0.50. 0.50*4 = £2.
ABS
The ABS function always returns the Absolute Value of a number, which means that if a number if negative, it will turn positive. This is useful in situations where you're concerned with finding the difference between two numbers. For example:
ABS([Average Weekly Hourly Session Rate] - [Local Authority Term/Funding Rate]) * [Weekly Funded Hours]
Explanation
The Local Authority Term/Funding Rate is subtracted from the child's Average Weekly Hourly Session Rate to get the parent's chargeable hourly rate. For example, 8.90 - 9.03 = -0.13.
The ABS function converts this number into a positive. ABS(-0.13) = 0.13.
The result is then multiplied by the Weekly Funded Hours. 0.13 * 15 = £1.95.
IFS
The IFS function can be used to charge parents different amounts depending on the age of their child.
IFS(
[Age] < 2, [Weekly Funded Hours] * 0.5,
[Age] >= 2 AND [Age] < 3, [Weekly Funded Hours] * 1,
true, [Weekly Funded Hours] * 1.5
)
Explanation
- In the first line, children under 2 (the condition) are charged £0.50 per hour of weekly funding (the result).
- In the second line, children who are 2 or older, but are younger than 3, (the condition) are charged £1 per hour of weekly funding (the result).
- In the third line, because TRUE is used, all other children are charged £1.50 per hour of weekly funding.
- The conditions (i.e. [Age] < 2) are separated from the results [i.e. [Weekly Funded Hours] * 0.5) by a comma (,).
- Each alternative condition is also separated by a comma (,).
- TRUE is used to indicate the default result if none of the conditions are met.