Range names not working formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SSkenny - 27 Apr 2007 21:58 GMT

I am hoping that someone can help me with this problem.

I have created several named ranges in excel, I am using excel 2000. Before
anyone asks, I selected the range of cells i.e. c2 to c13, clicked in the
name box and then typed in a name.

Now what happens is when I go to do a simple formula using my named ranges I
get the #value! error message. For example I enter the formula as =gas+food

Using that format generates the error, but I know the ranges will work
because if I do the formula of =sum(gas) or =sum(gas,food) I will get the
result.

Why won't excel recogonize my formula in the format of =gas+food

I have check my options and I do have the option of use labesl in formulas
turned ON.

I am hoping someone knows how to fix this.

Thanks

Reply to this Message

F.H. van Zelm - 27 Apr 2007 22:12 GMT

Hi SSkenny,

A formula will only accept a single cell (or value) for calculation. E.g.
=A1+B1.
You could use named single cells. E.g. =FirstCell+SecondCell.
You cannot use named ranges in formulas, unless you create so called array
formulas. That's a bit 'advanced' .
But you could sum named ranges and then create a formula. E.g.
=SUM(RangeOne)+SUM(RangeTwo).

Hope this helps.

Frans

>I am hoping that someone can help me with this problem.
>
> I have created several named ranges in excel, I am using excel 2000.
> Before
> etc.

Reply to this Message

Ragdyer - 28 Apr 2007 05:55 GMT

The easiest way to explain this is perhaps to substitute the actual range
references for the names.

gas - A1 to A5
food - B1 to B9

You said that this doesn't work:

=gas+food

So, let's look at exactly what you're calculating.

=A1:A5+B1:B9
That's not really a valid formula for XL, so that's why it doesn't work,
even with the names.

However, this is legal:
=Sum(A1:A5,B1:B9)

SO ... use this:
=Sum(gas,food)

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> I am hoping that someone can help me with this problem.
>
[quoted text clipped - 17 lines]
>
> Thanks

Reply to this Message

DaveNeath - 29 Apr 2007 07:51 GMT

> I am hoping that someone can help me with this problem.
>
[quoted text clipped - 17 lines]
>
> Thanks

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You CAN use =gas+food, but only in a particular way.
Assume you have A1 to A5 defined as gas and B1 to B5 defined as food.
If you type =gas+food in a cell in row 1 (e.g. C1) you will get the
sum of A1 and B1,
If you type =gas+food in a cell in row 2 (e.g. C2) you will get the
sum of A2 and B2, and so on...

This is because Excel assumes you are referring to the intersection of
the row of formula cell and the named range.
So you can see that if you try =gas+food in a row that is not included
in the range names then you get #VALUE!

The =sum(gas,food) formula works anywhere because it is referring to
all of the ranges.

Best Regards,
Dave Neath

Reply to this Message