YTD budget formula based on current actual input

S

Sanj

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD
actual 10 20 30 40 30 130
budget 15 15 20 25 20 25 35 20 25 30 35 15 .......

Need a formula in YTD budget row so that when I enter 10 in Jun actual (140
YTD actual) so that I can get 120 in YTD budget. Thanks for the help.
Sanj
 
J

JoeU2004

Sanj said:
Need a formula in YTD budget row so that when I enter 10 in Jun actual
(140 YTD actual) so that I can get 120 in YTD budget.

One way:

For actual YTD: =SUM(B1:M1)

For budget YTD: =SUMPRODUCT(--(B1:M1<>""),B2:M2)


----- original message -----
 
S

smartin

Sanj said:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec YTD
actual 10 20 30 40 30 130
budget 15 15 20 25 20 25 35 20 25 30 35 15 .......

Need a formula in YTD budget row so that when I enter 10 in Jun actual (140
YTD actual) so that I can get 120 in YTD budget. Thanks for the help.
Sanj

Assume your example in A1:N3, try this in N3:
=SUM(OFFSET(B3,0,0,1,COUNT(B2:M2)))
 
J

Jacob Skaria

To always reflect the calculation upto current months.

Assuming the head Jan is in ColB

=SUM(INDIRECT("B" & ROW() & ":" & ADDRESS(ROW(),MONTH(TODAY())+1)))


If this post helps click Yes
 
A

Ashish Mathur

Hi,

You may also try this array formula (Ctrl+Shift+Enter) in cell N14

=SUM(B14:INDEX(B13:M14,2,MATCH("@",B13:M13&"@",0)-1))

B13:M13 has the actual figures. B14:M14 has the budget figures

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top