all formulae entered show zero

[rockhammer]

I'm looking at someone else's worksheet and am hoping to add some formulae of
my own to massage their data. However something rather odd is happening.

For some unknown reason, formulae that I enter in cells to the left of a
particular column (call it X) would work as one would expect. But if I enter
the exact same formulae (say by dragging the cell to the right) in column X
and all columns to the right of it, they all return zero. The formulae I'm
talking about can be as simple as =A1 and they still return zero when clearly
the values should not be zero.

What possibly could be going on? And of course, how can I resolve it? When I
open the sheet, there is a warning about circular references. Thanks.

 

[JLatham]

When you copy or fill formulas that contain relative addresses, the
column/row references change. You may need to use absolute referencing.

Examples of changes when using relative addressing:
In C1 you put this formula: =A1
you copy that to D1 and it becomes =B1
you copy that to C2 and it becomes =A2

But if you use absolute referencing as =$A$1 it won't change when you copy
it somewhere else. In some instances you may need to use mixed referencing
to allow a column indicator change while keeping the row the same (A$1) or
keep the column the same while allowing the row number to change ($A1).

Hope this helps.

 

[rockhammer]

Hi JLatham,

Thanks for your response. I'm afraid is more than that. If you can help,
that would be much appreciated.

Let me give you more detail. For example, there would be a row (say row 1)
of data like this (say starting in column A), using commas to indicate the
next column to the right:

10, 12, 13, 15, =sum(a1:d1), 16, 17, 18, 19, =sum(f1:i1), 20, 21, 22, 23,
=sum(k1:n1), 24, 25, 26... and so on, some 100+ columns like this. The file
is actually fairly big some 2MB and so there are actually many, many rows of
data like this.

The formulae that I try to put in would be stuff like, starting in column F:
=(f1-a1)/abs(a1), (g1-b1)/abs(b1), (h1-c1)/abs(c1),... and copied to the
right via dragging the cells using the relative referencing.

But like I mentioned in the original post, starting from a specific column
(it happens to be column DN), those formulae all return zero but to the left
of column DN, it returns the proper results. In fact, the formulae can be as
simple as (starting from column A), =A1, =B1, =C1, =D1, ... and it would get
the same result to the right of column DN. It is clear that the results of my
formulae or cell references should be anything but zero.

I'm thinking, the folks who built the spreadsheet are probably much more
advanced users than I, and probably they used circular reference to
autobalance some things. Btw, the spreadsheet is a financial model. I've
never used circular reference to autobalance myself. So may be there is a
something I need to do to avoid the problem I'm seeing?

Thanks.

 

[JLatham]

Errors of all types, including circular reference, have a 'downstream' or
propogating effect. That is, if a formula is dependent on a predecessor that
had an error, that same error affects the formulas that are dependent on it.

While creating an intentional circular reference is generally undesirable,
there are no doubt situations where it does provide a needed function/result.
I used to use one myself until I finally figured out a mathematical way of
achieving the result without resorting to the circular reference solution.

Anyhow - to permit intentional circular references: Tools | Options and
then to the [Calculation] tab and check the box next to the "Iteration"
entry. You can leave the two associated options as is, or play with them if
you like to see how it affects things. Probably best to leave them at their
default initially. The 'danger' in making this setting is that any
accidental/unintentional circular references you may add to the workbook
later on will go undetected.

If this doesn't clear up the problem, we can look further into it. What
kind of concerns me is that you're not hitting the issue until you get to
column DN. That's not a column boundary (it's column 118) and Excel 2003
goes on out to column IV, and 2007 goes on out to 3-letter column
identifiers).

 

[rockhammer]

HI JLatham, thanks again for your time. I think i will just resort to
recasting the data, as much of a waste of time as it is, by copy/paste values
into a new sheet to avoid whatever it is. The really odd part is that this
original spreadsheet has its own formulae in cells in DN and to the right
which work! Thanks.

Errors of all types, including circular reference, have a 'downstream' or
propogating effect. That is, if a formula is dependent on a predecessor that
had an error, that same error affects the formulas that are dependent on it.

While creating an intentional circular reference is generally undesirable,
there are no doubt situations where it does provide a needed function/result.
I used to use one myself until I finally figured out a mathematical way of
achieving the result without resorting to the circular reference solution.

Anyhow - to permit intentional circular references: Tools | Options and
then to the [Calculation] tab and check the box next to the "Iteration"
entry. You can leave the two associated options as is, or play with them if
you like to see how it affects things. Probably best to leave them at their
default initially. The 'danger' in making this setting is that any
accidental/unintentional circular references you may add to the workbook
later on will go undetected.

If this doesn't clear up the problem, we can look further into it. What
kind of concerns me is that you're not hitting the issue until you get to
column DN. That's not a column boundary (it's column 118) and Excel 2003
goes on out to column IV, and 2007 goes on out to 3-letter column
identifiers).

Hi JLatham,

Thanks for your response. I'm afraid is more than that. If you can help,
that would be much appreciated.

Let me give you more detail. For example, there would be a row (say row 1)
of data like this (say starting in column A), using commas to indicate the
next column to the right:

10, 12, 13, 15, =sum(a1:d1), 16, 17, 18, 19, =sum(f1:i1), 20, 21, 22, 23,
=sum(k1:n1), 24, 25, 26... and so on, some 100+ columns like this. The file
is actually fairly big some 2MB and so there are actually many, many rows of
data like this.

The formulae that I try to put in would be stuff like, starting in column F:
=(f1-a1)/abs(a1), (g1-b1)/abs(b1), (h1-c1)/abs(c1),... and copied to the
right via dragging the cells using the relative referencing.

But like I mentioned in the original post, starting from a specific column
(it happens to be column DN), those formulae all return zero but to the left
of column DN, it returns the proper results. In fact, the formulae can be as
simple as (starting from column A), =A1, =B1, =C1, =D1, ... and it would get
the same result to the right of column DN. It is clear that the results of my
formulae or cell references should be anything but zero.

I'm thinking, the folks who built the spreadsheet are probably much more
advanced users than I, and probably they used circular reference to
autobalance some things. Btw, the spreadsheet is a financial model. I've
never used circular reference to autobalance myself. So may be there is a
something I need to do to avoid the problem I'm seeing?

Thanks.