Range name confusion

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I'm using Excel XP. I have a general question regarding
range names.

Suppose I have the following in a spreadsheet:

Qty Price Sales
5 .75 =Quantity*Price
7 .80 =Quantity*Price
3 .50 =Quantity*Price
..
..
etc.

The first column is named "Quantity" and the second is
named "Price". Column 'C' contains the
formula "Quantity*Price". When I type the formula in 'C1'
and copy it down, the formula works correctly, multiplying
each value in column 'A' with the corresponding value in
column 'B'. My question is: Why? Or rather, how?

If you click on "Insert/Name/Define" from the menu, and
click on either 'Quantity' or 'Price', the formula
referenced is absolute. I would think the row part of the
formula would have to be relative for the formula to
work. How does Excel know, for instance, to multiply the
value in 'A7' with the value in 'B7', and so forth all the
way down the list?

Thanks for any and all help.
 
Craig,

This isn't much of an answer, but "that's just the way it works." It only
works when your reference to the range name is in any row of the range. YOu
get the element from the same row as the formula referring to it. Same
with:

=A5:A15

This only works in rows 5 - 15. In row 6, it yields an error.
 
Craig,

Well, damn. That didn't make sense. "In row 6, it yields an error" is
wrong. In row 6 it works. In any row outside of 5 - 15 it yields an error.
 
In my Excel manual for Version 5.0 its called 'Implicit Intersection' and is
covered on page 145/146 ; referring to Intersecting Ranges.

But I have to admit I cannot find it in help!

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
harles.

Right. My statement about getting the value from the same row isn't
necessarily the case. If the range referred to in the formula is a row,
instead of a column, you'll get the value from the same column.
 
Back
Top