RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 807
- Office Version
- 365
- Platform
- Windows
Hi all,
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Paper[/TD]
[TD]Rank[/TD]
[TD]Div Rank 1[/TD]
[TD]Div Rank 2[/TD]
[TD]Score[/TD]
[TD]Diversity Score 1[/TD]
[TD]Diversity Score 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Andover[/TD]
[TD]1[/TD]
[TD]#REF ![/TD]
[TD]#REF ![/TD]
[TD]80[/TD]
[TD]80[/TD]
[TD]#REF ![/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Andover[/TD]
[TD]2[/TD]
[TD]#REF ![/TD]
[TD]#REF ![/TD]
[TD]64[/TD]
[TD]54[/TD]
[TD]#REF ![/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Andover[/TD]
[TD]3[/TD]
[TD]#REF ![/TD]
[TD]#REF ![/TD]
[TD]62[/TD]
[TD]62[/TD]
[TD]#REF ![/TD]
[/TR]
</tbody>[/TABLE]
In Column C, the formula is:
=SUMPRODUCT((A4=$A:$A)*(R4<$R:$R))+1
(Previously I had this as $A$4:$A$5128 which also worked)
So this formula goes all the way down and correctly ranks column R which is the score (gotten from a Sum) Column R contains a live formula and this ranks correctly
In column D, the formula is:
=SUMPRODUCT((A4=$A:$A)*(S4<$S:$S))+1
Even if I give the cells an absolute range to look at, I keep getting a #REF ! result.
Column S has a small formula that looks at the rank and determines something, don't worry about that, however, it seems the SUMPRODUCT formula in Column D is having a hard time displaying a ranking result from the scores in Column S.
The weird part is if I copy column S's score into a whole new sheet and put the formula in for another column, it calculates perfectly and gives a correct rank.
Any ideas why I'm getting the #REF ! error? Thank you.
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Paper[/TD]
[TD]Rank[/TD]
[TD]Div Rank 1[/TD]
[TD]Div Rank 2[/TD]
[TD]Score[/TD]
[TD]Diversity Score 1[/TD]
[TD]Diversity Score 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Andover[/TD]
[TD]1[/TD]
[TD]#REF ![/TD]
[TD]#REF ![/TD]
[TD]80[/TD]
[TD]80[/TD]
[TD]#REF ![/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Andover[/TD]
[TD]2[/TD]
[TD]#REF ![/TD]
[TD]#REF ![/TD]
[TD]64[/TD]
[TD]54[/TD]
[TD]#REF ![/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Andover[/TD]
[TD]3[/TD]
[TD]#REF ![/TD]
[TD]#REF ![/TD]
[TD]62[/TD]
[TD]62[/TD]
[TD]#REF ![/TD]
[/TR]
</tbody>[/TABLE]
In Column C, the formula is:
=SUMPRODUCT((A4=$A:$A)*(R4<$R:$R))+1
(Previously I had this as $A$4:$A$5128 which also worked)
So this formula goes all the way down and correctly ranks column R which is the score (gotten from a Sum) Column R contains a live formula and this ranks correctly
In column D, the formula is:
=SUMPRODUCT((A4=$A:$A)*(S4<$S:$S))+1
Even if I give the cells an absolute range to look at, I keep getting a #REF ! result.
Column S has a small formula that looks at the rank and determines something, don't worry about that, however, it seems the SUMPRODUCT formula in Column D is having a hard time displaying a ranking result from the scores in Column S.
The weird part is if I copy column S's score into a whole new sheet and put the formula in for another column, it calculates perfectly and gives a correct rank.
Any ideas why I'm getting the #REF ! error? Thank you.