Ytd balance

[Jarvin24]

Hi Guys,

I was hoping you could help me solve my problem if its possible.

I have COLUMN B:M (BUDGET for JAN to DEC) and COLUMN N:Y (ACTUALS for JAN to DEC).

a. The figures in COLUMN B:M is fixed and was predetermined at the beginning of the year.
b. The Actuals are the one that change based on the DATA given for the month.
c. YTD BALANCE or COLUMN Z's formula should be like this.
SUM OF THE REMAINING MONTHS FOR BUDGET + SUM OF YEAR TO DATE OF ACTUALS
Examples:
(SUM OF FEB TO DEC) + (JAN ACTUALS) = YTD BALANCE
(SUM OF MAR TO DEC) + (JAN + FEB ACTUALS) = YTD BALANCE
(SUM OF APR TO DEC) + (JAN + FEB + MAR ACTUALS) = YTD BALANCE
ETC ETC....

I hope its possible because I have a lot of sheets per DEPARTMENT and every month I move the formula.

Thanks in advance. ^_^

[JosephP]

can your actuals have a valid 0 value? can you clear the actuals cells completely instead of populating with 0 where the data is not available yet? or maybe use an entry cell for the current month?

[Jarvin24]

I have one solution.. eheheh.. but with simple TRICK.. I put 1 on TOP of the MONTHS and just use SUMIF..

That is the best I can do.. LOL

Hope you have some better...

[Jarvin24]

Hi Joseph...

Thanks for showing interest.. Uhm, the figures are actually on the PIVOT TABLE.. but its fixed...

I just put it on that way for example

[Jarvin24]

But assuming its BLANK... is it possible?

[shukla.ankur281190]

Try

Z3=SUMPRODUCT((OFFSET($N$2,1,,,MATCH(LOOKUP(2,1/(N3:Y3>0),N3:Y3),N3:Y3,0))))+SUMPRODUCT((OFFSET($A$2,1,MATCH(LOOKUP(2,1/(N3:Y3>0),N3:Y3),N3:Y3,0)+1,,12-MATCH(LOOKUP(2,1/(N3:Y3>0),N3:Y3),N3:Y3,0))))

Please Login or Register  to view this content.

[samba_ravi]

=sum(b3:y3)-sumproduct((b3:m3)*(n3:y3>0))

[Jarvin24]

Its working!.. Can I drag it down? because there are a lot of GL ACCOUNT and each GL account has budget...
lets say 10002000 is under column 4

[JosephP]

if you never have 0 as a valid actual value
=SUM(N3:Y3)+SUMIF(N3:Y3,0,B3:M3)
or if you can make the cells blank
=SUM(N3:Y3)+SUMIF(N3:Y3,"",B3:M3)

[Jarvin24]

Super Thanks!!!... its working!