Why can't a range formula be on a different row?

E

Evi

I name cells A2:A4 Range1. I name cells B2:B4 Range2. In C2:C4 I type
=Range1-Range2
I get the right answer.
But if I move down a couple of rows and in A9:A11 I type
=Range1-Range2
I get a #Value error.

Can anyone explain why?
Evi
 
E

Earl Kiosterud

Evi,

My guess is that you're getting only once cell from range1 and one from
range2 in your result. You're asking the formula to subtract a range from a
range, and return a single result. That results in only one value from each
range, if the formula is in the same row.

You may want

=SUM(Range1)-SUM(Range2)

That will work anywhere.
 
K

Kevin Stecyk

Evi,

You must be in the same rows for your formula to work.

Excel depends on symmetry. Otherwise, it doesn't know what to line up with
what.

If you want your formula to work, then you must use an array entered formula

That is, in A9:A11 use the following formula =Range1-Range2. Select
A9:A11.

And then hit "control shift enter" so that the formula appears as

{=Range1-Range2}

Now, Excel will better understand your intent.

Excel puts the brackets in for you, so just type the equation as per normal
and then do the control shift enter.

Best regards,
Kevin
 
B

Bernard V Liengme

Hi Evi,
I think you must have Allow Labels in Formulas checked (see Tools|Options
and open the Calculations tab). This was a much-heralded innovation with
Excel97 but MS has since realise the trouble it causes (problems like yours)
and by default it is now switch off.
Bernard
 
G

Guest

-----Original Message-----
I name cells A2:A4 Range1. I name cells B2:B4 Range2. In C2:C4 I type
=Range1-Range2
I get the right answer.
But if I move down a couple of rows and in A9:A11 I type
=Range1-Range2
I get a #Value error.

Can anyone explain why?
Evi


.That I know of, you can't add/subtract "ranges" --
Click to expand...
unless the range refers to a numeric value . . .

Sum(Range1)-sum(Range2) = calculated value

Range1-Range2 can't really calculate anything -- unless
they refer to a single value. You said you made a range
out of 4 rows -- that's not a single value.
 
K

Kevin Stecyk

Anon,

<<That I know of, you can't add/subtract "ranges" -- >>

Unless I am misinterpreting your message, you are incorrect.

You can add and subtract ranges willy nilly, so long as the ranges contain
numeric data.

In fact, in the original message A2:A4 & B2:B4 were added together in C2:C4.

Go ahead and try, see for yourself.

Try this:
A2 = 1
A3 = 2
A4 = 3

A2:A4 named Range1

B2 = 4
B3 = 5
B4 = 6

B2:B4 named Range2

C2 = Range1+Range2 => 5
C3 = Range1+Range2 => 7
C4 = Range1+Range2 => 9

<< Range1-Range2 can't really calculate anything -- unless they refer to a
single value. You said you made a range out of 4 rows -- that's not a
single value.>>

I think you need to test this for yourself, and then you will realize your
error.

As I indicated in my earlier message, simply transforming the equation to an
array entered equation will address the issue.

Regards,
Kevin
 
E

Evi

Thanks for having a bash at this Earl but the formula works correctly if I
do it on the same rows as the ranges. I get a different result for each
line. Try it out as I describe in my message. The problem isn't getting
just one result but getting a #VALUE error if I try the formula out in a
different row.
Evi
 
E

Evi

I checked Accept Labels in Formulae but it didn't make any difference.

I get the #VALUE error both in Excel97 and ExcelXP.

Evi
 
E

Evi

I selected A9:A11 and pressed Ctrl + Shift + Enter but nothing happened, I
didn't get the brackets. I'm using Excel 97 here at home. When I get back to
college I'll try this on Excel XP
Evi
 
E

Evi

Ah, I see, I have to do the Ctrl, Shift, Enter in the Formula bar. And
you're right. It does work!
My tutors at college were stumped by this one too so they will be very
interested in your solution.
Evi
 
K

Kevin Stecyk

Evi,

Glad you were able to bash your challenge into submission.

With regards to Control Shift Enter, I do it so often that it is
instinctive. I just seem to hit those three keys, and presto, I have a
formula entered array equation.

The trick is to ensure that you have your entire range selected before
creating an array entered equation.

I will let you experiment what happens when you don't.

Again, glad you solved your problem.

Best regards,
Kevin
 
Top