I'm trying to move away from using OFFSET, having learned this is a volatile function, and hence likely a contributing reason that my many & large spreadsheets run slow, even after having invested in a new pc with plenty of RAM and speedy processors.
How can I write a formula that sums a (single column) range that always ends one row above my total row, WITHOUT using OFFSET([thiscell],-1,). I could of course determine a reference using ROW()-1, but it appears the answer to the question of whether the ROW() function is also volatile is still not definitively answered. Having read Mynda's recent post on the use of INDEX, I've been trying to come up with a formula involving this, thus far unsuccessfully.
I don't want to use named ranges, as I have a great many subtotals to calculate, across many columns / periods and for many areas, hence I don't want to use a myriad of names.
Any ideas would be greatly appreciated. Thanks
Hello Lasse,
Without any sample file that shows how your data is setup one can only assume and that is quite often not so good. My first thought when reading your post was that your data is not in a tabular format and hence you are not using a Pivot Table, which would be my option of choice instead of wrangle around with nested formulas and such. If it is more or less so then I can recommend you to read Mynda's blog about setting up data in tabular format.
Any way, if you can provide us with a sample file it will be easier for all of us.
Br,
Anders
enter following formula in column A
=SUM(A$1:INDEX(A:A,ROW()-1))
Depending on your data, you could consider converting it into a Table and add a Total Row below it.
