#66 — Search for The Position of A Certain Value And Take Values by Position

Problem Description & Analysis:

We have a statistical table for daily sales of January 2022:

Now we want to find out the day with the largest sales and the sales of 5 days before and after this day.

Solution:

Enter the following formula in cell C2 to get the day with the largest sales:
=spl("=E(?1).pmax(Sales)",A1:B32)
The returned result is 12.

Next, find out the sales of 5 days before and after the day with the largest sales:
=spl("=E(?1).to(?2–5,?2+5)",A1:B32,C2)
As shown in the picture below:

---

Feel free to download esProc Desktop and use this simple SPL formula to save time and get insights in seconds! 🔍💡⬇️

✨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

Comments

[Judith-Excel-Sharing]

Feel free to reach out and learn more Excel tricks with us!

🔺Discord
🔺Reddit