Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with year to date comparison
Hello,
I have 2 sheets, one for 2008 and other for 2009. On each sheet I have monthly data ($) and a cell with the sum of all the months. On the 2009 sheet I will add a new figure monthly and this will add up the year total cell. next to this one there is a year to date 2008 for comparison. So what I need is that everytime that I type a number in a month on 2009, the year to date 2008 adds the same months... here the sample for better understanding... 2008 Revenue (USD)JUN JUL AUG SEPT OCT NOV DEC YTD 08 YTD 07 money 5 8 2 7 5 1 2 30 25 2009 Revenue (USD)JUN JUL AUG SEPT OCT NOV DEC YTD 09 YTD 08 money 5 6 4 15 15* YTD= year to date (*)Is there a formula that on YTD 08 adds the same months that already contain data on 2009 extracting that data from 2008?? and doest it everytime I add a number on a month in 2009? thanks please help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with year to date comparison
Hi,
Let' say you are pulling in the 2009 sheet totals from 2008 as described now assume that you have your 2009 from column A being A1= January and then in column N you have your 2008 data being N1=january, the amounts start in row 2 so in N2 you can enter =if(A2="","",sheet2008!A200) copy this formula to the left Sheet2008 is the sheet where you have your 2008 information A200 I assumed is where you have your total for January 2008 change the above to fit your needs if this helps please click yes thanks "danny" wrote: Hello, I have 2 sheets, one for 2008 and other for 2009. On each sheet I have monthly data ($) and a cell with the sum of all the months. On the 2009 sheet I will add a new figure monthly and this will add up the year total cell. next to this one there is a year to date 2008 for comparison. So what I need is that everytime that I type a number in a month on 2009, the year to date 2008 adds the same months... here the sample for better understanding... 2008 Revenue (USD)JUN JUL AUG SEPT OCT NOV DEC YTD 08 YTD 07 money 5 8 2 7 5 1 2 30 25 2009 Revenue (USD)JUN JUL AUG SEPT OCT NOV DEC YTD 09 YTD 08 money 5 6 4 15 15* YTD= year to date (*)Is there a formula that on YTD 08 adds the same months that already contain data on 2009 extracting that data from 2008?? and doest it everytime I add a number on a month in 2009? thanks please help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with year to date comparison
Thanks for the quick help,
That didn't work, is hard to explain but let me try again, In the 2009 sheet from A2 to L2 I have monthly totals, it adds automatically all months on cell M2, the same applies to the 2008 sheet. Then, on 2009 I need in cell N2 the sum for the same months that already have data in 2009, but from 2008. Let's say in 2009 I put the data for JUL, I need in cell N2 the sum from JAN to JUL 2008. Then when I put data on AUG 2009, N2 should give the SUM from JAN to AUG 2008 and so on. This way I get to campare the totals for the same months of each year, just by looking to N2 and M2. thanks "Eduardo" wrote: Hi, Let' say you are pulling in the 2009 sheet totals from 2008 as described now assume that you have your 2009 from column A being A1= January and then in column N you have your 2008 data being N1=january, the amounts start in row 2 so in N2 you can enter =if(A2="","",sheet2008!A200) copy this formula to the left Sheet2008 is the sheet where you have your 2008 information A200 I assumed is where you have your total for January 2008 change the above to fit your needs if this helps please click yes thanks "danny" wrote: Hello, I have 2 sheets, one for 2008 and other for 2009. On each sheet I have monthly data ($) and a cell with the sum of all the months. On the 2009 sheet I will add a new figure monthly and this will add up the year total cell. next to this one there is a year to date 2008 for comparison. So what I need is that everytime that I type a number in a month on 2009, the year to date 2008 adds the same months... here the sample for better understanding... 2008 Revenue (USD)JUN JUL AUG SEPT OCT NOV DEC YTD 08 YTD 07 money 5 8 2 7 5 1 2 30 25 2009 Revenue (USD)JUN JUL AUG SEPT OCT NOV DEC YTD 09 YTD 08 money 5 6 4 15 15* YTD= year to date (*)Is there a formula that on YTD 08 adds the same months that already contain data on 2009 extracting that data from 2008?? and doest it everytime I add a number on a month in 2009? thanks please help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with year to date comparison
Hi
Sorry for the delay =SUM(OFFSET(Sheet2!A2:E2,0,0,1,COUNT(F5:J5))) Sheet2!A2:E2 is the 2008 range you want to summarize from jan to dec change it to fit your needs count(F5:J5) is the 2009 range where you enter the information from jan to dec change it to fit your needs "danny" wrote: Thanks for the quick help, That didn't work, is hard to explain but let me try again, In the 2009 sheet from A2 to L2 I have monthly totals, it adds automatically all months on cell M2, the same applies to the 2008 sheet. Then, on 2009 I need in cell N2 the sum for the same months that already have data in 2009, but from 2008. Let's say in 2009 I put the data for JUL, I need in cell N2 the sum from JAN to JUL 2008. Then when I put data on AUG 2009, N2 should give the SUM from JAN to AUG 2008 and so on. This way I get to campare the totals for the same months of each year, just by looking to N2 and M2. thanks "Eduardo" wrote: Hi, Let' say you are pulling in the 2009 sheet totals from 2008 as described now assume that you have your 2009 from column A being A1= January and then in column N you have your 2008 data being N1=january, the amounts start in row 2 so in N2 you can enter =if(A2="","",sheet2008!A200) copy this formula to the left Sheet2008 is the sheet where you have your 2008 information A200 I assumed is where you have your total for January 2008 change the above to fit your needs if this helps please click yes thanks "danny" wrote: Hello, I have 2 sheets, one for 2008 and other for 2009. On each sheet I have monthly data ($) and a cell with the sum of all the months. On the 2009 sheet I will add a new figure monthly and this will add up the year total cell. next to this one there is a year to date 2008 for comparison. So what I need is that everytime that I type a number in a month on 2009, the year to date 2008 adds the same months... here the sample for better understanding... 2008 Revenue (USD)JUN JUL AUG SEPT OCT NOV DEC YTD 08 YTD 07 money 5 8 2 7 5 1 2 30 25 2009 Revenue (USD)JUN JUL AUG SEPT OCT NOV DEC YTD 09 YTD 08 money 5 6 4 15 15* YTD= year to date (*)Is there a formula that on YTD 08 adds the same months that already contain data on 2009 extracting that data from 2008?? and doest it everytime I add a number on a month in 2009? thanks please help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with year to date comparison
You just made my day, thank you so so so much!!!!
"Eduardo" wrote: Hi Sorry for the delay =SUM(OFFSET(Sheet2!A2:E2,0,0,1,COUNT(F5:J5))) Sheet2!A2:E2 is the 2008 range you want to summarize from jan to dec change it to fit your needs count(F5:J5) is the 2009 range where you enter the information from jan to dec change it to fit your needs "danny" wrote: Thanks for the quick help, That didn't work, is hard to explain but let me try again, In the 2009 sheet from A2 to L2 I have monthly totals, it adds automatically all months on cell M2, the same applies to the 2008 sheet. Then, on 2009 I need in cell N2 the sum for the same months that already have data in 2009, but from 2008. Let's say in 2009 I put the data for JUL, I need in cell N2 the sum from JAN to JUL 2008. Then when I put data on AUG 2009, N2 should give the SUM from JAN to AUG 2008 and so on. This way I get to campare the totals for the same months of each year, just by looking to N2 and M2. thanks "Eduardo" wrote: Hi, Let' say you are pulling in the 2009 sheet totals from 2008 as described now assume that you have your 2009 from column A being A1= January and then in column N you have your 2008 data being N1=january, the amounts start in row 2 so in N2 you can enter =if(A2="","",sheet2008!A200) copy this formula to the left Sheet2008 is the sheet where you have your 2008 information A200 I assumed is where you have your total for January 2008 change the above to fit your needs if this helps please click yes thanks "danny" wrote: Hello, I have 2 sheets, one for 2008 and other for 2009. On each sheet I have monthly data ($) and a cell with the sum of all the months. On the 2009 sheet I will add a new figure monthly and this will add up the year total cell. next to this one there is a year to date 2008 for comparison. So what I need is that everytime that I type a number in a month on 2009, the year to date 2008 adds the same months... here the sample for better understanding... 2008 Revenue (USD)JUN JUL AUG SEPT OCT NOV DEC YTD 08 YTD 07 money 5 8 2 7 5 1 2 30 25 2009 Revenue (USD)JUN JUL AUG SEPT OCT NOV DEC YTD 09 YTD 08 money 5 6 4 15 15* YTD= year to date (*)Is there a formula that on YTD 08 adds the same months that already contain data on 2009 extracting that data from 2008?? and doest it everytime I add a number on a month in 2009? thanks please help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine year over year date for comparison | Excel Worksheet Functions | |||
Monthly Comparison By Year | Charts and Charting in Excel | |||
Date comparison | Excel Discussion (Misc queries) | |||
Year-to-date year to date formula | Excel Worksheet Functions | |||
Showing data per month & year for comparison | Excel Discussion (Misc queries) |