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

 

[AhoyNC]

Maybe something like this.
These are array formulas and must be entered with CTRL-SHIFT-ENTER.

Formula in C76 will return last value from Dec. if the month in C75 = 1.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
24		12/3/2015	12/31/2015	1/1/2016	1/5/2016	2/2/2016	2/8/2016	2/25/2016	3/2/2016	3/5/2016	3/20/2016	3/28/2016
25
59
60		1	2	3	4	5	6	7	15	9	12	11
74
75	Month	2
76	End of last Month	4
77	End of Month from C75	7
78
Logg

 

[Jarke]

Hi m8, thanks for you effort and great reply, looks terrific! I got some problems tho with entering your formula.. I know that it should be arrays. But i got alot of errors in the code, i got excel 2010. And i always have problems with other peoples yet correct formulas, i think my excel don't like comma ",".

I took away the arrays and let excel show me where there was problems, and it wanted to change all the commas ",". So i switched all these to ";". This worked on your C77!

The furthest i came on the C76 is that the cell says #NAME?. I also know that "*" has worked instead of comma "," before. Any idéa on how to retype the big formula C76?

Thanks again, and great work!

 

[AhoyNC]

See if this works (changed "," to ";").

=IFNA(INDEX(Logg!$C$60:$BN$60;MATCH(MAX(IF(MONTH(Logg!$C$24:$BN$24)=C75-1;IF(YEAR(Logg!$C$24:$BN$24)=2016;Logg!$C$24:$BN$24)));Logg!$C$24:$BN$24,0));INDEX(Logg!$C$60:$BN$60;MATCH(MAX(IF(MONTH(Logg!$C$24:$BN$24)=12;IF(YEAR(Logg!$C$24:$BN$24)=2016-1;Logg!$C$24:$BN$24)));Logg!$C$24:$BN$24,0)))

 

[Jarke]

No it does not work, it doesn't like the comma before the zeroes either, and changing them to ";" does not help(Cell just shows #NAME?). Frustrating >_<

 

[AhoyNC]

See what this gives you. Your month in C75 will have to be 2 or greater (it will fail if month is 1 as C75-1 would equal 0). Also, make sure all the cell references are correct.

=INDEX(Logg!$C$60:$BN$60;MATCH(MAX(IF(MONTH(Logg!$C$24:$BN$24)=C75-1;IF(YEAR(Logg!$C$24:$BN$24)=2016;Logg!$C$24:$BN$24)));Logg!$C$24:$BN$24,0))

 

[Jarke]

It worked! Just changed the comma before the zero in the end to ";". Thanks alot! This would have taken me days, be well friend.

PS. I now figured out a very easy wait to solve the problem, as i have every month in a columm, 1,2,3,4,5.... i could just change the C77 formula, and add =C74 instead of C75.

Best regards

 

[AhoyNC]

You're welcome. Glad you got it to work. Thanks for the feedback.

 

[Jarke]

Hi! I'm going to do the same with weekly now, should be easier i suppose. I want the weekly change, so last bank value from last week and the last this week should do the trick.

Here's the code to sum the weekly result, =SUMPRODUCT((YEAR(Logg!$C$24:$BN$24)=2016)*(Logg!$C$63:$BN$63=B36)*(Logg!$C$60:$BN$60<>0)*(Logg!$C$60:$BN$60))

Daily date is in row 24, result is in row 60, the dates weekly number is in row 63, bank value after each transaction is in row 65.

Hope you can help me! Should probably be something like the monthly code you made.

Thanks, best regards

 

[AhoyNC]

Here is one way.
Also, instead of hard coding in the year 2016, I would probably put this into a cell and reference it. That way if your data goes over multiply years you can just change the one cell and not have to change all the formulas.
As always change cell references to match your data.

These are array formulas and must be entered with

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
24		Date	12/3/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
60			1	2	3	4	5	-6	7	15	9	12	20	11	25
61
62
63		Week	49	53	1	2	6	6	6	8	10	10	11	11	11
64
65		Bank	1	2	3	4	5	-6	7	15	9	12	20	11	25
66
67		Week #	11
68		Last Week	12
69		This Week	25
CTRL-SHIFT-ENTER.