Ready for this? It's a doozy!

[TVGuy29]

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!D1999,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

 

[T. Valko]

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$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 Format>Cells>Number tab>Number. 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

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!D1999,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

 

[T. Valko]

Ooops! I forgot the most most "obvious" problem with the formula:

=INDEX([Jasons_calendar.xls]Sheet1!D$1$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$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

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$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 Format>Cells>Number tab>Number. 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

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!D1999,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

 

[TVGuy29]

You are a genius, it worked!

Thanks so much for your help!

-Jason

Ooops! I forgot the most most "obvious" problem with the formula:

=INDEX([Jasons_calendar.xls]Sheet1!D$1$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$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

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$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 Format>Cells>Number tab>Number. 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

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!D1999,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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

You are a genius, it worked!

Thanks so much for your help!

-Jason

Ooops! I forgot the most most "obvious" problem with the formula:

=INDEX([Jasons_calendar.xls]Sheet1!D$1$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$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

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$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 Format>Cells>Number tab>Number. 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


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!D1999,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