Excel: Formula that would roll forward the amount (netting the amounts)


So, with a help from great excel experts in this forum, I was able to get the formula that would give me the results for STEP 1. Now, I need help for STEP 2. F or an example, I want ABC's Session 2 for Feb-17 to be the net value of from both Jan-17 and Feb-17, which would equal to ZERO. Then for Mar-17, ABC's Session 2 would become 12. Another example would be for ABC's Session 3 for Feb-17 would be 230, netting/accumulating both Jan and Feb. Then for Mar-17, it would become 242, all of Jan, Feb, and Mar. I feel like "<="&EOMONTH should be used somewhere, but not quite sure how and where I would insert it... Greatly appreciate any help!!!

[CENTER]A[/CENTER] [CENTER]B[/CENTER] [CENTER]C[/CENTER] [CENTER]D[/CENTER] [CENTER]E[/CENTER]
[CENTER]1[/CENTER] 17-Jan 17-Feb 17-Mar
[CENTER]2[/CENTER] ABC Session 1 -50
[CENTER]3[/CENTER] ABC Session 2 -15 15 12
[CENTER]4[/CENTER] ABC Session 3 30 200 12
[CENTER]5[/CENTER] BCD Session 1 400 -300
[CENTER]6[/CENTER] BCD Session 2 -50 100 150
[CENTER]7[/CENTER] BCD Session 3 -50
[CENTER]8[/CENTER] CDE Session 1 16 300
[CENTER]9[/CENTER] CDE Session 2 50 100 15
[CENTER]10[/CENTER] CDE Session 3 90 -10 5
[CENTER]A[/CENTER] [CENTER]B[/CENTER] [CENTER]C[/CENTER] [CENTER]D[/CENTER] [CENTER]E[/CENTER] [CENTER]F[/CENTER] [CENTER]G[/CENTER] [CENTER]H[/CENTER] [CENTER]I[/CENTER] [CENTER]J[/CENTER]
[CENTER]12[/CENTER] Input Sheet
[CENTER]13[/CENTER] [RIGHT]17-Jan[/RIGHT] [RIGHT]17-Feb[/RIGHT] [RIGHT]17-Mar[/RIGHT]
[CENTER]14[/CENTER] Session 1 Session 2 Session 3 Session 1 Session 2 Session 3 Session 1 Session 2 Session 3
[CENTER]15[/CENTER] ABC -15 30 -50 15 200 12 12
[CENTER]16[/CENTER] BCD 400 -50 100 -50 -300 150
[CENTER]17[/CENTER] CDE 16 50 90 300 100 -10 15 5
B15=INDEX($C$2:$E$10,MATCH($A15,$A$2:$A$10,0)+MATCH(B$14,$B$2:$B$4,0)-1,MATCH(B$13,$C$1:$E$1,0))


This question generated 19 answers. To proceed to the answers, click here.

This thread is current as of May 24, 2017.


For more resources for Microsoft Excel