This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
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
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-10-30T03:15:15+00:00) #85 — Calculate Using Adjacent Row/Interval When Data of The Same Group Is Continuous (Link Relative Ratio And YOY). Retrieved from https://www.scien.cx/2024/10/30/85-calculate-using-adjacent-row-interval-when-data-of-the-same-group-is-continuous-link-relative-ratio-and-yoy/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.