Formulas gone bonkers

  • Thread starter Thread starter Capt. Bangs
  • Start date Start date
C

Capt. Bangs

Help!! I am creating a spreadsheet to handle a relatively complicated
cashflow analysis. I was doing fine until I started replacing explicit cell
references with named ranges. All of a sudden, I started getting circular
reference errors. To cure them, I tried going back, in some cases, to
explicit cell references. That worked to a point, but then this started
happening: I would create a formula, I could see that the value the formula
created in that row was correct, I would copy the formula down, but the
values appearing in the subsequent rows were the same value that appeared in
the row from which the formula was copied! Why is this happening? And how
can I fix the problem?
 
Yes. I checked that, and I tried hitting F9.

Don Guillett said:
calculation automatic??

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
 
I think I found one solution: Apparently functions like MIN, MAX, and SUM,
when faced with a reference to a range, evaluate the entire named range
rather than just the value in the cell located in the same row within the
named range. To refer to the cell in the same range, I added a "+" before
references to named ranges within MIN functions. Not very elegant, but it
might do the trick.
 
If you name a range like A1:A10 as MyRange, you're creating an absolute
reference - $A1:$A10 If your formula is =SUM(A1:A10) and you change that to
be =SUM(MyRange) you are now saying: =SUM($A$1:$A:$10). Bear that in mind.
When you copied (dragged?) the formula down, the relative references
(A1:A10) changed.. Now when you drag the formula down the absolute
references (MyRange - $A$1:$A$10) do NOT change,. As far as problems with
implict reference are concerned I would need to see an example.

Tyro
 
Correction: If you name a range like A1:A10 as MyRange, you're creating an
absolute reference - $A1:$A10 should read: If you name a range like A1:A10
as MyRange, you're creating an absolute reference - $A$1:$A$10
I check these things 3 times before I post them but never see the error
until I have posted. :(

Tyro
 
You can change the absolute references in the named range to relative, but
to do so, you must be on the first cell that references the range, before
dragging the formula to other cells. You have to be very careful doing so.
Generally not recommended. The purpose of naming something is to have a
static reference to that something.

Tyro
 
Tyro,

Perhaps I'm using named ranges incorrectly. I have named say, C16:C99 as
Cash_In and D16:D99 as Cash_Out. Each cell in each column represents a value
for one of those quantities in a different period. Sometimes, the formulas
in columns to the right of C and D which reference a value in either Column C
or Column D are relatively simple, and the formula seems to understand that
it should look back in the same row. But other formulas are more complex, and
as you may have seen, I did discover that if the formula uses a function such
as MIN or MAX, the function within the formula apparently doesn't know to
confine its evaluation to the value appearing in the referenced column and in
the same row. In still other instances, I would have liked to have referred
to the value in the previous row (as in, if the cash inflow in the previous
period was less than x, do this; otherwise, do that).

Anyway, thank you for your help.

Capt. Bangs
 
The functions MAX and MIN are designed to work with more than one cell. Why
are you using them to refer to one cell? "I did discover that if the formula
uses a function such as MIN or MAX, the function within the formula
apparently doesn't know to confine its evaluation to the value appearing in
the referenced column and in the same row." That is one cell. If you name a
single cell, you can refer to that and you'll be referring to only one cell,
but absolutely. I guess I don't understand what you're doing with MIN and
MAX which should refer to more than one cell. You imply that you're using
these functions to refer to one cell. Why?

Tyro
 
You can create a relative reference with a named range. For example. I
enter the values 1, 2 and 3 in cells A1, A2 and A3. I select cell B2. Then I
define a range, cell A1, with the name MyCell and change the absolute
reference $A$1 that Excel creates to the relative reference A1 which is now
relative to cell B2. In cell B2 I enter the formula =MyCell + 1 which shows
the value 2. Then I drag B2 down to B3 and B4. The values 2, 3, 4 appear in
those cells. Try this. It is of paramount importance that you select the
cell, in this case B2, before creating the named range and then changing the
addresses of the named range from absolute to relative for this method to
work. When you change the addresses of the named range to relative, they are
relative to the selected cell, in this case, B2.

Tyro
 
I need the MIN of several values, one of which is the value in a particular
cell within a column which is a named range. The other values that are
evaluated by the MIN (or MAX) function appear elsewhere in the spreadsheet.
 
I appreciate all your efforts, but not sure how this suggestion helps. Each
column in the spreadsheet is basically a time-series. Each row represents
the values that various variables take on at a particular point in the time
series, and they are often interdependent which is why, in column W I may
need to look back to the value in column N. Defining individual cells as
named ranges would, in most cases, not be helpful, because the model is, at
this point, roughly 90 columns by 85 rows. That's a lot of cells!
 
Perhaps you could upload your workbook to http://savefile.com. Show me the
original formula and the replacement formula with the named range. Tell me
where these are located on the sheet.

Tyro
 
Back
Top