INDEX Problem; Finding the 0th Result

[Roseus]

Summary: I have two identical INDEX formulas – one results in #VALUE, the other as a number. I think it’s caused by some malarkey where my formula tells Excel to find INDEX(Range,0).
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Explanation:<o></o>
I was working with INDEX today. I have a report card of sorts, across 12 months. I have a table that contains all the goals a person is striving for. Depending on their rank, they will have more difficult goals. The formula uses their Rank to determine how far down the table to look using INDEX.
<o></o>
For my first test case (Joe), I only placed Rank data for January and February. The rest of the months just zeroed out like they’re supposed to – If(Rank=0,0,…) – except March. Instead of evaluating as a zero, it gave me #VALUE. I poked around and couldn’t figure out why, but left it alone.
<o></o>
When I created my second case test (Amy), all the months behaved as they were supposed to except March again – but this time it evaluated as a number! Same formula, same Goal table, same Rank – different result!<o></o>
I reviewed the error and evaluated the “working” formula, and then literally get to the same point, and then evaluate differently! I don’t understand it at all!
<o></o>
#VALUE:
If(Rank=0,0,(.55>(INDEX(‘Goal Table’!$E$25:$E$72,(0)))))
If(Rank=0,0, (.55>(#VALUE!)))
#VALUE
<o></o>
“Working”
If(Rank=0,0, (.55>(INDEX(‘Goal Table’!$E$25:$E$72,(0)))))
If(Rank=0,0, (.55>(.8799))) - - - .8799 is the Goal at this Rank.
1

<o></o>
Gets worse:<o></o>
I started trying to break down the problem (the above code is a snippet of a more complex formula) and isolate the problem. Each Rank is 12 cells long (12 months). Rank D is 1-12, C is 13-24, and so on (E25:E72).<o></o>
=INDEX('Sales Goals'!E25:E72,0)
0.9099<o></o>
=INDEX('Sales Goals'!E25:E36,0)
#VALUE<o></o>
This makes even less sense! .9099 is result 13 of 48 (first goal for Rank C). And reducing the range to just Rank D results in an error!

<o></o>
I’m really confused, help is appreciated!!<o></o>

 

[Marcelo Branco]

Roseus,

Sorry for my questions (maybe silly ones) - i'm only trying to understand your problem.

Probably i misunderstood when you said that using Audit one formula had produced this

“Working”
If(Rank=0,0, (.55>(INDEX(‘Goal Table’!$E$25:$E$72,(0)))))
If(Rank=0,0, (.55>(.8799))) - - - .8799 is the Goal at this Rank.
1

M.

 

[Roseus]

No, no - I really appreciate you taking time to understand my problem. I'm sorry if my answers sounded annoyed or something to make you feel that way!

To clarify on the "Working" formula, here's the concern.

.8799 is the 1st Goal in the table. The INDEX should be able to locate it with a 1, like this:

If(Rank=0,0, (.55>(INDEX(‘Goal Table’!$E$25:$E$72,(1)))))
If(Rank=0,0, (.55>(.8799)))
1

That's perfect. The problem is that it's somehow returning that exact same thing when the INDEX is told to reference the 0th row:

If(Rank=0,0, (.55>(INDEX(‘Goal Table’!$E$25:$E$72,(0)))))
If(Rank=0,0, (.55>(.8799)))
1

It shouldn't resolve with .8799. It shouldn't resolve at all.

 

[Roseus]

I built a test page to try and re-create the problem. I hosted a screen here:

http://www.flickr.com/photos/60517709@N08/5514948959/

Columns A-D (1:42) each represent the goals for each Grade (Rank) level.

This is the formula that goes in J1:

=IF(H1=0,"",(H1>0)+(H1>(INDEX(A1:A48,((F1=60)*(K1)+(F1=61)*(K13)+(F1=62)*(K25)+(F1=63)*(K37)))))+(H1>(INDEX(B1:B48,((F1=60)*(K1)+(F1=61)*(K13)+(F1=62)*(K25)+(F1=63)*(K37)))))+(H1>(INDEX(C1:C48,((F1=60)*(K1)+(F1=61)*(K13)+(F1=62)*(K25)+(F1=63)*(K37)))))+(H1>(INDEX(D1:D48,((F1=60)*(K1)+(F1=61)*(K13)+(F1=62)*(K25)+(F1=63)*(K37))))))

Now then, let's look at results.

Rank 60, Performance .8
Rating is a 3, here, because Performance is equal to or greater than A1, B1, and C1 (but not D1).

Rank 61, Performance .8
Rating is now a 2, because Performance is equal to or greater than A1 and B1, but not C1 or D1.

Rank 0, Performance .8
Rating goes back to a 3, indicating that the 0th result in the index is now looking up a value in the 60's range. This is different than my isolated result in the original file (see OP, "Gets Worse" section), where it came back as .9099, a result from row 13.

I guess in the end this only demonstrates proof that a 0 in the INDEX does not always result in a #VALUE like expected. I still don't understand why it would in some instances, but not in others... This has really become a topic for theory on the matter.