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

Question

Edited:

Guys, 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 month and year, taking the value that is in this row in column D..

By PROCV I can 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

...

Some help/intuition?

Thank you.

Answer 1

Diogo, sort it out like this:

In the column F put the formula below for each row of 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

She takes the month and year column To line (in this case line 1) and generates the last date of the corresponding month.

For example:

dez/88         31/12/1988

jan/89         31/01/1989

Put the formula below in the column And and ready!

=PROCV(F1;C1:D80;2;VERDADEIRO)

The TRUE causes him stop the search on the exact answer or the value immediately prior to a value higher than the one sought (complicated, right?), if there is no exact answer.

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

Place the appropriate search strip for you!

Worked buddy?

Answer 2

Fala Diogo!

I’m not sure I understand very well what you want. See if I got it right:
You want the value of E1 (E2,E3,E* and so on) to always be the last value of D1 (D2,D3,D* and so on)?

If this is the case, simply insert in column E1 (E2,E3,E* and so on) the formula:

=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), always be the value of the above cell, just change "D2" to "D1". But you’d 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 values of the months ten/88; Jan/89; Feb/89; and etc, then you will need the procv in the sheet of each month and use the higher function() (or max()) in a column beside and point to your list.

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

Where you went select an area from A to E, where E should be placed the MAX() of column D;
Behold: https://goo.gl/tQwC29

To see the ENG and PT excel formulas conversion list:
https://usuariosdoexcel.wordpress.com/lista-de-funcoes-do-excel/

By any chance is one of the three options ? Otherwise, speak and! :)