This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Problem description & analysis:
Here below is a data table:
Task: We want to calculate the price value in column B according to the quantity in column A, and the calculation should follow the rule: different quantity intervals correspond to different prices, as shown in the table below:
Solution:
Use SPL XLL and enter in cell B2:
=spl("=[15,13.75,13,12.5]([30,50,100,300,500].pseg@r(?1))",A2)
The result is as follows:
Then drag B2 down to every relevant row:
The idea of the calculation is to use the pseg function to calculate which interval the quantity value belongs to [30, 50, 100, 300, 500], and then take out the price of the corresponding interval from the price sequence [15, 13.75, 13, 12.5] and return.
Download esProc Desktop for FREE and revolutionize your Excel processes using SPL XLL!! 🚀✨⬇️
✨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 (2025-01-13T02:24:20+00:00) #130 — Use Formulas to Handle Interval Association. Retrieved from https://www.scien.cx/2025/01/13/130-use-formulas-to-handle-interval-association/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.