In my opinion, every person should have a track record of their all time earned income from the companies they worked for. This is because it is essential to analyze the data and trends and also when retirement time comes, to check whether the state’s pension calculations are correct or not.
To ease this task I created an Excel model with two sheets. In the first sheet (“allData”) you input every month the amounts and in the second sheet (“report”) you have a few Indicators of interest and a dynamic chart regarding the time evolution of your amounts.
!!! Please note that in this post I used dummy amounts. It is just an example, you can replace them with your own data and use the model. The example is for Romania, but it can be adapted for other countries too.
- Input data in the yellow section and modify the income tax rate in column “P” if it’s the case:
- Analyze few indicators and a simple chart:
–> One interesting fact from this file is the following issue: as you can see in the “allData” image, if we worked for more companies in the same period, we can have multiple rows (duplicates) for the same year and month (please see in the first image, month 9 from 2014). This means that if we want to count the number of months in the “report” sheet, we cannot use a simple countif formula because that will count also the duplicate values. In the above example a countif will give the value 13 for year 2014. We could solve this issue with a pivot table , but this solution implies that the user will have to refresh it all the time.
–> This is why, to solve this in a dynamic and nice way, I used an Excel Array formula which counts only the Unique Values:
The formula from C4:
{=SUM(–(FREQUENCY(IF(allData!$C$2:$C$2000=report!B4,IF(allData!$E$2:$E$2000<>””,allData!$D$2:$D$2000)),allData!$D$2:$D$2000)>0))}
This array formula uses the Frequency function to count the frequency of each month in a given year and when it finds a value greater than 0 it puts TRUE, else it puts FALSE. Then, the double minus transforms the TRUES and FALSES into 1 and 0 and the Sum function sums all those values (1 and 0). So, with this, for year 2014 we get 12 months even if the month 9 appears twice in the “database”. This is a nice Excel trick 😉
Please note that all array formulas need to be confirmed with CTRL+SHIFT+ENTER in order to work. This gives the curly brackets which you see in the last image.
–>> Another nice feature from this file is that the Chart has a dynamic source data. If we add in the table also data for next years, the chart is automatically refreshed:
- This is made with dynamic named ranges:
You can download the Excel model and use it by replacing the dummy data with your own data from here:
