|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 18th, 2010, 04:57 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Include YTD calculations?
Hello,
I have built a table with formulas similar to this. I need assistance on how I can create a YTD table based on what Period I select. =SUMPRODUCT(('2010 AOPII with Benefits'!$A$1:$A$248=F$27)*('2010 AOPII with Benefits'!$E$1:$Q$1=$C$3)*('2010 AOPII with Benefits'!$B$1:$B$248=$E28),'2010 AOPII with Benefits'!$E$1:$Q$248)-SUMPRODUCT(('2010 Actuals'!$A$1:$A$302=F$27)*('2010 Actuals'!$E$1:$Q$1=$C$3)*('2010 Actuals'!$B$1:$B$302=$E28),'2010 Actuals'!$E$1:$Q$302) NOTE: C3 is the Period that I choose from drop down selection. Any help is appreciated. 
|
|
#3
May 18th, 2010, 06:09 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Include YTD calculations?
Hello Don,
I will do my best to explain. Can't send via email as it contains confidential information. B / (W) P5 CATEGORY NEDC SEDC SCDC SWDC RENT - BUILDING -4,329.58 34.50 1,340.00 6,703.81 RENT - EQUIPMENT 375.20 5,071.79 1,802.42 352.82 COMPUTER EXP / DATA COM -104.00 6.00 -105.00 138.00 UTILITIES 2,371.20 -379.00 -7,011.22 3,213.88 Here is a portion of a table that I use. Under the numbers is from two other tables (AOP & Actuals). I use sumproduct to look for each category (Rent) AND for each location (NEDC) AND what Period (P5), this is a drop down for all periods. This is working just fine for me. Now, I would like to use this format to encompass YTD numbers. The layout of AOP & Actuals tables are similar to it, with the exception that the columns are by Period. If I select P5, I would like to see numbers from P1-P5. The formula below is what is behind the numbers I used as stated above. Hope this helps. If not, let me know and I will try another method. Thanks again. "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "MrRJ" wrote in message ... Hello, I have built a table with formulas similar to this. I need assistance on how I can create a YTD table based on what Period I select. =SUMPRODUCT(('2010 AOPII with Benefits'!$A$1:$A$248=F$27)*('2010 AOPII with Benefits'!$E$1:$Q$1=$C$3)*('2010 AOPII with Benefits'!$B$1:$B$248=$E28),'2010 AOPII with Benefits'!$E$1:$Q$248)-SUMPRODUCT(('2010 Actuals'!$A$1:$A$302=F$27)*('2010 Actuals'!$E$1:$Q$1=$C$3)*('2010 Actuals'!$B$1:$B$302=$E28),'2010 Actuals'!$E$1:$Q$302) NOTE: C3 is the Period that I choose from drop down selection. Any help is appreciated. .
|
|
#4
May 18th, 2010, 06:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Include YTD calculations?
I do not have time to re-create your file. If you can't provide dummy info
then I will be unable to assist. -- Don Guillett Microsoft MVP Excel SalesAid Software "MrRJ" wrote in message news  Hello Don, I will do my best to explain. Can't send via email as it contains confidential information. B / (W) P5 CATEGORY NEDC SEDC SCDC SWDC RENT - BUILDING -4,329.58 34.50 1,340.00 6,703.81 RENT - EQUIPMENT 375.20 5,071.79 1,802.42 352.82 COMPUTER EXP / DATA COM -104.00 6.00 -105.00 138.00 UTILITIES 2,371.20 -379.00 -7,011.22 3,213.88 Here is a portion of a table that I use. Under the numbers is from two other tables (AOP & Actuals). I use sumproduct to look for each category (Rent) AND for each location (NEDC) AND what Period (P5), this is a drop down for all periods. This is working just fine for me. Now, I would like to use this format to encompass YTD numbers. The layout of AOP & Actuals tables are similar to it, with the exception that the columns are by Period. If I select P5, I would like to see numbers from P1-P5. The formula below is what is behind the numbers I used as stated above. Hope this helps. If not, let me know and I will try another method. Thanks again. "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "MrRJ" wrote in message ... Hello, I have built a table with formulas similar to this. I need assistance on how I can create a YTD table based on what Period I select. =SUMPRODUCT(('2010 AOPII with Benefits'!$A$1:$A$248=F$27)*('2010 AOPII with Benefits'!$E$1:$Q$1=$C$3)*('2010 AOPII with Benefits'!$B$1:$B$248=$E28),'2010 AOPII with Benefits'!$E$1:$Q$248)-SUMPRODUCT(('2010 Actuals'!$A$1:$A$302=F$27)*('2010 Actuals'!$E$1:$Q$1=$C$3)*('2010 Actuals'!$B$1:$B$302=$E28),'2010 Actuals'!$E$1:$Q$302) NOTE: C3 is the Period that I choose from drop down selection. Any help is appreciated. .
|
|
#5
May 18th, 2010, 08:33 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Include YTD calculations?
Thanks Don. I wasn't asking to re-create the file. I was merely asking for
a formula that I can use in conjuction with my sumproduct to come up with YTD figures. I will try this on my own. "Don Guillett" wrote: I do not have time to re-create your file. If you can't provide dummy info then I will be unable to assist. -- Don Guillett Microsoft MVP Excel SalesAid Software "MrRJ" wrote in message news Hello Don, I will do my best to explain. Can't send via email as it contains confidential information. B / (W) P5 CATEGORY NEDC SEDC SCDC SWDC RENT - BUILDING -4,329.58 34.50 1,340.00 6,703.81 RENT - EQUIPMENT 375.20 5,071.79 1,802.42 352.82 COMPUTER EXP / DATA COM -104.00 6.00 -105.00 138.00 UTILITIES 2,371.20 -379.00 -7,011.22 3,213.88 Here is a portion of a table that I use. Under the numbers is from two other tables (AOP & Actuals). I use sumproduct to look for each category (Rent) AND for each location (NEDC) AND what Period (P5), this is a drop down for all periods. This is working just fine for me. Now, I would like to use this format to encompass YTD numbers. The layout of AOP & Actuals tables are similar to it, with the exception that the columns are by Period. If I select P5, I would like to see numbers from P1-P5. The formula below is what is behind the numbers I used as stated above. Hope this helps. If not, let me know and I will try another method. Thanks again. "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "MrRJ" wrote in message ... Hello, I have built a table with formulas similar to this. I need assistance on how I can create a YTD table based on what Period I select. =SUMPRODUCT(('2010 AOPII with Benefits'!$A$1:$A$248=F$27)*('2010 AOPII with Benefits'!$E$1:$Q$1=$C$3)*('2010 AOPII with Benefits'!$B$1:$B$248=$E28),'2010 AOPII with Benefits'!$E$1:$Q$248)-SUMPRODUCT(('2010 Actuals'!$A$1:$A$302=F$27)*('2010 Actuals'!$E$1:$Q$1=$C$3)*('2010 Actuals'!$B$1:$B$302=$E28),'2010 Actuals'!$E$1:$Q$302) NOTE: C3 is the Period that I choose from drop down selection. Any help is appreciated. . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
