Rank Issue using value from Pivot Table

  • Trying to apply conditional formatting to a pivot based on the ranking of the value. It was working for most values but some would generate an #N/A. I built some test formulas outside of the pivot and I am seeing the results in the pic below.


    Both rank formulas are as follows:

    =RANK(E31,Scoring!$H$10:$J$10,0)

    =RANK(E32,Scoring!$H$10:$J$10,0)


    The sumif value is coming from the range used for ranking, and it is looking at the same dataset as the pivot table (it will be in cell H10 of the ranking range listed above). I thought maybe there was some weird rounding or decimal issue, but based on the variance in the pic it looks like they are identical too me. Not to mention it is working for most of the other cells. Any ideas what could be driving that?