Hello excel geniuses, I have a list of data. For example:
Data
10
20
--
32
--
--
--
25
30
--
40
55
I need a formula to be filled down the side of the list, which ignores the dashed rows and sums back the last 10 numbers, not 10 rows. So the offset range needs to expand back, x times if there's x dashed rows. I cannot delete the dashed rows, they must stay. And the dashed rows could be anywhere in the list.
I did try doing this using the offset function by totaling the number of dashed cells in the range and adding it on the offset rows, however it came unstuck when the cells above were also dashed...
Anyone got a painless solution, I would prefer a formula to vba. Thanks alot
Data
10
20
--
32
--
--
--
25
30
--
40
55
I need a formula to be filled down the side of the list, which ignores the dashed rows and sums back the last 10 numbers, not 10 rows. So the offset range needs to expand back, x times if there's x dashed rows. I cannot delete the dashed rows, they must stay. And the dashed rows could be anywhere in the list.
I did try doing this using the offset function by totaling the number of dashed cells in the range and adding it on the offset rows, however it came unstuck when the cells above were also dashed...
Anyone got a painless solution, I would prefer a formula to vba. Thanks alot