Values based on the last day of each month (Excel)

Navigation
1

Edited:

Personal, this is my Excel file. I want the last value of each month of column A to be referenced by column C, that is, for the month and year of each row of column A I must find the value of what would be referring to the last day of that same month and year, value that is in this row in column D ..

Why PROCV do I do? 

 A           B      C          D
30/12/1988  1,8085  30/12/1988  1,8027
31/01/1989  1,7500  31/01/1989  1,7479
28/02/1989  1,7440  28/02/1989  1,739101
31/03/1989  1,6852  31/03/1989  1,6819
28/04/1989  1,6890  28/04/1989  1,6858
31/05/1989  1,5715  31/05/1989  1,564201
30/06/1989  1,5490  30/06/1989  1,543899
31/07/1989  1,6660  31/07/1989  1,6583
31/08/1989  1,5730  31/08/1989  1,5617
29/09/1989  1,6145  28/09/1989  1,605299
31/10/1989  1,5775  27/10/1989  1,5723
30/11/1989  1,5695  30/11/1989  1,5628
29/12/1989  1,6145  29/12/1989  1,6031
31/01/1990  1,6794  31/01/1990  1,6695
28/02/1990  1,6892  28/02/1990  1,667699
30/03/1990  1,6480  30/03/1990  1,6384
30/04/1990  1,6389  30/04/1990  1,632197
31/05/1990  1,6765  31/05/1990  1,668499
29/06/1990  1,7450  29/06/1990  1,739399
31/07/1990  1,8600  31/07/1990  1,852199
31/08/1990  1,8920  31/08/1990  1,8844
28/09/1990  1,8735  27/09/1990  1,8693
31/10/1990  1,9440  31/10/1990  1,934101
30/11/1990  1,9392  28/11/1990  1,9588
31/12/1990  1,9285  31/12/1990  1,9192
31/01/1991  1,9650  31/01/1991  1,9564
28/02/1991  1,9100  28/02/1991  1,903115
29/03/1991  1,7485  28/03/1991  1,732099
30/04/1991  1,7225  30/04/1991  1,7174
31/05/1991  1,6980  31/05/1991  1,690332
28/06/1991  1,6180  28/06/1991  1,614698
31/07/1991  1,6845  31/07/1991  1,679599
30/08/1991  1,6800  30/08/1991  1,6743
30/09/1991  1,7520  30/09/1991  1,7436
31/10/1991  1,7425  31/10/1991  1,7324
29/11/1991  1,7655  29/11/1991  1,759256
31/12/1991  1,8660  31/12/1991  1,860501
31/01/1992  1,7890  31/01/1992  1,7783
28/02/1992  1,7560  28/02/1992  1,7491
31/03/1992  1,7363  31/03/1992  1,7272
30/04/1992  1,7750  30/04/1992  1,766502
29/05/1992  1,8290  29/05/1992  1,8206
30/06/1992  1,9035  30/06/1992  1,894701
31/07/1992  1,9200  31/07/1992  1,916501

...

Any help / intuition?

Thank you.

    
asked by anonymous 24.06.2016 / 00:06

2 answers

1

Diogo, resolve it :

In the F column, place the formula below for each row in your table 

=DATA(SE(MÊS(A1)=12;ANO(A1)+1;ANO(A1));SE(MÊS(A1)=12;1;MÊS(A1)+1);1)-1

It takes the month and year from the A column of the respective line (in this case, line 1) and last date of the corresponding month .

For example: 

dez/88         31/12/1988

jan/89         31/01/1989

Place the formula below in the E column and you're done! 

=PROCV(F1;C1:D80;2;VERDADEIRO)
The TRUE causes it to pause the search in the exact answer or the value immediately preceding a value greater than the sought (complicated, right?), if there is no exact answer .

If you need to treat the search, use SE with the same PROCV inside, and if it does, repeat the PROCV in the response part correct for it by the value found by PROCV , otherwise, put " white " or a message that did not find the corresponding date on the non-genuine part of the response (from the SE command).

Put the appropriate search strip for you!

Have you worked as a friend?

    
24.06.2016 / 20:28
1

Speak Diogo!

I do not know if I understand very well what you want. See if I get it right:
Do you want the value of E1 (E2, E3, E * and etc) to always be the last value of D1 (D2, D3, D * and etc)?

If this is the case, simply enter the formula in the column E1 (E2, E3, E * and etc):

  

= right (D1,1)
  = right (D2,1)
  = right (D3,1)
  = right (D *, 1)
  etc.

If you want the value of E1 (E2, E3, E * and etc.) to always be the value of the cell above, just change "D2" to "D1". But you would have to start from the second line.

  

= right (D1,1) error
  = right (D1,1)
  = right (D2,1)
  = right (D *, 1)
  etc.

If you have a list of the months 10/88 values; Jan / 89; Feb / 89; and etc., then you will need procv on the sheet of each month and use the larger () (or max ()) function in a column next to it and point to your list.

  

= VLOOKUP (A1; 'Page2'! A: E; 5; 0) " this formula enters E1 'Aba 1' "
  = MAX (D: D) " this formula would be in 'Aba 2' next to the value D1 (for example)

Where you selected an area from A to E, where in E should be placed the MAX () of column D;
See: link

To see the ENG and PT excel formulas conversion list:
link

Is it any of the three options? Otherwise, hey! :)

    
24.06.2016 / 02:52
Change time in sql server 2014 How does the "hover" event behave on "touch" devices?