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!

 

[boboivan]

You mean like this?

=RIGHT(N2,LEN(N2)-FIND("-",N2))+1&"-"&SUBTOTAL(9,$M$2:M3)

This is exactly what I mean, you smart, smart man!
Thank you very much again.

All the best!

 

[TheCman81]

Glad to help. Thanks for the feedback

 

[boboivan]

Set ref = ThisWorkbook.Sheets("2013")

what error are you getting, run time? what number what does it say

what version of excel are you using

maybe
set ref = ActiveWorkbook.Sheets("2013")

Yes, I'm getting Run Time error. My excel version is 2007.
Fortunately I solved the problem taking "TheCman81"'s suggestion.

Thank you very much for your kind help and I wish you all the best!

 

[boboivan]

Glad to help. Thanks for the feedback

Dear friend,
Apparently there is another small problem. When I insert a new row in my table, the new row takes automatically the following formula: =1&"-"&SUM($M$2:M18), instead of =RIGHT(N16;LEN(N16)-FIND("-";N16))+1&"-"&SUBTOTAL(9;$M$2:M17), which, of course, gives a mis result. Any idea how to fix this?
Thanks!

 

[boboivan]

Glad to help. Thanks for the feedback

Also, when I delete a row, the cells's formula below the deleted row changes automatically into =RIGHT(#REF!;LEN(#REF!)-FIND("-";#REF!))+1&"-"&SUBTOTAL(9;$M$2:M18), which gives as well a faulty result like #REF!.
Sorry for bothering so much!

 

[TheCman81]

That's because the formula below is referencing a cell above and if you delete a row which contains data referencing another formula, excel doesn't like that and throws out the REF error. To fix it, just highlight the REF part within the formula and select the cell above which it relates to.

Check out this link for more info

Correct a #REF! error - Excel - Office.com

 

[TheCman81]

Dear friend,
Apparently there is another small problem. When I insert a new row in my table, the new row takes automatically the following formula: =1&"-"&SUM($M$2:M18), instead of =RIGHT(N16;LEN(N16)-FIND("-";N16))+1&"-"&SUBTOTAL(9;$M$2:M17), which, of course, gives a mis result. Any idea how to fix this?
Thanks!

When you insert a row, the formula below will still reference the cell above the inserted line which will cause issues if you put data in the inserted row. I don't have a total fix for it but what I would do to ensure your data is correct is once you have made all your changes, I would take off your filter and copy the formula that's in the first cell which I assume is in N3 and copy it over your entire range. Hope this sorts you out

Colin

 

[TheCman81]

Also, when I delete a row, the cells's formula below the deleted row changes automatically into =RIGHT(#REF!;LEN(#REF!)-FIND("-";#REF!))+1&"-"&SUBTOTAL(9;$M$2:M18), which gives as well a faulty result like #REF!.
Sorry for bothering so much!

To make this easy to sort, I assume the formula is in cell N18? All you would do is change all the #REF! to the cell above which would be N16 or just copy the formula above

 

[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.

 

[boboivan]

When you insert a row, the formula below will still reference the cell above the inserted line which will cause issues if you put data in the inserted row. I don't have a total fix for it but what I would do to ensure your data is correct is once you have made all your changes, I would take off your filter and copy the formula that's in the first cell which I assume is in N3 and copy it over your entire range. Hope this sorts you out

Colin

I got it Colin and ... glad to meet you!
This small issue is not a big deal to me. I'll do as you say, no problem.
Thanks again and sorry for any excessive bothering.

Bogdan