esProc Desktopesproc-desktop.hashnode.dev·Dec 4, 2024#110 — Sorting of Row-Based Data — by ExpressionProblem description & analysis: Here below is a data table: Task: Sort in reverse order by the sum of three columns, Maths, English, and PE. Solution: Use SPL XLL and enter the following formula: =spl("=E(?).sort(Maths+English+PE:-1)",A1:E12) The r...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 15, 2024#97 — Count Duplicates of Simple MembersProblem description & analysis: Here below is a data table of names: Task: Count the number of duplicate names. Solution: Use SPL XLL and enter the formula in cell B2: =spl("=?1.conj().select(~==?2).count()",A$2:A$6,A2) Download esProc Desktop fo...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Nov 1, 2024#87 — Merge Data of The Same GroupProblem description & analysis: Here below is an annual and quarterly sales data table: Task: In this table, column A is ordered and has duplicate values. Now we want to clear the data from all rows in the same group except the first row. Solution: ...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Oct 31, 2024#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 u...10 likesexcel
esProc Desktopesproc-desktop.hashnode.dev·Oct 25, 2024#82 — Filter by AccumulationProblem description & analysis: There is a sales data table: Task: Count the date when the sales volume of each person reaches 50,000. Solution: Use SPL XLL and the code is as follows: =spl("=E(?1).group(Name).new(Name,~.select@1(Sales[:0].sum()>500...excel
esProc Desktopesproc-desktop.hashnode.dev·Oct 24, 2024#81 — Accumulate Data in Each GroupProblem description & analysis: There is a sales data table: Task: Calculate the cumulative sales of each person. Solution: Use SPL XLL and the code is as follows (to calculate in cell D1): =spl("=E(?1).new(cum(~~+Sales;Name):'Cumulative Sales')",A1...excel
esProc Desktopesproc-desktop.hashnode.dev·Oct 23, 2024#80 — Simple AccumulationProblem description & analysis: There is a sales data table: Task: We want to calculate the cumulative sales volume and fill them in column C. Solution: Use SPL XLL and calculate in cell C2 first: =spl("=?1+?2",B2,C1) Then drag C2 down to every rel...#Excel-tutorial
esProc Desktopesproc-desktop.hashnode.dev·Oct 18, 2024#77 - Conditional AggregationProblem description & analysis: Here below is a data table: Task: Count the following information for each class: the number of students with scores above 90, the number of failed students, and the average score after removing the highest and lowest...excel
esProc Desktopesproc-desktop.hashnode.dev·Oct 17, 2024#76 — Simple Column-Wise AggregationProblem description & analysis: Here below is a data table: Task: Count the following information for each class: the total number of students, and the average score of the individual total score. Solution: Use SPL XLL and the code is as follows: =s...excel
esProc Desktopesproc-desktop.hashnode.dev·Oct 16, 2024#75 - Find out The Intervals in Which A Certain Condition Occurs ContinuouslyProblem description & analysis: We have a statistical table for daily sales: Task: Find out the date when the sales rises for three consecutive days or more. Solution: Use SPL XLL and enter the following code: A 1 =0 2 =E(‘A1:B32’).group@o(if(Sales...excel
