Create a range field base on other column values

[IJH34]

I am working with credit score data and need to break out the scores in 20pts increments to do some summary statistics. 

I have 43K rows of data all which have a credit score ranging from 300-880.

What I would like to do is add a field that says what range the score is in so I can do a group by....

RANGE	ACCT #	CREDIT SCORE	CURRENT BAL	RATE
640-659	0123	652	21965	8.01
740-759	0456	749	47658	6.45
320-339	0789	326	11697	9.99

Is there a way to easily add a range field without inputting a file with the 30 different range values, and then a extremely long IF THEN functions that calls the score to the range?

[Claje]

For this particular example I would use math and a formula like the following:

TOSTRING(FLOOR([CREDIT SCORE]/20)*20)+'-'+TOSTRING(CEIL([CREDIT SCORE]/20)*20-1)

This will display the range numbers you want by taking the lower and upper bounds of each range per score, and won't require a lookup or complex formula. 

[Thableaus · Accepted solution]

Hi @IJH34 

I'd do this way:

- Generate Rows to generate your range numbers with intervals of 20

- Use Multi-Row Formula to create the end point of the Range

- Filter tool to leave out the last value (880)

- Create the Range string with Range Values

- Use Append Tool to append all ranges and the filter by a condition to match the range (value between start point and end point) and bring desired values

Of course it depends of the size of your dataset. Appending sometimes might not be the best way to go, so other alternatives should be considered.

WF attached.

Cheers,

[IJH34]

@Claje I like this idea however, my ranges seems a bit funky. I have the 20pt intervals but I also have 1pt intervals following. What is causing this?

[Muazul]

IF
FLOOR([Column Name]/10) - [Column Name]/10 = 0 THEN
TOSTRING(FLOOR([Column Name]/10)*10)+'-'+TOSTRING((CEIL([Column Name]/10)+1)*10)
ELSE
TOSTRING(FLOOR([Column Name]/10)*10)+'-'+TOSTRING(CEIL([Column Name]/10)*10)
ENDIF

//10 ->  this is Class Interval, you might want to have a different C.I. replace 10 with that particular number.

// ex - if x >=10 AND x<20, Output = 10-20