How to create smart classification for list of numeric values using formula tool.

[Ravy12]

Hello Experts,

I have a list of prices(numeric values) and I am looking to create two fields called Price classification and Price sub classification using formula tool based on the values for Prices.

1. Price Classification: This is straightforward and I am able to build this using the formula tool.

Price Classification	Explanation
>1000	All prices greater than 1000 USD
500-1000	All prices between 500 to 1000 USD
400-500	All prices between 400 to 500 USD
300-400	All prices between 300 to 400 USD
200-300	All prices between 200 to 300 USD
100-200	All prices between 100 to 200 USD
000-100	All prices between 0 to 100 USD
<0	All negative prices

2. Price Sub Classification : This is where I am struggling. This is a drill down to Price classification based on the price value. Please see the explanation below:

Each Price classification has 10 price sub classification.

For eg, 0-100 has following Price Sub classification depending on the price value:

Price classification	Price Sub Class definition	Explanation
0-100	0-100-0	All values between 0 to 10
0-100	0-100-1	All values between 10 to 20
0-100	0-100-2	All values between 20 to 30
0-100	0-100-3	All values between 30 to 40
0-100	0-100-4	All values between 40 to 50
0-100	0-100-5	All values between 50 to 60
0-100	0-100-6	All values between 60 to 70
0-100	0-100-7	All values between 70 to 80
0-100	0-100-8	All values between 80 to 90
0-100	0-100-9	All values between 90 to 100

Similar definition for other subclass
Price classification	Price Sub Class definition	Explanation	Explanation 2
400-500	400-500 -4	All values between 440 to 450	All values in 440's
200-300	200-300-9	All values between 290 to 300	All values in 290's
100-200	100-200-6	All values between 160 to 170	All values in 160's

Exceptions in case of two price classification viz. 500-1000 and >1000 (where the jump is more than 100 and hence further detail added in the sub classification).

Here's the sub classification explanation:

Price sub classification for 500-1000.

Second number in Price sub classification shows the jump of value from 500 and the last number shows value of last two digits price.

Price USD	Price Classification	Price Sub classification	Explanation
567	500-1000	500-1000 - 0 - 6	0-6 because the value range is 560's
587	500-1000	500-1000 - 0 - 8	0-8 because the value range is 580's
612	500-1000	500-1000 - 1 - 1	1-1 because (5+1 jump) for 600 and last two digits in 10's
642	500-1000	500-1000 - 1 - 4	1-4 because (5+1 jump) for 600 and last two digits in 40's
702	500-1000	500-1000 - 2 - 0	2-0 because (5+2 jump) for 700 and last two digits in 0's
714	500-1000	500-1000 - 2 - 1	2-1 because (5+2 jump) for 700 and last two digits in 10's
803	500-1000	500-1000 - 3- 0	3-0 because (5+3 jump) for 800 and last two digits in 0's
942	500-1000	500-1000 - 4- 4	4-4 because (5+4 jump) for 900 and last two digits in 40's

Price Sub classification For >1000

Price USD	Price Classification	Price Sub classification	Explanation
1012	>1000	>1000-0-1	0-1 because (1000+0 jump) from 1000 and last two digit in 10's hence 1
1078	>1000	>1000-0-7	0-7 because (1000+0 jump) from 1000 and last two digit in 70's hence 7
1102	>1000	>1000-1-0	1-0 because (1000+1 jump) from 1000 and last two digit in 0's hence 0
1145	>1000	>1000-1-4	1-4 because (1000+1 jump) from 1000 and last two digit in 40's hence 4
1189	>1000	>1000-1-8	1-8 because (1000+1 jump) from 1000 and last two digit in 80's hence 8
22473	>1000	>1000 - 22 -4 - 7	7 in tens place (70's), 4 in 100's place (400's) and 22 in thousands place (22000's)
31758	>1000	>1000 - 31 - 7 - 5	5 in tens place (50's), 7 in 700's place (700's) and 31 in thousands place (31000's)
31879	>1000	>1000 - 31 - 8 - 7	7 in tens place (70's), 8 in 800's place (700's) and 31 in thousands place (31000's)
11078	>1000	>1000-11-0 -7	7 in tens place (70's), 0 in 100's place (100's) and 11 in thousands place (11000's)

Request you support for building the Price Sub classification field.

Attached is the workflow build so far and the excel sheet with data.

Regards, Ravi

[Aguisande · Accepted solution]

Hi @Ravy12 

Below you'll find a rough WF to make what you need.

The basic steps are, separating (FILTER) the stream to apply different conditions.

In this case, you'll find several filters that separates: 0-500, 500-1000, >1000 and negative values.

Then, I created a value (the min value of each bucket) to compare the Price against it and use the results to get the desired deltas.

I had no time to finish the >1000 category nor tiding up the workflow (I'll do this later), but wanted to show an approach asap.

Hope this helps