|
Specify a column by the month to calculate it's data
I have 12 columns (Jan-Dec) for 2007 and then 12 more columns (Jan-Dec) for
2008. At the end of each years section of monthly columns I have a Month to Date Difference column that compares the accumulated months for 2007 to the same number of months for 2008. I need a way for the formula in the MTD column to know what month I want it to calculate to. If I am in Feb I only want it to use the 1st 2 months for calculations by checking on the date or month I enter at the top of the MTD column. Currently I edit the formulas so it only includes the months columns I want. I would like this to be automatic. A simplified example with just 3 months is shown below: I want the formula to check the month I type into cell G1 and calulate up to the month that matches what I typed in. The 1st example shows a 2 in G4 because that is the diff between Jan & Feb 2007 and Jan & Feb 2008. The second example shows 3 in G4 because that is the Diff between Jan, Feb, Mar 2007 and Jan, Feb Mar 2008: A B C D E F G 1 Feb 2 2007 2007 2007 2008 2008 2008 MTD 3 Jan Feb Mar Jan Feb Mar DIFF 4 2 2 2 3 3 3 2 A B C D E F G 1 Mar 2 2007 2007 2007 2008 2008 2008 MTD 3 Jan Feb Mar Jan Feb Mar DIFF 4 2 2 2 3 3 3 3 Thank you |
Specify a column by the month to calculate it's data
If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in M1-X1... ...and values below in row 2 for each month noted above. For now, place a text entry like Mar in A4. Now this INDIRECT formula will compare the two ranges based on the text entry "Mar" =SUM(INDIRECT("A2:"&CHOOSE(B4,"A","B","C","D","E", "F", "G","H","I","J","K","L")&"2"))-SUM(INDIRECT("M2:"&CHOOSE(B4, "M","N","O","P","Q","R","S","T","U","V","W","X")&" 2")) Be sure to copy that as a single row, not the 3 rows the forum will make it into. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62175 |
Specify a column by the month to calculate it's data
Sorry, I used an intermediate helper cell in B4 to convert the "Mar" to a 3,
here is the long formula with that operation buried in the formula itself. Sorry for the omission. =SUM(INDIRECT("A2:"&CHOOSE(MATCH(A4,A1:L1,0), "A","B","C","D","E","F","G","H","I","J","K","L ")&" 2"))- SUM(INDIRECT("M2:"&CHOOSE(MATCH(A4,A1:L1,0), "M","N","O","P","Q","R","S","T","U","V","W","X ")&" 2")) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in M1-X1... ...and values below in row 2 for each month noted above. For now, place a text entry like Mar in A4. Now this INDIRECT formula will compare the two ranges based on the text entry "Mar" =SUM(INDIRECT("A2:"&CHOOSE(B4,"A","B","C","D","E", "F", "G","H","I","J","K","L")&"2"))-SUM(INDIRECT("M2:"&CHOOSE(B4, "M","N","O","P","Q","R","S","T","U","V","W","X")&" 2")) Be sure to copy that as a single row, not the 3 rows the forum will make it into. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62175 |
Specify a column by the month to calculate it's data
I get a #REF! error. Does it matter what cell this formula goes in?
"JBeaucaire" wrote: Sorry, I used an intermediate helper cell in B4 to convert the "Mar" to a 3, here is the long formula with that operation buried in the formula itself. Sorry for the omission. =SUM(INDIRECT("A2:"&CHOOSE(MATCH(A4,A1:L1,0), "A","B","C","D","E","F","G","H","I","J","K","L ")&" 2"))- SUM(INDIRECT("M2:"&CHOOSE(MATCH(A4,A1:L1,0), "M","N","O","P","Q","R","S","T","U","V","W","X ")&" 2")) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in M1-X1... ...and values below in row 2 for each month noted above. For now, place a text entry like Mar in A4. Now this INDIRECT formula will compare the two ranges based on the text entry "Mar" =SUM(INDIRECT("A2:"&CHOOSE(B4,"A","B","C","D","E", "F", "G","H","I","J","K","L")&"2"))-SUM(INDIRECT("M2:"&CHOOSE(B4, "M","N","O","P","Q","R","S","T","U","V","W","X")&" 2")) Be sure to copy that as a single row, not the 3 rows the forum will make it into. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62175 |
Specify a column by the month to calculate it's data
I found a couple of spaces next to the "2". Now it is working. How do I
implement this in my example to get the Month to Date Difference? "JBeaucaire" wrote: Sorry, I used an intermediate helper cell in B4 to convert the "Mar" to a 3, here is the long formula with that operation buried in the formula itself. Sorry for the omission. =SUM(INDIRECT("A2:"&CHOOSE(MATCH(A4,A1:L1,0), "A","B","C","D","E","F","G","H","I","J","K","L ")&" 2"))- SUM(INDIRECT("M2:"&CHOOSE(MATCH(A4,A1:L1,0), "M","N","O","P","Q","R","S","T","U","V","W","X ")&" 2")) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in M1-X1... ...and values below in row 2 for each month noted above. For now, place a text entry like Mar in A4. Now this INDIRECT formula will compare the two ranges based on the text entry "Mar" =SUM(INDIRECT("A2:"&CHOOSE(B4,"A","B","C","D","E", "F", "G","H","I","J","K","L")&"2"))-SUM(INDIRECT("M2:"&CHOOSE(B4, "M","N","O","P","Q","R","S","T","U","V","W","X")&" 2")) Be sure to copy that as a single row, not the 3 rows the forum will make it into. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62175 |
Specify a column by the month to calculate it's data
see response to your later posting at 6:55
-- Regards Roger Govier "Joe" wrote in message ... I found a couple of spaces next to the "2". Now it is working. How do I implement this in my example to get the Month to Date Difference? "JBeaucaire" wrote: Sorry, I used an intermediate helper cell in B4 to convert the "Mar" to a 3, here is the long formula with that operation buried in the formula itself. Sorry for the omission. =SUM(INDIRECT("A2:"&CHOOSE(MATCH(A4,A1:L1,0), "A","B","C","D","E","F","G","H","I","J","K","L ")&" 2"))- SUM(INDIRECT("M2:"&CHOOSE(MATCH(A4,A1:L1,0), "M","N","O","P","Q","R","S","T","U","V","W","X ")&" 2")) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in M1-X1... ...and values below in row 2 for each month noted above. For now, place a text entry like Mar in A4. Now this INDIRECT formula will compare the two ranges based on the text entry "Mar" =SUM(INDIRECT("A2:"&CHOOSE(B4,"A","B","C","D","E", "F", "G","H","I","J","K","L")&"2"))-SUM(INDIRECT("M2:"&CHOOSE(B4, "M","N","O","P","Q","R","S","T","U","V","W","X")&" 2")) Be sure to copy that as a single row, not the 3 rows the forum will make it into. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62175 |
Specify a column by the month to calculate it's data
Thanks very much Roger. You help is much appreciated.
"Roger Govier" wrote: see response to your later posting at 6:55 -- Regards Roger Govier "Joe" wrote in message ... I found a couple of spaces next to the "2". Now it is working. How do I implement this in my example to get the Month to Date Difference? "JBeaucaire" wrote: Sorry, I used an intermediate helper cell in B4 to convert the "Mar" to a 3, here is the long formula with that operation buried in the formula itself. Sorry for the omission. =SUM(INDIRECT("A2:"&CHOOSE(MATCH(A4,A1:L1,0), "A","B","C","D","E","F","G","H","I","J","K","L ")&" 2"))- SUM(INDIRECT("M2:"&CHOOSE(MATCH(A4,A1:L1,0), "M","N","O","P","Q","R","S","T","U","V","W","X ")&" 2")) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in M1-X1... ...and values below in row 2 for each month noted above. For now, place a text entry like Mar in A4. Now this INDIRECT formula will compare the two ranges based on the text entry "Mar" =SUM(INDIRECT("A2:"&CHOOSE(B4,"A","B","C","D","E", "F", "G","H","I","J","K","L")&"2"))-SUM(INDIRECT("M2:"&CHOOSE(B4, "M","N","O","P","Q","R","S","T","U","V","W","X")&" 2")) Be sure to copy that as a single row, not the 3 rows the forum will make it into. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62175 |
| All times are GMT +1. The time now is 09:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com