Convert numbers into intervals

[boboivan]

Hi guys!


Please let me know if I can convert a particular number (e.g. 5) into an interval (1-5) and if I can count them as follows:


Column M Column N
(Number of pages) (Number interval)
2 1-2
5 3-7
3 8-10
... ...


Thanks!

 

[Legacy 234512]

you have to specify your intervals - ( or their bounds ) then plug in any number as parameter to see the interval.
for example.

x = 2
lowB = x - 5
upB = x + 5

so number 2, would be in between -3 and 7

 

[boboivan]

you have to specify your intervals - ( or their bounds ) then plug in any number as parameter to see the interval.
for example.

x = 2
lowB = x - 5
upB = x + 5

so number 2, would be in between -3 and 7

Ok, sorry for being so misleading. The point is, every time I fill in a cell in column M with a number (e.g. 2), automatically that number to be converted into an interval in column N (format like "1-2") and count with the next row converted as well in this format.

	Column L	Column M	Column N
Row 2	Paperwork	No. of pages	Pages interval
Row 3	e.g.	2	1-2
Row 4	e.g.	5	3-7
Row 5	e.g.	4	8-11
Row 6	Total	11

<tbody>
</tbody>

Thank you!

 

[Legacy 234512]

A = M
B = N
first row =1&"-"&A1
2nd til 9 =RIGHT(B1,1)+1&"-"& RIGHT(B1,1)+1 +A2
10 til 99 =RIGHT(B3,2)+1&"-"& RIGHT(B3,2)+1 +A4

then obviously for each digit do RIGHT(n, 3 etc )

 

[PaddyD]

I'd do it like this:

1) establish a lookup table with the minimum value for each interval and the corresponding interval lable:



then return the value from the table with a vlookup of the form:

=VLOOKUP(A5,$A$1:$B$4,2)

...where a5 is your total pages so far count, and $a$1:$b$4 is your lookup table

 

[boboivan]

Thank you very much PaddyD but I need to count every row, not just to copy. Any idea how to do that?

 

[boboivan]

@ mehowski
Thanks maestro, great job!
I still I have a problem when in the second row I'll add a 2 or 3 digits number and in the third a 1 digit number. Any idea about it?
Also, I don't think it's needed to add +1 in the formula.
"=RIGHT(B1;1)+1&"-"& RIGHT(B1;1)+A2" will do the job.

 

[TheCman81]

Try this, assuming your data starts in M2 & N2 - You only need to enter the formula in N3 and drag down

Excel 2010

	M	N
1	Pages	Intervals
2	2	1-2
3	5	3-7
4	3	8-10
5	4	11-14
6	25	15-39
7	55	40-94
8	12	95-106
9	1000	107-1106

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula N2 =1&"-"&M2+0 N3 =SUBSTITUTE(RIGHT(N2,FIND("-",N2)),"-","")+1&"-"&SUM($M$2:M3) N4 =SUBSTITUTE(RIGHT(N3,FIND("-",N3)),"-","")+1&"-"&SUM($M$2:M4) N5 =SUBSTITUTE(RIGHT(N4,FIND("-",N4)),"-","")+1&"-"&SUM($M$2:M5) N6 =SUBSTITUTE(RIGHT(N5,FIND("-",N5)),"-","")+1&"-"&SUM($M$2:M6) N7 =SUBSTITUTE(RIGHT(N6,FIND("-",N6)),"-","")+1&"-"&SUM($M$2:M7) N8 =SUBSTITUTE(RIGHT(N7,FIND("-",N7)),"-","")+1&"-"&SUM($M$2:M8) N9 =SUBSTITUTE(RIGHT(N8,FIND("-",N8)),"-","")+1&"-"&SUM($M$2:M9) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[boboivan]

A = M
B = N
first row =1&"-"&A1
2nd til 9 =RIGHT(B1,1)+1&"-"& RIGHT(B1,1)+1 +A2
10 til 99 =RIGHT(B3,2)+1&"-"& RIGHT(B3,2)+1 +A4

then obviously for each digit do RIGHT(n, 3 etc )

Thanks maestro, great job!
I still I have a problem when in the second row I'll add a 2 or 3 digits number and in the third a 1 digit number. Any idea about it?
Also, I don't think it's needed to add +1 in the formula.
"=RIGHT(B1;1)+1&"-"& RIGHT(B1;1)+A2" will do the job.

 

[boboivan]

Try this, assuming your data starts in M2 & N2 - You only need to enter the formula in N3 and drag down

Excel 2010

	M	N
1	Pages	Intervals
2	2	1-2
3	5	3-7
4	3	8-10
5	4	11-14
6	25	15-39
7	55	40-94
8	12	95-106
9	1000	107-1106

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula N2 =1&"-"&M2+0 N3 =SUBSTITUTE(RIGHT(N2,FIND("-",N2)),"-","")+1&"-"&SUM($M$2:M3) N4 =SUBSTITUTE(RIGHT(N3,FIND("-",N3)),"-","")+1&"-"&SUM($M$2:M4) N5 =SUBSTITUTE(RIGHT(N4,FIND("-",N4)),"-","")+1&"-"&SUM($M$2:M5) N6 =SUBSTITUTE(RIGHT(N5,FIND("-",N5)),"-","")+1&"-"&SUM($M$2:M6) N7 =SUBSTITUTE(RIGHT(N6,FIND("-",N6)),"-","")+1&"-"&SUM($M$2:M7) N8 =SUBSTITUTE(RIGHT(N7,FIND("-",N7)),"-","")+1&"-"&SUM($M$2:M8) N9 =SUBSTITUTE(RIGHT(N8,FIND("-",N8)),"-","")+1&"-"&SUM($M$2:M9) <tbody> </tbody>

<tbody>
</tbody>

This is exactly what I need. Thank you very much smart man from the beautiful Edinburgh!