#86 — Calculate Using Adjacent Row/Interval When Data of The Same Group Is Discontinuous (LRR/YOY in The Case of Missing Data)

Problem description & analysis:

Here below is an annual and quarterly sales data table:

Task: In this table, the data for the first quarter of 2020 is missing. When the data of this quarter is used to calculate LRR, skip this quarter directly, and use the data of the fourth quarter in 2019; when the data of this quarter is used to calculate YOY, regard it as zero (to calculate in the cell D1).

Solution:

Use SPL XLL and the code is as follows:

=spl("=E(?1).new(Sales-Sales[-1]:LinkRelative,Sales-~[:-1].select@z1(Year==get(1,Year)-1 && Quarter==get(1,Quarter)).Sales:YOY)",A1:C20)

get(1,Year) means taking the value in the column Year of the current member of the previous-layer function.

~[:-1] represents the set from the first member to the previous member.

The results are shown below:

Download esProc Desktop for FREE and unlock powerful insights into your Excel datasheets!!! 🚀🔥⬇️

✨SPL download address: esProc Desktop FREE Download

✨Plugin Installation Method: SPL XLL Installation and Configuration

✨References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

✨YouTube FREE courses: SPL Programming