DEV Community

Cover image for #63 — Search for The Nth Value And The Nth Value from Last
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#63 — Search for The Nth Value And The Nth Value from Last

Problem description & analysis:

Here below is a sales statistical table:

original table
Task: Now we want to find out the product ranked tenth by sales and its sales, and the product ranked tenth from last by sales and its sales.

Solution:

Enter the following formulas in SPL XLL. The results are as follows:
The 10th:
=spl("=E(?1).sort(ProductSales:-1)(10)",A1:C78)

result table of getting the 10th value
The 10th from last:
=spl("=E(?1).sort(ProductSales:-1).m(-10)",A1:C78)

result table of getting the 10th value from last


ℹ️ Feel free to download esProc Desktop and explore the related information:
SPL download address: esProc Desktop 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

Top comments (1)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Feel free to reach out if you have any Excel problems. We'd love to help!

⭐️Discord
⭐️Reddit