#85 — Calculate Using Adjacent Row/Interval When Data of The Same Group Is Continuous (Link Relative Ratio And YOY)

#beginners #tutorial #programming #productivity

Problem description & analysis:

Here below is an annual and quarterly sales data table:

Task: Now we want to calculate LRR and YOY (to calculate in cell D1).

Solution:

Use SPL XLL and the code is as follows:

   A
1 =E(‘A1:C21’)
2 =A1.(Sales-Sales[-1])
3 =A1.group(Year)
4 =A3.(~.(Sales-A3.~[-1](#).Sales)).conj()
5 return A2.new(~:LinkRelative,A4(#):YOY)

A2: Calculate LRR, [-1] represents the previous row.
A3: Group by Year.
A4: Calculate YOY. The symbol ~ represents the current member in the loop function, and # represents the sequence number of the current member in the loop function.
The results are shown below:

Download esProc Desktop for FREE and watch your Excel skills level up!!!! 🚀🔥⬇️

✨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

Your AI Code Assistant

Automate your code reviews. Catch bugs before your coworkers. Fix security issues in your code. Built to handle large projects, Amazon Q Developer works alongside you from idea to production code.

Get started free in your IDE