Would it be the case that all ranges are same number of rows, a relatively simple formula would do. As from your example follows, the 1st range is 47 rows, the 2nd one 49 rows, the 3rd one 48 rows, ... . So unless you have some other criterion in some column of your table allowing to decide, to which range/group of data the row belongs, and which you forgot to include in your message, there is no way to use formulas to get wanted result.

Either you provide some additional info, or you write a procedure which calculates and inserts needed result, and call it through shortcut after you select a range.