0

I cannot decipher what is going on in an excel sheet of mine. It appears the result of a sumproduct function is a number yet the value doesn't behave like a number in a subsequent sumif formula.

To be more specific:

I use a =sumproduct based formula to give a rank to values entered in a column "=SUMPRODUCT((P19<=P$19:P$73)/COUNTIF(P$19:P$73,P$19:P$73))", I use this because =rank.eq doesn't return consecutive numbers (which is important elsewhere).

Later I use a sumif to gather all these values with the same "rank" into a sum. The sumif returns a correct result for 25 columns but for the 26th column it returns "0". It happens that all other columns have 5 ranks (1-5) and thus a number of elements that is divideable by 5.

For the 26th column the sumif return = 0 "=SUMIF($AD$19:$AD$73,"="&AO$20,$X$19:$X$73)/SUMIF($AD$19:$AD$73,"="&AO$20,$Y$19:$Y$73)"

When testing the outcome of "=SUMPRODUCT((P19<=P$19:P$73)/COUNTIF(P$19:P$73,P$19:P$73))" with "type" the result is 1 (number). When the cell displays "6" =int(6) returns value 5 (not 6). I tried solving the issue with "=abs(SUMPRODUCT((P19<=P$19:P$73)/COUNTIF(P$19:P$73,P$19:P$73)))" this didn't solve the issue.

When testing the outcome: "=SUMPRODUCT((P19<=P$19:P$73)/COUNTIF(P$19:P$73,P$19:P$73))" -> "6" (even 6.000000000000 formated as number)

"=abs(SUMPRODUCT((P19<=P$19:P$73)/COUNTIF(P$19:P$73,P$19:P$73)))" returns number "6" (even 6.000000000000 formated as number)

but "=int(SUMPRODUCT((P19<=P$19:P$73)/COUNTIF(P$19:P$73,P$19:P$73)))" returns number "5"

"=roundup(SUMPRODUCT((P19<=P$19:P$73)/COUNTIF(P$19:P$73,P$19:P$73))),1)" returns number "6"

Since the type is 'number' and "=abs()" didn't have and effect it seams clear that the displayed value is near to 6 for excel. Yet for "=if()" and "=sumif()" and "=int()" it apparantly is not 6.

Can anyone enlighten me what is happening here? Using "=roundup(...,1)" made the sheet work but it is not very elegant.

HS JDN
  • 1
  • It seems that the number you get is a little bit below 6. You can check if this is true by `=(SUMPRODUCT((P19<=P$19:P$73)/COUNTIF(P$19:P$73,P$19:P$73))<6)`. – Xaver Dec 21 '20 at 17:36
  • 2
    this is a floating point error. It is well documented and you will need to use round to fix it. – Scott Craner Dec 21 '20 at 17:36
  • see: https://stackoverflow.com/questions/36452194/why-does-excel-not-round-according-to-8-byte-ieee-754 – Scott Craner Dec 21 '20 at 17:38
  • and: https://stackoverflow.com/questions/28324169/floating-point-number-in-excel – Scott Craner Dec 21 '20 at 17:39

0 Answers0