labels in formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In going through an Excel course I'm asked to use column labels in a formula.
I've enabled labels in formulas in the options page. I first set the cells up
with the normal cell designations for the formula, eg., A1*D1, and all was
well. When I changed the formula, per lesson instructions, to the column
labels,=(qty)*(price), the resultant was an astronomicaly large negative
number. The initial numbers were 20 and 4.85 and the subject cell is in the
same row. I tried setting up a dummy worksheet using different numbers and
labels and the same procedure and it worked. Any ideas as to what I may be
doing wrong?
 
It should work just fine, with or without ( ) as
=qty*price
where qty & price are named cells
To name the cell just select and type in a name in the name box to the left
of the formula box
 
I'm trying to use column labels vs. named cells.

Don Guillett said:
It should work just fine, with or without ( ) as
=qty*price
where qty & price are named cells
To name the cell just select and type in a name in the name box to the left
of the formula box
Click to expand...
 
I, for one, am still not clear what you want. If you named an entire column
qty and another price and you want to multiply all in the column * all in
the other column, use the SUMPRODUCT function.
=SUMPRODUCT(I2:I100*J2:J100)
 
Did Price change to capital letters in the formula? Perhaps it's picking
up a built-in function.

Use UnitPrice or Prices for the column heading, and it should work as
expected.
 
Yes, "price" changed to all upper case. but "qty" changed only the "q" in
caps. This worksheet is laid out per instructions in a MS Excel '97 course
book. The intent is to demonstrate using column/row labels in formulas. Named
cells were previously dealt with. My problem is in this situation it doesn't
seem to work, getting either a "NAME" message or a huge number.
I changed "price" to "prices" and it works as advertised. I went into the
function list and found the PRICE function to be financial one involving
securities and their maturity. No wonder the strange number!
I can only surmise that MS has changed that function in the migration from
'97 to XP which is what I'm using.
Thanks to both Don and Debra for your help.

C
 
Back
Top