Reference to row at beginning of month?

[Carson Dyle]

I have worksheets that record a value for something taken on a daily basis, one row per day. On the first of each month I want to know the difference (gain or loss) from the first of the previous month. How much did my IRA grow, how much weight did I lose, etc.

This is the sort of formula I use now. Each month it requires me to manually editing the row number of the 1st of the month. Here, the date is in column C, the value in column D. "If it's the 1st of the month, display the difference, otherwise display nothing."

=IF(AND(DAY($C205)=1, $D205>0), $D205-$D$175, "")

How would I construct a formula that could do this automatically? In the formula above, the '175' would need to be the current row number minus the number of days in the previous month.

Part 2: More complex (to my thinking) would be to do it on a daily basis, as you have to figure the offset from the row containing the value for the 1st of the current month.

[mehmetcik]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AliGW]

This might work well enough for you:

=D205-VLOOKUP(EDATE(C205,-1),C:D,2,0)

This will take the number from exactly one month ago away from today's number.

[José Augusto]

Try this in E2 and copy down

Please Login or Register  to view this content.

[Carson Dyle]

This might work well enough for you:

=D205-VLOOKUP(EDATE(C205,-1),C:D,2,0)

This will take the number from exactly one month ago away from today's number.

Perfect. Thank you.

[AliGW]

Glad to have helped!