Monthly Archives: April 2020

Excel

Finnish tax card

Finnish tax card includes so called basic taxation percentage. That will be used in taxation till the cumulative salary during the validity period reach the limit. When cumulative salary exceeds the limit, the extension percent will be used for taxation.

Limit: 47000 €

Salary: 5000

Basic percentage: 25

Extension percentage 49

As long as the cumulative salary stays under 47000, the tax will be 5000 * 25 % = 1250. If the cumulative salary exceeds 47000 then the tax will be 5000 * 49 % = 2450.

If limit is exceeded but only partially, the salary will be taxed by basic percentage till the limit, and the part exceeding the limit will be taxed per extension percentage.

If cumulative salary during the validity period before the payment is 46000, then 1000 of the salary will be taxed per 25 % and remaining 4000 per 49 % equaling to (1000 * 25 %) + (4000 * 49 %) = 2210.

The IF-sentence is here for copy paste:

=IF(D8-D3 >=D4;D4*D7;IF(AND(D8-D3>0;(D8-D3<D4));(D8-D3)*D7+(D4-(D8-D3))*D9;IF(D8<=D3;D4*D9)))

You might find some other IF-sentence for the same purpose. This is just my example.

Functions

Introducing INDIRECT function

INDIRECT formula returns the value of a defined cell. Examples below shed the light how the function functions.

Think you have a three-dimensional sales report Excel sheet. In each sheet there is a sales report from different sales district. The top sheet sums up all the sheets.

The top sheet sums up districts 1 to 10.

But somewhere exists an error and the top sheet cannot calculate the sum.

You can browse each sheet and track the error. If you do this several times or if you have dozens of sheets in your workbook, you might think to have a faster way to find out the sheet not functioning correctly.

INDIRECT formula will help us. We need to what is the cell C7 value in each sheet, as one C7 shows an error.

In the District_1 view the value in C7 is 47,2 in District_2 C7 holds value of 27,6 and so on. The faulty value is in sheet District_7.

Once the wrong value was corrected, the sheet District_7 shows correct figures.

The INDIRECT formula needs sheet, exclamation mark and cell like INDIRECT(District_1!C7). With references you avoid typing the formula each time.

=INDIRECT($B16 & “!” & C$15 & “”)