Find first and last value of month, to calculate monthly change

[Jarke]

Hello folks, need som help with finding value on a certain date to calculate monthly change!

I want to calculate the percentage changed every month, and so i got transactions every month, but not always is it the first or last in the month, to find right dates? could perhaps go from last transaction previous month to last transaction this month to get what happened within the month.

This is my code to sum what happened during the month.
=SUMPRODUCT((MONTH(Logg!$C$24:$BN$24)=C75)*(YEAR(Logg!$C$24:$BN$24)=2016)*(Logg!$C$60:$BN$60))

And obviously i need to subtract or add the ingoing "bankaccount" with the transaction sum of the month, and then divide this with the ingoing bankaccount value to get the percentage changed.

Nuff said, i also have the bank value being changed with each transaction and date, and i want a code to find the right date, and thereby the right value.

Ooor, as i have the "bankvalue" after each transaction, a code that will find the first logged bankvalue of the month and the last, this could do the trick. But how will i code this?

Hope i made it clear enought, please ask otherwise

Best regards

 

[Jarke]

Got the C69 to work! Thanks again friend. I have som problem with the C68 tho, i got it to work, but it adds all the bank values in last week.. Don't know how to fix it, here's my edited version of C68:

{=IF(B37>1;SUMPRODUCT((YEAR(Logg!$C$24:$BN$24)=2016)*(MAX(IF(Logg!$C$63:$BN$63=B37-1;Logg!$C$24:$BN$24))=Logg!$C$24:$BN$24)*(Logg!$C$65:$BN$65));SUMPRODUCT((MAX(IF(YEAR(Logg!$C$24:$BN$24)=2016-1;Logg!$C$24:$BN$24))=Logg!$C$24:$BN$24)*Logg!$C$65:$BN$65))}

So instead of returning the last value, i gain all the weeks bank values summed up.

But if i don't hardcode 2016, and just change a cell with 2016 to 2017, all my previous formulas would change and use 2017 instead of the last year 2016. Don't want to mess with old formulas. I'm probably wrong, maybe you can lock that cell in old formulas with F4?? :O Atleast i drag all formulas so it's not so much work to change a few to 2017.

Cheers

 

[AhoyNC]

I used the formula you posted above (just changed the ";" to "," for my ver. of Excel) and it worked.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
24		Date	12/30/2016	12/31/2015	1/1/2016	1/5/2016	2/2/2016	2/3/2016	2/5/2016	2/16/2016	3/2/2016	3/5/2016	3/6/2016	3/7/2016	3/9/2016
25
36
37	Week #	1
59
60			1	2	3	4	5	-6	7	15	9	12	20	11	25
61
62
63		Week	53	53	1	2	6	6	6	8	10	10	11	11	11
64
65		Bank	15	44	3	4	5	-6	7	15	9	12	20	11	25
66
67
68		Last Week	44
Sheet

 

[Jarke]

I figured out the problem! My formula and your formula works, it's just that i have several transactions in the same day and in the same week(so it just adds/sums these). How could we go around this problem? I do note the time when each happens, but that seems tricky? Maybe just choose the last added(the one on the right, everything happpens after another.

Entry clock for transaction is in row 25

You'll probably know what is best, thanks again for the help

 

[AhoyNC]

If the data is row 25 are actual time values then you could add the time to the dare.
You will need to change both formulas (I assume you could have the same issue with 2016 data).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
24		Date	12/31/2015	12/31/2015	1/1/2016	1/5/2016	2/2/2016	2/3/2016	2/5/2016	2/16/2016	3/2/2016	3/5/2016	3/6/2016	3/7/2016	3/9/2016
25		Time	10:34 AM	11:00 AM
36
37	Week #	1
59
60			1	2	3	4	5	-6	7	15	9	12	20	11	25
61
62
63		Week	53	53	1	2	6	6	6	8	10	10	11	11	11
64
65		Bank	15	25	3	4	5	-6	7	15	9	12	20	11	25
66
67
68		Last Week	25
69		This Week	3
Sheet

 

[Jarke]

Changed it some but got it to work! Thanks alot again! You are a real timesaver.

What is the difference between C68 and C69? Why not just change the week in C69 to B36?

 

[AhoyNC]

In C68 we're looking for the pervious ending week balance. So, B37-1 is looking at the value you entered in B37 and subtracting 1. If you enter 11 in B37 the formula in C68 subtracts 1 and looks for the last value in week 10. We also have to check to see if you entered week 1 in B37 in which case we use another formula to get the last value from the year before.

Formula in C69 is just looking to return the last value for the week you enter in B37.

 

[Jarke]

I see. From B36 and down, B37,B38,B39..... i have week 31,32,33,34... That is why i thought that the change in formula C69 would work.

I see. Thanks alot!

 

[AhoyNC]

It may work then. I just didn't know how your actual data was set up.

 

[Jarke]

Yes, that's my fault, all credd to you tho! Great work and thanks again.