KNOWLEDGE BASE

Percentile Function Calculates Differently than in Excel


Published: 25 Oct 2017
Last Modified Date: 25 Oct 2017

Issue

Tableau's percentile function might return a different value than Excel.

Environment

  • Tableau Desktop 10.4.0
  • Windows 7
  • Excel

Resolution

Try using Excel's PERCENTILE.INC function instead of PERCENTILE.EXC, however please note that it still may not always match exactly. The values should get closer as the number of records that are analyzed increases.

It is possible to recreate Excel's function with the Window_percentile. See the following community post for more information: Percentile Calculation Different in Tableau from Excel.

Cause

  • There is no standard for exactly how PERCENTILE should be calculated.
  • Part of Microsoft's explanation of their Percentile function is: "If k is not a multiple of 1/(n - 1), PERCENTILE interpolates to determine the value at the k-th percentile". For more information, see Percentile function in the Microsoft Knowledge Base. 
  • Tableau uses weighted interpolation, giving a higher weight to the value that is closer to the 'real' percentile. Excel does not have a function that matches this method exactly.
Did this article resolve the issue?