Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ready for this? It's a doozy!
Hello! So i have this wacky formula that is connecting to a spreadsheet and
returning a result, but it's totally the wrong row of info. First off, let me introduce you to the infamous formula I am using... =INDEX([Jasons_calendar.xls]Sheet1!D1:D999,MATCH(B28+D2,INDEX([Jasons_calendar.xls]Sheet1!A1:A999+B1:B999,,0),0)) Below is a representation of the "Jasons_calendar.xls" spreadsheet I am trying to pull info from. As you can see, its 3 columns of info (A, B & D. Column 'C' is empty), and 24 hours (24 rows) of shows, with the date and times they air. "Jasons_calendar" A B C D 1 1/31/08 12:00 AM "12amRenato Watches" 2 1/31/08 1:00 AM "1amRenato Watches" 3 1/31/08 2:00 AM "2amTime Zone - Clearance" 4 1/31/08 3:00 AM "3amTime Zone - Clearance" 5 1/31/08 4:00 AM "4amSemi Annual Clearance" 6 1/31/08 5:00 AM "5amSemi Annual Clearance" 7 1/31/08 6:00 AM "6amSemi Annual Clearance" 8 1/31/08 7:00 AM "7amSemi Annual Clearance" 9 1/31/08 8:00 AM "8amPamela McCoy Collection - Clearance" 10 1/31/08 9:00 AM "9amDuet in Gold & Silver - Clearance" 11 1/31/08 10:00 AM "10amLucien Piccard - Clearance" 12 1/31/08 11:00 AM "11amLucien Piccard - Clearance" 13 1/31/08 12:00 PM "12pmHandbag Blowout" 14 1/31/08 1:00 PM "1pmHandbag Blowout" 15 1/31/08 2:00 PM "2pmHandbag Blowout" 16 1/31/08 3:00 PM "3pmPamela McCoy Collection - Clearance" 17 1/31/08 4:00 PM "4pmDuet in Gold & Silver - Clearance" 18 1/31/08 5:00 PM "5pmLucien Piccard - Clearance" 19 1/31/08 6:00 PM "6pmSemi Annual Clearance" 20 1/31/08 7:00 PM "7pmSemi Annual Clearance" 21 1/31/08 8:00 PM "8pmPamela McCoy Collection - Clearance" 22 1/31/08 9:00 PM "9pmTreasures D' Italia - Clearance" 23 1/31/08 10:00 PM "10pmTreasures D' Italia - Clearance" 24 1/31/08 11:00 PM "11pmSemi Annual Clearance" Below is the spreadsheet that Ive created. The formula example listed at the top was taken from the 1:00 PM (B28) row (which returns "#N/A"). The formula basically looks at date (always at D2) and time in 4 row incriments (B4, B8, B12....B28, etc.). So, the formula is exactly the same for each cell, with the exception of row number (the time) that the formula is referencing in column B (time). Here are the results of the formula... "1st Shift Shows" A(empty) B(time) C (show name) D (date = D2) 2 1/31/2008 4 7:00 AM "3amTime Zone - Clearance" 8 8:00 AM "7amSemi Annual Clearance" 12 9:00 AM "11amLucien Piccard - Clearance" 16 10:00 AM "3pmPamela McCoy Collection - Clearance" 20 11:00 AM "7pmSemi Annual Clearance" 24 12:00 PM "11pmSemi Annual Clearance" 28 1:00 PM #N/A 32 2:00 PM #N/A As you can see, the formula returns the totally wrong shows. Example: 7:00 AM (B4) + 1/31/2008 (D2) = "3amTime Zone - Clearance" (wha?!). Baffling to me, hehe. And on all three of my spreadsheets (1st shift, 2nd shift & 3rd shift), the last 2 time slots consistently return #N/A. Should I be using a VLOOKUP command instead of a MATCH command? Or do i need a -1 or 1 instead of a 0 for the MATCH command? Maybe i need to go in a different direction?? Any advice you have will be helpful! Thanks in advance! Jason Biwer |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ready for this? It's a doozy!
I love a good doozy! <g
If you're copying the formula down a column then you need to make the row references to Jasons_calendar.xls absolute. The formula basically looks at date (always at D2) Then you should also make the row reference to D2 absolute. =INDEX([Jasons_calendar.xls]Sheet1!D$1:D$999,MATCH(B28+D$2,INDEX([Jasons_calendar.xls]Sheet1!A$1:A$999+B$1:B$999,,0),0)) Now, this part I don't understand: ...and time in 4 row incriments (B4, B8, B12....B28, etc.). As you can see, the formula returns the totally wrong shows. Ok, how are the times entered in Jasons_calendar.xls column B? You have to be careful how you enter incrementing time. Try this little experiment. Enter this in cell A1: 12:00 AM Enter this formula in A2: =A1+TIME(1,0,0) Copy A2 down to A8. The last time should be 7:00 AM Format as TIME Manually type this time in C1: 7:00 AM Enter this formula in an empty cell: =C1=A8 The result will be TRUE so that would lead you to believe that those 2 cells hold the same value. 7:00 AM = 7:00 AM = TRUE Now, enter this formula in an empty cell: =(C1-A8)=0 The result will be FALSE so that would lead you to believe that those 2 cells DO NOT hold the same value. =(7:00 AM - 7:00 AM) = 0 = FALSE wha?! Which formula should we believe? 7:00 AM minus 7:00 AM *has* to equal 0, or does it? <g In this case we need to believe the formula that returns FALSE. The reason for this is rounding to a very very very small number. Select the cell with this formula =(C1-A8)=0 and edit out the =0 part. Now, goto FormatCellsNumber tabNumber. Keep increasing the decimal places and you'll see where this rounding error is happening. It happens at the 17th decimal place. This is not a bug. See this: http://www.cpearson.com/Excel/rounding.htm Ok, that's a lot of "stuff" we just went through. So, how do we fix your problem? MANUALLY retype the times in Jasons_calendar.xls column B and make the appropriate references absolute. -- Biff Microsoft Excel MVP "TVGuy29" wrote in message ... Hello! So i have this wacky formula that is connecting to a spreadsheet and returning a result, but it's totally the wrong row of info. First off, let me introduce you to the infamous formula I am using... =INDEX([Jasons_calendar.xls]Sheet1!D1:D999,MATCH(B28+D2,INDEX([Jasons_calendar.xls]Sheet1!A1:A999+B1:B999,,0),0)) Below is a representation of the "Jasons_calendar.xls" spreadsheet I am trying to pull info from. As you can see, it's 3 columns of info (A, B & D. Column 'C' is empty), and 24 hours (24 rows) of shows, with the date and times they air. "Jasons_calendar" A B C D 1 1/31/08 12:00 AM "12amRenato Watches" 2 1/31/08 1:00 AM "1amRenato Watches" 3 1/31/08 2:00 AM "2amTime Zone - Clearance" 4 1/31/08 3:00 AM "3amTime Zone - Clearance" 5 1/31/08 4:00 AM "4amSemi Annual Clearance" 6 1/31/08 5:00 AM "5amSemi Annual Clearance" 7 1/31/08 6:00 AM "6amSemi Annual Clearance" 8 1/31/08 7:00 AM "7amSemi Annual Clearance" 9 1/31/08 8:00 AM "8amPamela McCoy Collection - Clearance" 10 1/31/08 9:00 AM "9amDuet in Gold & Silver - Clearance" 11 1/31/08 10:00 AM "10amLucien Piccard - Clearance" 12 1/31/08 11:00 AM "11amLucien Piccard - Clearance" 13 1/31/08 12:00 PM "12pmHandbag Blowout" 14 1/31/08 1:00 PM "1pmHandbag Blowout" 15 1/31/08 2:00 PM "2pmHandbag Blowout" 16 1/31/08 3:00 PM "3pmPamela McCoy Collection - Clearance" 17 1/31/08 4:00 PM "4pmDuet in Gold & Silver - Clearance" 18 1/31/08 5:00 PM "5pmLucien Piccard - Clearance" 19 1/31/08 6:00 PM "6pmSemi Annual Clearance" 20 1/31/08 7:00 PM "7pmSemi Annual Clearance" 21 1/31/08 8:00 PM "8pmPamela McCoy Collection - Clearance" 22 1/31/08 9:00 PM "9pmTreasures D' Italia - Clearance" 23 1/31/08 10:00 PM "10pmTreasures D' Italia - Clearance" 24 1/31/08 11:00 PM "11pmSemi Annual Clearance" Below is the spreadsheet that I've created. The formula example listed at the top was taken from the '1:00 PM' (B28) row (which returns "#N/A"). The formula basically looks at date (always at D2) and time in 4 row incriments (B4, B8, B12....B28, etc.). So, the formula is exactly the same for each cell, with the exception of row number (the time) that the formula is referencing in column B (time). Here are the results of the formula... "1st Shift Shows" A(empty) B(time) C (show name) D (date = D2) 2 1/31/2008 4 7:00 AM "3amTime Zone - Clearance" 8 8:00 AM "7amSemi Annual Clearance" 12 9:00 AM "11amLucien Piccard - Clearance" 16 10:00 AM "3pmPamela McCoy Collection - Clearance" 20 11:00 AM "7pmSemi Annual Clearance" 24 12:00 PM "11pmSemi Annual Clearance" 28 1:00 PM #N/A 32 2:00 PM #N/A As you can see, the formula returns the totally wrong shows. Example: 7:00 AM (B4) + 1/31/2008 (D2) = "3amTime Zone - Clearance" (wha?!). Baffling to me, hehe. And on all three of my spreadsheets (1st shift, 2nd shift & 3rd shift), the last 2 time slots consistently return "#N/A". Should I be using a VLOOKUP command instead of a MATCH command? Or do i need a -1 or 1 instead of a 0 for the MATCH command? Maybe i need to go in a different direction?? Any advice you have will be helpful! Thanks in advance! Jason Biwer |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ready for this? It's a doozy!
Ooops! I forgot the most most "obvious" problem with the formula:
=INDEX([Jasons_calendar.xls]Sheet1!D$1:D$999,MATCH(B28+D$2,INDEX([Jasons_calendar.xls]Sheet1!A$1:A$999+B$1:B$999,,0),0)) You're not referencing the correct sheet with: +B$1:B$999 It should be: +[Jasons_calendar.xls]Sheet1!B$1:B$999 =INDEX([Jasons_calendar.xls]Sheet1!D$1:D$999,MATCH(B28+D$2,INDEX([Jasons_calendar.xls]Sheet1!A$1:A$999+[Jasons_calendar.xls]Sheet1!B$1:B$999,,0),0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I love a good doozy! <g If you're copying the formula down a column then you need to make the row references to Jasons_calendar.xls absolute. The formula basically looks at date (always at D2) Then you should also make the row reference to D2 absolute. =INDEX([Jasons_calendar.xls]Sheet1!D$1:D$999,MATCH(B28+D$2,INDEX([Jasons_calendar.xls]Sheet1!A$1:A$999+B$1:B$999,,0),0)) Now, this part I don't understand: ...and time in 4 row incriments (B4, B8, B12....B28, etc.). As you can see, the formula returns the totally wrong shows. Ok, how are the times entered in Jasons_calendar.xls column B? You have to be careful how you enter incrementing time. Try this little experiment. Enter this in cell A1: 12:00 AM Enter this formula in A2: =A1+TIME(1,0,0) Copy A2 down to A8. The last time should be 7:00 AM Format as TIME Manually type this time in C1: 7:00 AM Enter this formula in an empty cell: =C1=A8 The result will be TRUE so that would lead you to believe that those 2 cells hold the same value. 7:00 AM = 7:00 AM = TRUE Now, enter this formula in an empty cell: =(C1-A8)=0 The result will be FALSE so that would lead you to believe that those 2 cells DO NOT hold the same value. =(7:00 AM - 7:00 AM) = 0 = FALSE wha?! Which formula should we believe? 7:00 AM minus 7:00 AM *has* to equal 0, or does it? <g In this case we need to believe the formula that returns FALSE. The reason for this is rounding to a very very very small number. Select the cell with this formula =(C1-A8)=0 and edit out the =0 part. Now, goto FormatCellsNumber tabNumber. Keep increasing the decimal places and you'll see where this rounding error is happening. It happens at the 17th decimal place. This is not a bug. See this: http://www.cpearson.com/Excel/rounding.htm Ok, that's a lot of "stuff" we just went through. So, how do we fix your problem? MANUALLY retype the times in Jasons_calendar.xls column B and make the appropriate references absolute. -- Biff Microsoft Excel MVP "TVGuy29" wrote in message ... Hello! So i have this wacky formula that is connecting to a spreadsheet and returning a result, but it's totally the wrong row of info. First off, let me introduce you to the infamous formula I am using... =INDEX([Jasons_calendar.xls]Sheet1!D1:D999,MATCH(B28+D2,INDEX([Jasons_calendar.xls]Sheet1!A1:A999+B1:B999,,0),0)) Below is a representation of the "Jasons_calendar.xls" spreadsheet I am trying to pull info from. As you can see, it's 3 columns of info (A, B & D. Column 'C' is empty), and 24 hours (24 rows) of shows, with the date and times they air. "Jasons_calendar" A B C D 1 1/31/08 12:00 AM "12amRenato Watches" 2 1/31/08 1:00 AM "1amRenato Watches" 3 1/31/08 2:00 AM "2amTime Zone - Clearance" 4 1/31/08 3:00 AM "3amTime Zone - Clearance" 5 1/31/08 4:00 AM "4amSemi Annual Clearance" 6 1/31/08 5:00 AM "5amSemi Annual Clearance" 7 1/31/08 6:00 AM "6amSemi Annual Clearance" 8 1/31/08 7:00 AM "7amSemi Annual Clearance" 9 1/31/08 8:00 AM "8amPamela McCoy Collection - Clearance" 10 1/31/08 9:00 AM "9amDuet in Gold & Silver - Clearance" 11 1/31/08 10:00 AM "10amLucien Piccard - Clearance" 12 1/31/08 11:00 AM "11amLucien Piccard - Clearance" 13 1/31/08 12:00 PM "12pmHandbag Blowout" 14 1/31/08 1:00 PM "1pmHandbag Blowout" 15 1/31/08 2:00 PM "2pmHandbag Blowout" 16 1/31/08 3:00 PM "3pmPamela McCoy Collection - Clearance" 17 1/31/08 4:00 PM "4pmDuet in Gold & Silver - Clearance" 18 1/31/08 5:00 PM "5pmLucien Piccard - Clearance" 19 1/31/08 6:00 PM "6pmSemi Annual Clearance" 20 1/31/08 7:00 PM "7pmSemi Annual Clearance" 21 1/31/08 8:00 PM "8pmPamela McCoy Collection - Clearance" 22 1/31/08 9:00 PM "9pmTreasures D' Italia - Clearance" 23 1/31/08 10:00 PM "10pmTreasures D' Italia - Clearance" 24 1/31/08 11:00 PM "11pmSemi Annual Clearance" Below is the spreadsheet that I've created. The formula example listed at the top was taken from the '1:00 PM' (B28) row (which returns "#N/A"). The formula basically looks at date (always at D2) and time in 4 row incriments (B4, B8, B12....B28, etc.). So, the formula is exactly the same for each cell, with the exception of row number (the time) that the formula is referencing in column B (time). Here are the results of the formula... "1st Shift Shows" A(empty) B(time) C (show name) D (date = D2) 2 1/31/2008 4 7:00 AM "3amTime Zone - Clearance" 8 8:00 AM "7amSemi Annual Clearance" 12 9:00 AM "11amLucien Piccard - Clearance" 16 10:00 AM "3pmPamela McCoy Collection - Clearance" 20 11:00 AM "7pmSemi Annual Clearance" 24 12:00 PM "11pmSemi Annual Clearance" 28 1:00 PM #N/A 32 2:00 PM #N/A As you can see, the formula returns the totally wrong shows. Example: 7:00 AM (B4) + 1/31/2008 (D2) = "3amTime Zone - Clearance" (wha?!). Baffling to me, hehe. And on all three of my spreadsheets (1st shift, 2nd shift & 3rd shift), the last 2 time slots consistently return "#N/A". Should I be using a VLOOKUP command instead of a MATCH command? Or do i need a -1 or 1 instead of a 0 for the MATCH command? Maybe i need to go in a different direction?? Any advice you have will be helpful! Thanks in advance! Jason Biwer |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ready for this? It's a doozy!
You are a genius, it worked!
Thanks so much for your help! -Jason "T. Valko" wrote: Ooops! I forgot the most most "obvious" problem with the formula: =INDEX([Jasons_calendar.xls]Sheet1!D$1:D$999,MATCH(B28+D$2,INDEX([Jasons_calendar.xls]Sheet1!A$1:A$999+B$1:B$999,,0),0)) You're not referencing the correct sheet with: +B$1:B$999 It should be: +[Jasons_calendar.xls]Sheet1!B$1:B$999 =INDEX([Jasons_calendar.xls]Sheet1!D$1:D$999,MATCH(B28+D$2,INDEX([Jasons_calendar.xls]Sheet1!A$1:A$999+[Jasons_calendar.xls]Sheet1!B$1:B$999,,0),0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I love a good doozy! <g If you're copying the formula down a column then you need to make the row references to Jasons_calendar.xls absolute. The formula basically looks at date (always at D2) Then you should also make the row reference to D2 absolute. =INDEX([Jasons_calendar.xls]Sheet1!D$1:D$999,MATCH(B28+D$2,INDEX([Jasons_calendar.xls]Sheet1!A$1:A$999+B$1:B$999,,0),0)) Now, this part I don't understand: ...and time in 4 row incriments (B4, B8, B12....B28, etc.). As you can see, the formula returns the totally wrong shows. Ok, how are the times entered in Jasons_calendar.xls column B? You have to be careful how you enter incrementing time. Try this little experiment. Enter this in cell A1: 12:00 AM Enter this formula in A2: =A1+TIME(1,0,0) Copy A2 down to A8. The last time should be 7:00 AM Format as TIME Manually type this time in C1: 7:00 AM Enter this formula in an empty cell: =C1=A8 The result will be TRUE so that would lead you to believe that those 2 cells hold the same value. 7:00 AM = 7:00 AM = TRUE Now, enter this formula in an empty cell: =(C1-A8)=0 The result will be FALSE so that would lead you to believe that those 2 cells DO NOT hold the same value. =(7:00 AM - 7:00 AM) = 0 = FALSE wha?! Which formula should we believe? 7:00 AM minus 7:00 AM *has* to equal 0, or does it? <g In this case we need to believe the formula that returns FALSE. The reason for this is rounding to a very very very small number. Select the cell with this formula =(C1-A8)=0 and edit out the =0 part. Now, goto FormatCellsNumber tabNumber. Keep increasing the decimal places and you'll see where this rounding error is happening. It happens at the 17th decimal place. This is not a bug. See this: http://www.cpearson.com/Excel/rounding.htm Ok, that's a lot of "stuff" we just went through. So, how do we fix your problem? MANUALLY retype the times in Jasons_calendar.xls column B and make the appropriate references absolute. -- Biff Microsoft Excel MVP "TVGuy29" wrote in message ... Hello! So i have this wacky formula that is connecting to a spreadsheet and returning a result, but it's totally the wrong row of info. First off, let me introduce you to the infamous formula I am using... =INDEX([Jasons_calendar.xls]Sheet1!D1:D999,MATCH(B28+D2,INDEX([Jasons_calendar.xls]Sheet1!A1:A999+B1:B999,,0),0)) Below is a representation of the "Jasons_calendar.xls" spreadsheet I am trying to pull info from. As you can see, it's 3 columns of info (A, B & D. Column 'C' is empty), and 24 hours (24 rows) of shows, with the date and times they air. "Jasons_calendar" A B C D 1 1/31/08 12:00 AM "12amRenato Watches" 2 1/31/08 1:00 AM "1amRenato Watches" 3 1/31/08 2:00 AM "2amTime Zone - Clearance" 4 1/31/08 3:00 AM "3amTime Zone - Clearance" 5 1/31/08 4:00 AM "4amSemi Annual Clearance" 6 1/31/08 5:00 AM "5amSemi Annual Clearance" 7 1/31/08 6:00 AM "6amSemi Annual Clearance" 8 1/31/08 7:00 AM "7amSemi Annual Clearance" 9 1/31/08 8:00 AM "8amPamela McCoy Collection - Clearance" 10 1/31/08 9:00 AM "9amDuet in Gold & Silver - Clearance" 11 1/31/08 10:00 AM "10amLucien Piccard - Clearance" 12 1/31/08 11:00 AM "11amLucien Piccard - Clearance" 13 1/31/08 12:00 PM "12pmHandbag Blowout" 14 1/31/08 1:00 PM "1pmHandbag Blowout" 15 1/31/08 2:00 PM "2pmHandbag Blowout" 16 1/31/08 3:00 PM "3pmPamela McCoy Collection - Clearance" 17 1/31/08 4:00 PM "4pmDuet in Gold & Silver - Clearance" 18 1/31/08 5:00 PM "5pmLucien Piccard - Clearance" 19 1/31/08 6:00 PM "6pmSemi Annual Clearance" 20 1/31/08 7:00 PM "7pmSemi Annual Clearance" 21 1/31/08 8:00 PM "8pmPamela McCoy Collection - Clearance" 22 1/31/08 9:00 PM "9pmTreasures D' Italia - Clearance" 23 1/31/08 10:00 PM "10pmTreasures D' Italia - Clearance" 24 1/31/08 11:00 PM "11pmSemi Annual Clearance" Below is the spreadsheet that I've created. The formula example listed at the top was taken from the '1:00 PM' (B28) row (which returns "#N/A"). The formula basically looks at date (always at D2) and time in 4 row incriments (B4, B8, B12....B28, etc.). So, the formula is exactly the same for each cell, with the exception of row number (the time) that the formula is referencing in column B (time). Here are the results of the formula... "1st Shift Shows" A(empty) B(time) C (show name) D (date = D2) 2 1/31/2008 4 7:00 AM "3amTime Zone - Clearance" 8 8:00 AM "7amSemi Annual Clearance" 12 9:00 AM "11amLucien Piccard - Clearance" 16 10:00 AM "3pmPamela McCoy Collection - Clearance" 20 11:00 AM "7pmSemi Annual Clearance" 24 12:00 PM "11pmSemi Annual Clearance" 28 1:00 PM #N/A 32 2:00 PM #N/A As you can see, the formula returns the totally wrong shows. Example: 7:00 AM (B4) + 1/31/2008 (D2) = "3amTime Zone - Clearance" (wha?!). Baffling to me, hehe. And on all three of my spreadsheets (1st shift, 2nd shift & 3rd shift), the last 2 time slots consistently return "#N/A". Should I be using a VLOOKUP command instead of a MATCH command? Or do i need a -1 or 1 instead of a 0 for the MATCH command? Maybe i need to go in a different direction?? Any advice you have will be helpful! Thanks in advance! Jason Biwer |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ready for this? It's a doozy!
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "TVGuy29" wrote in message ... You are a genius, it worked! Thanks so much for your help! -Jason "T. Valko" wrote: Ooops! I forgot the most most "obvious" problem with the formula: =INDEX([Jasons_calendar.xls]Sheet1!D$1:D$999,MATCH(B28+D$2,INDEX([Jasons_calendar.xls]Sheet1!A$1:A$999+B$1:B$999,,0),0)) You're not referencing the correct sheet with: +B$1:B$999 It should be: +[Jasons_calendar.xls]Sheet1!B$1:B$999 =INDEX([Jasons_calendar.xls]Sheet1!D$1:D$999,MATCH(B28+D$2,INDEX([Jasons_calendar.xls]Sheet1!A$1:A$999+[Jasons_calendar.xls]Sheet1!B$1:B$999,,0),0)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I love a good doozy! <g If you're copying the formula down a column then you need to make the row references to Jasons_calendar.xls absolute. The formula basically looks at date (always at D2) Then you should also make the row reference to D2 absolute. =INDEX([Jasons_calendar.xls]Sheet1!D$1:D$999,MATCH(B28+D$2,INDEX([Jasons_calendar.xls]Sheet1!A$1:A$999+B$1:B$999,,0),0)) Now, this part I don't understand: ...and time in 4 row incriments (B4, B8, B12....B28, etc.). As you can see, the formula returns the totally wrong shows. Ok, how are the times entered in Jasons_calendar.xls column B? You have to be careful how you enter incrementing time. Try this little experiment. Enter this in cell A1: 12:00 AM Enter this formula in A2: =A1+TIME(1,0,0) Copy A2 down to A8. The last time should be 7:00 AM Format as TIME Manually type this time in C1: 7:00 AM Enter this formula in an empty cell: =C1=A8 The result will be TRUE so that would lead you to believe that those 2 cells hold the same value. 7:00 AM = 7:00 AM = TRUE Now, enter this formula in an empty cell: =(C1-A8)=0 The result will be FALSE so that would lead you to believe that those 2 cells DO NOT hold the same value. =(7:00 AM - 7:00 AM) = 0 = FALSE wha?! Which formula should we believe? 7:00 AM minus 7:00 AM *has* to equal 0, or does it? <g In this case we need to believe the formula that returns FALSE. The reason for this is rounding to a very very very small number. Select the cell with this formula =(C1-A8)=0 and edit out the =0 part. Now, goto FormatCellsNumber tabNumber. Keep increasing the decimal places and you'll see where this rounding error is happening. It happens at the 17th decimal place. This is not a bug. See this: http://www.cpearson.com/Excel/rounding.htm Ok, that's a lot of "stuff" we just went through. So, how do we fix your problem? MANUALLY retype the times in Jasons_calendar.xls column B and make the appropriate references absolute. -- Biff Microsoft Excel MVP "TVGuy29" wrote in message ... Hello! So i have this wacky formula that is connecting to a spreadsheet and returning a result, but it's totally the wrong row of info. First off, let me introduce you to the infamous formula I am using... =INDEX([Jasons_calendar.xls]Sheet1!D1:D999,MATCH(B28+D2,INDEX([Jasons_calendar.xls]Sheet1!A1:A999+B1:B999,,0),0)) Below is a representation of the "Jasons_calendar.xls" spreadsheet I am trying to pull info from. As you can see, it's 3 columns of info (A, B & D. Column 'C' is empty), and 24 hours (24 rows) of shows, with the date and times they air. "Jasons_calendar" A B C D 1 1/31/08 12:00 AM "12amRenato Watches" 2 1/31/08 1:00 AM "1amRenato Watches" 3 1/31/08 2:00 AM "2amTime Zone - Clearance" 4 1/31/08 3:00 AM "3amTime Zone - Clearance" 5 1/31/08 4:00 AM "4amSemi Annual Clearance" 6 1/31/08 5:00 AM "5amSemi Annual Clearance" 7 1/31/08 6:00 AM "6amSemi Annual Clearance" 8 1/31/08 7:00 AM "7amSemi Annual Clearance" 9 1/31/08 8:00 AM "8amPamela McCoy Collection - Clearance" 10 1/31/08 9:00 AM "9amDuet in Gold & Silver - Clearance" 11 1/31/08 10:00 AM "10amLucien Piccard - Clearance" 12 1/31/08 11:00 AM "11amLucien Piccard - Clearance" 13 1/31/08 12:00 PM "12pmHandbag Blowout" 14 1/31/08 1:00 PM "1pmHandbag Blowout" 15 1/31/08 2:00 PM "2pmHandbag Blowout" 16 1/31/08 3:00 PM "3pmPamela McCoy Collection - Clearance" 17 1/31/08 4:00 PM "4pmDuet in Gold & Silver - Clearance" 18 1/31/08 5:00 PM "5pmLucien Piccard - Clearance" 19 1/31/08 6:00 PM "6pmSemi Annual Clearance" 20 1/31/08 7:00 PM "7pmSemi Annual Clearance" 21 1/31/08 8:00 PM "8pmPamela McCoy Collection - Clearance" 22 1/31/08 9:00 PM "9pmTreasures D' Italia - Clearance" 23 1/31/08 10:00 PM "10pmTreasures D' Italia - Clearance" 24 1/31/08 11:00 PM "11pmSemi Annual Clearance" Below is the spreadsheet that I've created. The formula example listed at the top was taken from the '1:00 PM' (B28) row (which returns "#N/A"). The formula basically looks at date (always at D2) and time in 4 row incriments (B4, B8, B12....B28, etc.). So, the formula is exactly the same for each cell, with the exception of row number (the time) that the formula is referencing in column B (time). Here are the results of the formula... "1st Shift Shows" A(empty) B(time) C (show name) D (date = D2) 2 1/31/2008 4 7:00 AM "3amTime Zone - Clearance" 8 8:00 AM "7amSemi Annual Clearance" 12 9:00 AM "11amLucien Piccard - Clearance" 16 10:00 AM "3pmPamela McCoy Collection - Clearance" 20 11:00 AM "7pmSemi Annual Clearance" 24 12:00 PM "11pmSemi Annual Clearance" 28 1:00 PM #N/A 32 2:00 PM #N/A As you can see, the formula returns the totally wrong shows. Example: 7:00 AM (B4) + 1/31/2008 (D2) = "3amTime Zone - Clearance" (wha?!). Baffling to me, hehe. And on all three of my spreadsheets (1st shift, 2nd shift & 3rd shift), the last 2 time slots consistently return "#N/A". Should I be using a VLOOKUP command instead of a MATCH command? Or do i need a -1 or 1 instead of a 0 for the MATCH command? Maybe i need to go in a different direction?? Any advice you have will be helpful! Thanks in advance! Jason Biwer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to set up a 60 day ready reckoner | Excel Discussion (Misc queries) | |||
SHOW SUM ON READY BAR | Excel Discussion (Misc queries) | |||
Help I Am Ready To Give Up | Excel Worksheet Functions | |||
This is a doozy... | Charts and Charting in Excel | |||
This is a doozy... | New Users to Excel |