#85 — Calculate Using Adjacent Row/Interval When Data of The Same Group Is Continuous (Link Relative Ratio And YOY)
esProc Desktop
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 to optimize your data workflow!!! 🚀🔥⬇️
✨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
Subscribe to my newsletter
Read articles from esProc Desktop directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
esProc Desktop
esProc Desktop
esProc Desktop is a desktop data processing & analytics tool and is specifically designed for ordinary business people. It supports complex computations & spreadsheet data manipulation, can deal with tasks that are hard to accomplish in Excel, and in addition, generates more concise code than VBA and Python. esProc Desktop boasts all-around programming capabilities, supports multi-step interactive data analytics and is easy to use without configurations. This significantly lowers the technical threshold for data analytics.