Help need splitting a range of data

[Callie57]

I hope some of you Excel gurus can help me with an extremely tedious task. I have a range of data which need splitting into ranges. I have attached a small section of data to show what I need to split - I have a start number and an end number and need to split this into 6 ranges. 0-4, 4-8, 4-12, 12-16, 16-20 and 20-24 so if the start is 8.25 and the end is 19.5 then the ranges will end up 0-4 = 0, 4-8 = 0, 8-12 = 3.75, 12-16 = 4, 16-20 = 3.5 and 20-24 = 0. I have set up the spreadsheet to check the final add up matches the data supplied but am having to do this manually at the moment and it is taking for everrrr! Any help would be appreciated. Thanks

[Pete_UK]

Many contributors cannot view image files as there is a fault with the forum software - attach a sample Excel workbook instead (The FAQ describes how to).

Pete

[Callie57]

Thanks for that - Will try that - hopefully it will work!

[Pete_UK]

In your example file, all the start values are zero, whereas your opening comments indicate that this is not always the case. Have you done that just for illustration?

Pete

[AranDG]

Change your headers from 0-4, 4-8, 8-12,12-16,16-20,20-24 to just the lower limits, so 0,4,8,12,16,20 and use this formula in cell D2 then drag it across and down your range:

Please Login or Register  to view this content.

Edit: Or you could keep the headers as they are and use:

Please Login or Register  to view this content.

[Callie57]

Hi yes - sorry I had sorted the data by start then end and had just used those ones starting at 0 as an example - the start can be anything from 0 to 23.75 and the end can be from 0.25 to 24.

Carol

[Pete_UK]

Well, you have a solution from AranDG which works well.

Pete

[AranDG]

Well, you have a solution from AranDG which works well.

Pete

Thanks Pete

[Callie57]

Thank you so much - you cannot imagine the time saving this has brought. I have a workbook with 50 spreadsheets each with about 500 rows to calculate and this will get it done in minutes!!

Thanks again guys
Carol

[Pete_UK]

Glad you got a solution.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete