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-com
fficeffice" /><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>
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><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>
Last edited:
