4 Replies Latest reply on Dec 21, 2017 12:11 PM by Glenn Kuly

    Simple tweak for YoY comparison of ratios

    Glenn Kuly

      A couple days ago I almost got caught out by an error in a table showing a ratio for this year and last year, alongside a YoY comparison.


      ratio 1.png

      My table looked something like the above, and the percentages in the YoY column look incorrect. This is easily apparent in the bottom row, where the ratio is the same for both years, yet the final column indicates an increase of 0.1%.


      What's going on here is that while the ratios are rounded up to one decimal place, the actual underlying values are expressed to eight decimal places (the data is contained in the attached workbook v10.4). The YoY values shown above are correct in terms of the underlying data, but incorrect based on the rounded values shown in the view.


      If changing the underlying data is not an option, there is a simple tweak available to produce correct YoY values in the view, using the ROUND function :


           (SUM(ROUND([Current Year],3))-SUM(ROUND([Prev Year],3)))/SUM(ROUND([Prev Year],3))


      ratio 2.png

      (Rounding to three decimal places is equivalent to a percentage expressed to one decimal place.) Hope someone else finds this helpful.


      -- Glenn