Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YTD budget formula based on current actual input
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YTD budget formula based on current actual input
"Sanj" wrote:
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 ----- "Sanj" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YTD budget formula based on current actual input
Sanj wrote:
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))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YTD budget formula based on current actual input
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 --------------- Jacob Skaria "Sanj" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YTD budget formula based on current actual input
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 "Sanj" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
YTD budget based on current input of actual | Excel Discussion (Misc queries) | |||
Compare budget to actual | Excel Discussion (Misc queries) | |||
Excel formula to calculate % difference when actual 1, budget -48 | Excel Discussion (Misc queries) | |||
Budget vs. Actual | Excel Discussion (Misc queries) | |||
YTD Budget Sum if Actual Month has activities | Excel Worksheet Functions |