YTD Formula

[Neilesh Kumar]

Dear Experts,

Please find here attached workbook, where i am seeking for your precious expertise on YTD formula in Cell R3 for Opening Balance (Opening Balance will always be the first month Opening Balance for the particular Quarter, Suppose Feb//17 is falling uner 1Q//17 then as per results in 1Q//17, Opening Balance will be for YTD Feb.), R4 for total Addition upto Feb//17 & R5 for Total Due upto Feb//17.

Request to you please do provide the solution on the same.

Regards,

Neilesh

[samba_ravi]

Why due is 1000
I think it would be 1000+1000=2000
If you are looking for YTD Nov then what would be the result
Opening Balance is Jan month(1st QTR) opening balance or it is Opening Balance of Oct (4th QTR)

[Neilesh Kumar]

Why due is 1000
I think it would be 1000+1000=2000
If you are looking for YTD Nov then what would be the result
Opening Balance is Jan month(1st QTR) opening balance or it is Opening Balance of Oct (4th QTR)

Yes Sir, You are absolutely perfect. Request to you kindly do provide the required solution Sir.

Thank you for your precious phase and valuable support.

Regards,

Neilesh

[samba_ravi]

r3=OFFSET($B3,,INT((MONTH(DATEVALUE(1&RIGHT(R$1,3)))-1)/3)*4)
r4=SUM(OFFSET($B4,,INT((MONTH(DATEVALUE(1&RIGHT(R$1,3)))-1)/3)*4,,MOD(MONTH(DATEVALUE(1&RIGHT(R$1,3)))-1,3)+1))
r5=SUM(OFFSET($B5,,INT((MONTH(DATEVALUE(1&RIGHT(R$1,3)))-1)/3)*4,,MOD(MONTH(DATEVALUE(1&RIGHT(R$1,3)))-1,3)+1))

[Neilesh Kumar]

r3=OFFSET($B3,,INT((MONTH(DATEVALUE(1&RIGHT(R$1,3)))-1)/3)*4)
r4=SUM(OFFSET($B4,,INT((MONTH(DATEVALUE(1&RIGHT(R$1,3)))-1)/3)*4,,MOD(MONTH(DATEVALUE(1&RIGHT(R$1,3)))-1,3)+1))
r5=SUM(OFFSET($B5,,INT((MONTH(DATEVALUE(1&RIGHT(R$1,3)))-1)/3)*4,,MOD(MONTH(DATEVALUE(1&RIGHT(R$1,3)))-1,3)+1))

Thank you so much Dear Expert, It worked perfectly. Thank you once again.

Regards,

Neilesh