Add quarterly data into columns

Navigation
1

I have the following question:

I have 5 columns ... each with quarterly information; 

Consolidado        31/12/2006   31/03/2007  30/06/2007  30/09/2007  31/12/2007 ....
Reserva de Lucros   12300         12300        646      646           33283  ...
Receita liquida     92479         61524        141850   255179        384120    ... 
Lucro antes jurEBIT 17403         7386          23136   -23           32695  ...
Resultado financeir -3072          829          5288    16615         36681  ...
Receitas Financ      1107          2735         7469    22613         43320  ...
LAIR                  14331        8215         28424   16592         69376 .....

I am looking for a function that gives me the sum of this information, and organize this sum in the matrix below, because I need the information in annual period:

That is, I want the function to feed the array below, with the sums already made: 

Consolidado        2006           2007         2008     2009          2010 ...
Reserva de Lucros   -               -          -        -             -     ...
Receita liquida     -               -          -        -             -  
Lucro antes jurEBIT -               -          -        -             -  
Resultado financeir -               -          -        -             -   
Receitas Financ     -               -          -        -             -      
LAIR                -               -          -        -             - ...

Is it possible?

    
asked by anonymous 25.03.2015 / 20:44

1 answer

3

Use the #

To make it easier, create a line (in my example I called it "Hidden") to contain only the year of each date in each column. You can use the SOMASE " to extract only the year, as number instead of date. This line is just to facilitate the rest of the calculation and can be hidden from the user by selecting the entire line by right-clicking on the line and choosing "Hide":

Havingthislineovertheyears,simplyusetheANOfunctiontoaddthevalueofeachrow(Consolidated,Reserve,Revenue,Profit,etc.)onlytothecolumnyear,inanew"table" (which in the example I put below the original.) The function call has the following syntax: 

=SOMASE(REGIÃO_DE_TESTE; CRITÉRIO_DE_TESTE; REGIÃO_DE_SOMA)

So, for example, to calculate the total profit for 2007, the formula looks like this: 

=SOMASE($B$2:$F$2;C$11;$B5:$F5)

As the image below:

Iusedthedollarsign(SOMASE)tosettherows/columnsthatdonotchangeastheformulaiscopiedandpasted.

Thesamplefile can be downloaded from 4Shared here .

    
26.03.2015 / 13:15
Undefined reference compiling with g ++ phpMyAdmin with slow Xdebug in Windows 8.1 with Xampp