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!

 

[TheCman81]

Ricks formula is probably better to use as it will stop the issues if you delete lines but make sure you use the adapted formula to handle your filter

=SUBTOTAL(9,M$2:M2)+1&"-"&SUBTOTAL(9,M$2:M3)

 

[Rick Rothstein]

These formulas should work if you delete rows...

N2: ="1-"&M2

N3: =SUM(M$2:M2,1)&"-"&SUM(M$2:M3)

Copy the formula in N3 down to the end of your intervals.

I missed the message about inserting rows. Here are revised formulas that will work (after you copy the formula down into the inserted row and put a value next to it in the intervals column)..

N2: ="1-"&M2

N3: =SUM(M$2:INDIRECT("M"&ROW()-1),1)&"-"&SUM(M$2:M3)

 

[boboivan]

These formulas should work if you delete rows...

N2: ="1-"&M2

N3: =SUM(M$2:M2,1)&"-"&SUM(M$2:M3)

Copy the formula in N3 down to the end of your intervals.

Thank you very much Rick!
Your formula is good too, but when applying filters (huge necessity in my table) it doesn't help me.
It helps with deleting rows problem but not so much with inserting new rows.

 

[TheCman81]

Ricks adapted formula will be

=SUBTOTAL(9,M$2:INDIRECT("M"&ROW()-1))+1&"-"&SUBTOTAL(9,M$2:M3)

 

[boboivan]

I missed the message about inserting rows. Here are revised formulas that will work (after you copy the formula down into the inserted row and put a value next to it in the intervals column)..

N2: ="1-"&M2

N3: =SUM(M$2:INDIRECT("M"&ROW()-1),1)&"-"&SUM(M$2:M3)

This one doesn't work. Gives faulty results.

 

[boboivan]

Ricks adapted formula will be

=SUBTOTAL(9,M$2:INDIRECT("M"&ROW()-1))+1&"-"&SUBTOTAL(9,M$2:M3)

Yeah! This one fixes the deleting rows problem and gives accurate results when filtering. But when inserting new rows, still changes the new inserted cell to e.g. =1&"-"&SUM($M$2:M18). It just can't take automatically the formula above, which should be =SUBTOTAL(9;M$2:INDIRECT("M"&ROW()-1))+1&"-"&SUBTOTAL(9;M$2:M17)

 

[Rick Rothstein]

Ricks adapted formula will be

=SUBTOTAL(9,M$2:INDIRECT("M"&ROW()-1))+1&"-"&SUBTOTAL(9,M$2:M3)

I came late to this thread and I will confess that I did not read all the messages in the entire thread... there were too many of them... so I sort of skimmed it and posted what I thought the OP was after. Thanks TheCman81 for following up on my posting and fixing it for what the OP actually was looking for.

 

[boboivan]

I came late to this thread and I will confess that I did not read all the messages in the entire thread... there were too many of them... so I sort of skimmed it and posted what I thought the OP was after. Thanks TheCman81 for following up on my posting and fixing it for what the OP actually was looking for.

I should thank you both!

 

[boboivan]

I came late to this thread and I will confess that I did not read all the messages in the entire thread... there were too many of them... so I sort of skimmed it and posted what I thought the OP was after. Thanks TheCman81 for following up on my posting and fixing it for what the OP actually was looking for.

I should thank you both!