I have a table in excel with two columns [RunningTotal] and [Change].
I have a formula like this for [RunningTotal]
Its a table with two columns, one for a running total and the other for the change. The IFERROR is for the first row since it cannot be offset because there are no rows before it.
My table looks like this.
3 , #VALUE!
4 , 104
2 , 106
5 , 111
First row throws a error, the second row to reference the first row produce an error but then fallback's to the value
100 + change. Have i done something wrong?
Best How To :
In fact, you are accessing the cell one above the top of the tables's data body. It is the header string value (e.g. RunningTotal) and you receive the #VALUE! error when you try to use the string mathematically with the @Change number.
However, the SUM of a string is zero so that could be checked for.
=IF(SUM(OFFSET([@RunningTotal], -1, -1, 1, 2)), OFFSET([@RunningTotal], -1, 0), 100)+[@Change]
That formula checks that the row being examined will SUM to zero for both @Change and @RunningTotal.
After rereading your original formula it has occurred to me that you could also use your original if you brought the
+[@Change] into the error evaluation.
=IFERROR(OFFSET([@RunningTotal], -1, 0)+[@Change], 100+[@Change])