Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Year to date sum
I am bussy with a income statement with a date end of month in D1. My budget
is another worksheet with one column for each month. I am struggeling to figure out a sum formula combind with something like offset, so that excel automatily calculates the year to date budget total if I change the date on the incomestatment. Best regards |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Year to date sum
Try something like this:
With Budget data on Sheet2 Row_1 contains Category, Jan, Feb, etc...Dec Row_2 contains values: (AcctNum), (amt), (amt)....etc On Sheet1 D1: (a date....example: 6/1/2006) This formula sums the amounts from Row_2 on Sheet2 for Jan through the month referenced in D1 D2: =SUM(OFFSET(Sheet2!B2:M2,0,0,1,MONTH(Sheet1!$D$1)) ) If D1 contains a date in June, the formula sums from Jan through Jun Change D1 to a date in September and the formula sums from Jan through Sep Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Esrei" wrote: I am bussy with a income statement with a date end of month in D1. My budget is another worksheet with one column for each month. I am struggeling to figure out a sum formula combind with something like offset, so that excel automatily calculates the year to date budget total if I change the date on the incomestatment. Best regards |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Year to date sum
Dear Ron
Thank you for the reply. I am doing someting wrong because the formula sums up to May-07 but my date in D1 is 09/01/2006 My dates in IS Bud 2007! is in row2 I did check all my dates are formatted correctly. +SUM(OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1))) Thank you "Ron Coderre" wrote: Try something like this: With Budget data on Sheet2 Row_1 contains Category, Jan, Feb, etc...Dec Row_2 contains values: (AcctNum), (amt), (amt)....etc On Sheet1 D1: (a date....example: 6/1/2006) This formula sums the amounts from Row_2 on Sheet2 for Jan through the month referenced in D1 D2: =SUM(OFFSET(Sheet2!B2:M2,0,0,1,MONTH(Sheet1!$D$1)) ) If D1 contains a date in June, the formula sums from Jan through Jun Change D1 to a date in September and the formula sums from Jan through Sep Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Esrei" wrote: I am bussy with a income statement with a date end of month in D1. My budget is another worksheet with one column for each month. I am struggeling to figure out a sum formula combind with something like offset, so that excel automatily calculates the year to date budget total if I change the date on the incomestatment. Best regards |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Year to date sum
OK....let's analyze your formula:
=SUM(OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1))) This part: OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1)) Starts with the 12-column range $D$18:$O$18, offsets it by 0 rows, offsetss it by 0 columns, ensures that it is 1 row in height, and resizes the width to the number of columns related to the month number in D1. So... if $D$18:$O$18 contains the amounts for Jan_07 thru Dec_07 and the D1 date is 9/1/2006 (note: the year is irrelevent..only the month num is used) then the formula should sum from Jan_07 thru Sep_07 by effectively changing the range to $D$18:$L$18 (which is 9 cols wide). Does that help? *********** Regards, Ron XL2002, WinXP "Esrei" wrote: Dear Ron Thank you for the reply. I am doing someting wrong because the formula sums up to May-07 but my date in D1 is 09/01/2006 My dates in IS Bud 2007! is in row2 I did check all my dates are formatted correctly. +SUM(OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1))) Thank you "Ron Coderre" wrote: Try something like this: With Budget data on Sheet2 Row_1 contains Category, Jan, Feb, etc...Dec Row_2 contains values: (AcctNum), (amt), (amt)....etc On Sheet1 D1: (a date....example: 6/1/2006) This formula sums the amounts from Row_2 on Sheet2 for Jan through the month referenced in D1 D2: =SUM(OFFSET(Sheet2!B2:M2,0,0,1,MONTH(Sheet1!$D$1)) ) If D1 contains a date in June, the formula sums from Jan through Jun Change D1 to a date in September and the formula sums from Jan through Sep Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Esrei" wrote: I am bussy with a income statement with a date end of month in D1. My budget is another worksheet with one column for each month. I am struggeling to figure out a sum formula combind with something like offset, so that excel automatily calculates the year to date budget total if I change the date on the incomestatment. Best regards |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Year to date sum
Thank you, thank you
Now I completly understand offset, and my problem was that my financial year starts in Sep-06 Enjoy the day, you made somebodies life easier "Ron Coderre" wrote: OK....let's analyze your formula: =SUM(OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1))) This part: OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1)) Starts with the 12-column range $D$18:$O$18, offsets it by 0 rows, offsetss it by 0 columns, ensures that it is 1 row in height, and resizes the width to the number of columns related to the month number in D1. So... if $D$18:$O$18 contains the amounts for Jan_07 thru Dec_07 and the D1 date is 9/1/2006 (note: the year is irrelevent..only the month num is used) then the formula should sum from Jan_07 thru Sep_07 by effectively changing the range to $D$18:$L$18 (which is 9 cols wide). Does that help? *********** Regards, Ron XL2002, WinXP "Esrei" wrote: Dear Ron Thank you for the reply. I am doing someting wrong because the formula sums up to May-07 but my date in D1 is 09/01/2006 My dates in IS Bud 2007! is in row2 I did check all my dates are formatted correctly. +SUM(OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1))) Thank you "Ron Coderre" wrote: Try something like this: With Budget data on Sheet2 Row_1 contains Category, Jan, Feb, etc...Dec Row_2 contains values: (AcctNum), (amt), (amt)....etc On Sheet1 D1: (a date....example: 6/1/2006) This formula sums the amounts from Row_2 on Sheet2 for Jan through the month referenced in D1 D2: =SUM(OFFSET(Sheet2!B2:M2,0,0,1,MONTH(Sheet1!$D$1)) ) If D1 contains a date in June, the formula sums from Jan through Jun Change D1 to a date in September and the formula sums from Jan through Sep Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Esrei" wrote: I am bussy with a income statement with a date end of month in D1. My budget is another worksheet with one column for each month. I am struggeling to figure out a sum formula combind with something like offset, so that excel automatily calculates the year to date budget total if I change the date on the incomestatment. Best regards |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Year to date sum
I'm glad you figured it out.
Thanks for the feedback....much appreciated. *********** Regards, Ron XL2002, WinXP "Esrei" wrote: Thank you, thank you Now I completly understand offset, and my problem was that my financial year starts in Sep-06 Enjoy the day, you made somebodies life easier "Ron Coderre" wrote: OK....let's analyze your formula: =SUM(OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1))) This part: OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1)) Starts with the 12-column range $D$18:$O$18, offsets it by 0 rows, offsetss it by 0 columns, ensures that it is 1 row in height, and resizes the width to the number of columns related to the month number in D1. So... if $D$18:$O$18 contains the amounts for Jan_07 thru Dec_07 and the D1 date is 9/1/2006 (note: the year is irrelevent..only the month num is used) then the formula should sum from Jan_07 thru Sep_07 by effectively changing the range to $D$18:$L$18 (which is 9 cols wide). Does that help? *********** Regards, Ron XL2002, WinXP "Esrei" wrote: Dear Ron Thank you for the reply. I am doing someting wrong because the formula sums up to May-07 but my date in D1 is 09/01/2006 My dates in IS Bud 2007! is in row2 I did check all my dates are formatted correctly. +SUM(OFFSET('IS Bud 2007'!$D$18:$O$18,0,0,1,MONTH('Income Statement'!$D$1))) Thank you "Ron Coderre" wrote: Try something like this: With Budget data on Sheet2 Row_1 contains Category, Jan, Feb, etc...Dec Row_2 contains values: (AcctNum), (amt), (amt)....etc On Sheet1 D1: (a date....example: 6/1/2006) This formula sums the amounts from Row_2 on Sheet2 for Jan through the month referenced in D1 D2: =SUM(OFFSET(Sheet2!B2:M2,0,0,1,MONTH(Sheet1!$D$1)) ) If D1 contains a date in June, the formula sums from Jan through Jun Change D1 to a date in September and the formula sums from Jan through Sep Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Esrei" wrote: I am bussy with a income statement with a date end of month in D1. My budget is another worksheet with one column for each month. I am struggeling to figure out a sum formula combind with something like offset, so that excel automatily calculates the year to date budget total if I change the date on the incomestatment. Best regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
holiday dates | Excel Worksheet Functions | |||
How to return a value between date ranges | Excel Worksheet Functions | |||
Cell won't accept date past year 2009 | New Users to Excel | |||
How do I put in the date without the year? | Excel Discussion (Misc queries) | |||
dHow do I put in the date without the year? | Excel Discussion (Misc queries) |