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.
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]Column L[/TD]
[TD="align: center"]Column M[/TD]
[TD="align: center"]Column N[/TD]
[/TR]
[TR]
[TD="align: center"]Row 2[/TD]
[TD="align: center"]Paperwork[/TD]
[TD="align: center"]No. of pages[/TD]
[TD="align: center"]Pages interval[/TD]
[/TR]
[TR]
[TD="align: center"]Row 3[/TD]
[TD="align: center"]e.g. [/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1-2[/TD]
[/TR]
[TR]
[TD="align: center"]Row 4[/TD]
[TD="align: center"]e.g. [/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3-7[/TD]
[/TR]
[TR]
[TD="align: center"]Row 5[/TD]
[TD="align: center"]e.g. [/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8-11[/TD]
[/TR]
[TR]
[TD="align: center"]Row 6[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

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
Pages	Intervals

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1-2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3-7[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8-10
[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11-14[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]15-39[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]40-94[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]95-106[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]107-1106[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]=1&"-"&M2+0[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N3[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N2,FIND("-",N2)),"-","")+1&"-"&SUM($M$2:M3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N4
[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N3,FIND("-",N3)),"-","")+1&"-"&SUM($M$2:M4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N5[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N4,FIND("-",N4)),"-","")+1&"-"&SUM($M$2:M5)
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N6[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N5,FIND("-",N5)),"-","")+1&"-"&SUM($M$2:M6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N7[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N6,FIND("-",N6)),"-","")+1&"-"&SUM($M$2:M7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N8[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N7,FIND("-",N7)),"-","")+1&"-"&SUM($M$2:M8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]N9[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N8,FIND("-",N8)),"-","")+1&"-"&SUM($M$2:M9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Pages	Intervals

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1-2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3-7[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8-10[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11-14[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]15-39[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]40-94[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]95-106[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]107-1106[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]=1&"-"&M2+0[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N3[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N2,FIND("-",N2)),"-","")+1&"-"&SUM($M$2:M3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N4[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N3,FIND("-",N3)),"-","")+1&"-"&SUM($M$2:M4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N5[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N4,FIND("-",N4)),"-","")+1&"-"&SUM($M$2:M5)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N6[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N5,FIND("-",N5)),"-","")+1&"-"&SUM($M$2:M6)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N7[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N6,FIND("-",N6)),"-","")+1&"-"&SUM($M$2:M7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N8[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N7,FIND("-",N7)),"-","")+1&"-"&SUM($M$2:M8)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N9[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N8,FIND("-",N8)),"-","")+1&"-"&SUM($M$2:M9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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