@ > Home > Contents > Get short concatenated linear and exponential functions Excel 4+

Situation:

The German income tax uses a fitted function with no breaks in between:

0 - 7.664 Euro (tax free basis): 0;
7.665 - 12.739 Euro: (793,10y + 1.600)y;
12.740 - 52.151 Euro: (265,78z + 2.405)z + 1.016;
52.152 an more Euro: 0,45x - 8.845.

"y" and "z" are ten thousands of the exceeding parts of the taxable income. "x" is the full taxable income. 

How is a manageable formula possible on this?

Solution:

=INT(SUMPRODUCT(
(A2>={7665\12740\52152})*
(A2<{12740\52152\99999999999})*(
(A2-{7664\12739\0})%%^2*{793.1\265.78\0}+
(A2-{7664\12739\0})%*{16\24.05\45}+
{0\1016\-8845})))

The tax free basis (Euro 7664) is not needed in the formula. A short description of the formula follows:

Row 2 and 3: functions meeting points
Row 4: exponential part
Row 5: linear part
Row 6: f(0)

For a really short formula of concatenated but only linear functions see 5022.htm.