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

  • Thread starter Thread starter Evi
  • Start date Start date
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
 
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.
 
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
 
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
 
-----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.
 
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
 
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
 
I checked Accept Labels in Formulae but it didn't make any difference.

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

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
 
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
 
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
 
Back
Top