HI, I have a Pivot table set up as shown below
with a ranking table
The problem i have is Rank 2 & 3 both have the same return value on the pivot table yet the odds column is returning a random range form the table ( orange highlight ) and the 1 X 2 column is returning a #REF which i think is due to the incorrect values from the odds column. How can this be rectified to show the results ( yellow highlights )
My formula for the Odds column is
=INDEX($Q$9:$Q$27,SUMPRODUCT(($R$9:$T$27=$J22)*(ROW($Q$9:$Q$27)-ROW($Q$8))))
can this be resolved with a formula addition or do i need to do something else.
Thanks in advance
with a ranking table
The problem i have is Rank 2 & 3 both have the same return value on the pivot table yet the odds column is returning a random range form the table ( orange highlight ) and the 1 X 2 column is returning a #REF which i think is due to the incorrect values from the odds column. How can this be rectified to show the results ( yellow highlights )
My formula for the Odds column is
=INDEX($Q$9:$Q$27,SUMPRODUCT(($R$9:$T$27=$J22)*(ROW($Q$9:$Q$27)-ROW($Q$8))))
can this be resolved with a formula addition or do i need to do something else.
Thanks in advance