RaqForum 19 No.
1 Reply  •  437 View  • 
     ...

Interval Association in the Formula

 

Example

In the Excel file book1.xlsx, part of the data is as follows:

..

The task is to calculate the price value of column B according to the quantity values in column A. The calculation rule is that different quantity intervals correspond to different prices, as shown in the following table:

..

Write SPL script:

A

1

=T("e:/work/book1.xlsx")

2

=A1.run(Price=[15,13.75,13,12.5]([30,50,100,300,500].pseg@r(Quantity)))

3

=T("e:/work/book2.xlsx",A1)

A1 Read book1.xlsx

A2 Loop through each record, use the pseg function to calculate which segment number it is in the interval [30,50,100,300,500] according to the value of Quantity, and then take the price of the corresponding segment number from the price sequence [15,13.75,13,12.5] and assign it to Price

A3 Store the results in A1 to book2.xlsx


 
 •  437 View  •