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
|
|||
|
|||
FORMAT
Hi,
I have the table below for 2008/2009. Now, in another sheet in cell A1 I have the "Year" and in cell B1 I have the "Period". I need a formula to find the "Actual" value from the table below for the Period in cell B1. My issue is that in my formula I may need let's say the value in cell B1 but 4 prior periods and as you can see they may fall into the previous year (i.e. A1=2009, B1=2; I need the actual value 4 prior periods or the value for period 11, 2008). Any ideas? Thanks. Tony. Year Period Actual 2008 1 $0.0000 2008 2 $0.0000 2008 3 $0.1500 2008 4 $0.2800 2008 5 $0.1500 2008 6 $0.2800 2008 7 $0.3000 2008 8 $0.4800 2008 9 $0.3600 2008 10 $0.0000 2008 11 $0.0000 2008 12 $0.0000 2009 1 $0.4200 2009 2 $0.4800 2009 3 $0.4800 2009 4 $0.5000 2009 5 $0.4800 2009 6 $0.4800 2009 7 2009 8 2009 9 2009 10 2009 11 2009 12 |
#2
|
|||
|
|||
FORMAT
Try something like: Code: -------------------- =INDEX('Sheet2'!$C$2:$C$25,MATCH(1,INDEX(('Sheet2' !$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$25=B1),0),0)-3) -------------------- where Sheet2!A2:C25 contains your table and A1 on current sheet contains 2009 and B1 contains 2... -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109382 |
#3
|
|||
|
|||
FORMAT
NBVC,
It works. Just one more thing: would you please explain it to me? I need to use this method in a variety of formulas so I would like to understand what I am doing. Thank you for your time. Tony. "NBVC" wrote: Try something like: Code: -------------------- =INDEX('Sheet2'!$C$2:$C$25,MATCH(1,INDEX(('Sheet2' !$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$25=B1),0),0)-3) -------------------- where Sheet2!A2:C25 contains your table and A1 on current sheet contains 2009 and B1 contains 2... -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109382 |
#4
|
|||
|
|||
FORMAT
If A1 has the year and B1 the period, and we need to go back 3 periods ("4
periods before") then we want the year given by =A1-(B15) which says subtract 1 from A1 if B1 is less than 5) and the period given by =B1-3+12*(B14) which says subtract 3 from B1 and add 12 if B1 is less than 4 So since the table has only one entry for any given year/period we may use SUMPROUDUCT for a two way lookup (or SUMIFS if we have Excel 2007) I used this and it seems to do what you want: =SUMPRODUCT(--(Sheet1!$A$2:$A$30=A1-(B15)),--(Sheet1!$B$2:$B$30=B1-3+12*(B14)),Sheet1!$C$2:$C$30) Why did you use "Format" for the subject? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tony7659" wrote in message ... Hi, I have the table below for 2008/2009. Now, in another sheet in cell A1 I have the "Year" and in cell B1 I have the "Period". I need a formula to find the "Actual" value from the table below for the Period in cell B1. My issue is that in my formula I may need let's say the value in cell B1 but 4 prior periods and as you can see they may fall into the previous year (i.e. A1=2009, B1=2; I need the actual value 4 prior periods or the value for period 11, 2008). Any ideas? Thanks. Tony. Year Period Actual 2008 1 $0.0000 2008 2 $0.0000 2008 3 $0.1500 2008 4 $0.2800 2008 5 $0.1500 2008 6 $0.2800 2008 7 $0.3000 2008 8 $0.4800 2008 9 $0.3600 2008 10 $0.0000 2008 11 $0.0000 2008 12 $0.0000 2009 1 $0.4200 2009 2 $0.4800 2009 3 $0.4800 2009 4 $0.5000 2009 5 $0.4800 2009 6 $0.4800 2009 7 2009 8 2009 9 2009 10 2009 11 2009 12 |
#5
|
|||
|
|||
FORMAT
I like your solution better than mine
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "NBVC" wrote in message ... Try something like: Code: -------------------- =INDEX('Sheet2'!$C$2:$C$25,MATCH(1,INDEX(('Sheet2' !$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$25=B1),0),0)-3) -------------------- where Sheet2!A2:C25 contains your table and A1 on current sheet contains 2009 and B1 contains 2... -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109382 |
#6
|
|||
|
|||
FORMAT
Tony7659;391425 Wrote: NBVC, It works. Just one more thing: would you please explain it to me? I need to use this method in a variety of formulas so I would like to understand what I am doing. Thank you for your time. Tony. "NBVC" wrote: Try something like: Code: -------------------- =INDEX('Sheet2'!$C$2:$C$25,MATCH(1,INDEX(('Sheet2' !$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$25=B1),0),0)-3) -------------------- where Sheet2!A2:C25 contains your table and A1 on current sheet contains 2009 and B1 contains 2... -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC' (http://www.thecodecage.com/forumz/member.php?userid=74) View this thread: 'FORMAT - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=109382) Index() function indexes the range you want to look at Match() finds the position within a range that matches your lookup criteria... so: =Index(Lookup_Range,Row,Column) and can be substituted as follows If the lookupTable is 1-dimensional, you don't need the Column number... default to 1 =INDEX(Lookup_Table,MATCH(lookup_Value,Lookup_Rang e,0)) Since you have to columns that have to match criteria, we have to be creative in the 2nd Match() argument above... We use here, INDEX(('Sheet2'!$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$2 5=B1),0) to create a lookup Range made up of an array of 1's and 0's, so that we can Match a 1 against it to come up with a position. The 2 conditions in the above Index() function return arrays of Trues and Falses.. which, when multiplied together turn into 1's and 0's based on multiplying TRUE*TRUE, TRUE*FALSE, FALSE*TRUE and FALSE*FALSE.. the only one to give 1 is the TRUE*TRUE and that is when you have a match in both columns (in same row)... The 0 at the end is because you need at minimum to complete the Row Number condition of the Index() function for it to work (and you really don't have one, so use 0)... The Match() part then takes over and looks for a 1 in that array, returns the position of that 1 and then the first INDEX() takes the corresponding item from the same position vertically from Column C. Hope that helps... -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109382 |
#7
|
|||
|
|||
FORMAT
Bernard,
Thank you for replying. The formula is not giving me the results wanted. I'd like to be able to go back any amount of periods from the one in B1 (even if they fall in the year 2008 from the table) and get that value. I would also love to understand the logic behind it. Thanks! Tony. "Bernard Liengme" wrote: If A1 has the year and B1 the period, and we need to go back 3 periods ("4 periods before") then we want the year given by =A1-(B15) which says subtract 1 from A1 if B1 is less than 5) and the period given by =B1-3+12*(B14) which says subtract 3 from B1 and add 12 if B1 is less than 4 So since the table has only one entry for any given year/period we may use SUMPROUDUCT for a two way lookup (or SUMIFS if we have Excel 2007) I used this and it seems to do what you want: =SUMPRODUCT(--(Sheet1!$A$2:$A$30=A1-(B15)),--(Sheet1!$B$2:$B$30=B1-3+12*(B14)),Sheet1!$C$2:$C$30) Why did you use "Format" for the subject? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tony7659" wrote in message ... Hi, I have the table below for 2008/2009. Now, in another sheet in cell A1 I have the "Year" and in cell B1 I have the "Period". I need a formula to find the "Actual" value from the table below for the Period in cell B1. My issue is that in my formula I may need let's say the value in cell B1 but 4 prior periods and as you can see they may fall into the previous year (i.e. A1=2009, B1=2; I need the actual value 4 prior periods or the value for period 11, 2008). Any ideas? Thanks. Tony. Year Period Actual 2008 1 $0.0000 2008 2 $0.0000 2008 3 $0.1500 2008 4 $0.2800 2008 5 $0.1500 2008 6 $0.2800 2008 7 $0.3000 2008 8 $0.4800 2008 9 $0.3600 2008 10 $0.0000 2008 11 $0.0000 2008 12 $0.0000 2009 1 $0.4200 2009 2 $0.4800 2009 3 $0.4800 2009 4 $0.5000 2009 5 $0.4800 2009 6 $0.4800 2009 7 2009 8 2009 9 2009 10 2009 11 2009 12 |
#8
|
|||
|
|||
FORMAT
Send me (my private email fro my website or remove TRUENORTH from this on) a sample file I will explain my formula and the from NVBC which I prefer best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tony7659" wrote in message ... Bernard, Thank you for replying. The formula is not giving me the results wanted. I'd like to be able to go back any amount of periods from the one in B1 (even if they fall in the year 2008 from the table) and get that value. I would also love to understand the logic behind it. Thanks! Tony. "Bernard Liengme" wrote: If A1 has the year and B1 the period, and we need to go back 3 periods ("4 periods before") then we want the year given by =A1-(B15) which says subtract 1 from A1 if B1 is less than 5) and the period given by =B1-3+12*(B14) which says subtract 3 from B1 and add 12 if B1 is less than 4 So since the table has only one entry for any given year/period we may use SUMPROUDUCT for a two way lookup (or SUMIFS if we have Excel 2007) I used this and it seems to do what you want: =SUMPRODUCT(--(Sheet1!$A$2:$A$30=A1-(B15)),--(Sheet1!$B$2:$B$30=B1-3+12*(B14)),Sheet1!$C$2:$C$30) Why did you use "Format" for the subject? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tony7659" wrote in message ... Hi, I have the table below for 2008/2009. Now, in another sheet in cell A1 I have the "Year" and in cell B1 I have the "Period". I need a formula to find the "Actual" value from the table below for the Period in cell B1. My issue is that in my formula I may need let's say the value in cell B1 but 4 prior periods and as you can see they may fall into the previous year (i.e. A1=2009, B1=2; I need the actual value 4 prior periods or the value for period 11, 2008). Any ideas? Thanks. Tony. Year Period Actual 2008 1 $0.0000 2008 2 $0.0000 2008 3 $0.1500 2008 4 $0.2800 2008 5 $0.1500 2008 6 $0.2800 2008 7 $0.3000 2008 8 $0.4800 2008 9 $0.3600 2008 10 $0.0000 2008 11 $0.0000 2008 12 $0.0000 2009 1 $0.4200 2009 2 $0.4800 2009 3 $0.4800 2009 4 $0.5000 2009 5 $0.4800 2009 6 $0.4800 2009 7 2009 8 2009 9 2009 10 2009 11 2009 12 |
#9
|
|||
|
|||
FORMAT
Thanks a lot. I had not received (or at least seen) the reply.
Tony. "NBVC" wrote: Tony7659;391425 Wrote: NBVC, It works. Just one more thing: would you please explain it to me? I need to use this method in a variety of formulas so I would like to understand what I am doing. Thank you for your time. Tony. "NBVC" wrote: Try something like: Code: -------------------- =INDEX('Sheet2'!$C$2:$C$25,MATCH(1,INDEX(('Sheet2' !$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$25=B1),0),0)-3) -------------------- where Sheet2!A2:C25 contains your table and A1 on current sheet contains 2009 and B1 contains 2... -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC' (http://www.thecodecage.com/forumz/member.php?userid=74) View this thread: 'FORMAT - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=109382) Index() function indexes the range you want to look at Match() finds the position within a range that matches your lookup criteria... so: =Index(Lookup_Range,Row,Column) and can be substituted as follows If the lookupTable is 1-dimensional, you don't need the Column number... default to 1 =INDEX(Lookup_Table,MATCH(lookup_Value,Lookup_Rang e,0)) Since you have to columns that have to match criteria, we have to be creative in the 2nd Match() argument above... We use here, INDEX(('Sheet2'!$A$2:$A$25=A1)*('Sheet2'!$B$2:$B$2 5=B1),0) to create a lookup Range made up of an array of 1's and 0's, so that we can Match a 1 against it to come up with a position. The 2 conditions in the above Index() function return arrays of Trues and Falses.. which, when multiplied together turn into 1's and 0's based on multiplying TRUE*TRUE, TRUE*FALSE, FALSE*TRUE and FALSE*FALSE.. the only one to give 1 is the TRUE*TRUE and that is when you have a match in both columns (in same row)... The 0 at the end is because you need at minimum to complete the Row Number condition of the Index() function for it to work (and you really don't have one, so use 0)... The Match() part then takes over and looks for a 1 in that array, returns the position of that 1 and then the first INDEX() takes the corresponding item from the same position vertically from Column C. Hope that helps... -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109382 |
Thread Tools | |
Display Modes | |
|
|