Consider the revenue of a company for the last five year and you want to to know whether there is an upward, downward or no trend. How would you calculate the "optimal" percentage change?
I have an excel file and I would like to find the best guess for 1000 companies. In the end the number of upward and downward trends should be symmetric. My idea was to calculate the average of all percentage changes and use this number as the base for the trend estimate, do you have any good ideas?
What do you mean, symmetric? If you want half of the companies in your sample to have a negative "trend number" and half a positive, then compute the median revenue percentage increase over the last 5 years over all companies, and subtract this from every company's revenue increase percentage. Every company above the median increase will then have a positive "trend number" (relative to the median), every company below has a negative "trend number" (relative to median).
If you want the average "trend number" to be zero, then compute the average revenue increase over 5 years over all companies and subtract that average from every company's percentage increase. This would be the percentage point increase above (if positive) or below (if negative) the average percentage increase.