Dear All,

I have used absolute references in Excel for many years without any
issues. Today, however, I have come across a problem I have never seen
before.

I have a fomula in cell N162.

The formula is: =SUM($L108:N108)/SUM($L106:N$106)*7

The problem I have is this:

When I drag down or copy this forlmula to cell N163 the following
formula is created:

=SUM($L109:N109)/SUM($L$106:N107)*7

The '$' sign has now gone from the N$106 reference and has been changed
to N107. I required the N$106 to remain the same.

You may suggest that I change it to $N$106, but as I also need to drag
the formula to the cells on the left (and create O$106), I don't
require the column reference to be absolute.

Any suggestions? I do have my formula syntax correct don't I? I don't
recall ever running into this problem before!!

Thanks in advance....

punter1000

Excel is actually incrementing your formula correctly. Whenever you write a
range (A1:A10 for example), the smaller portion of the range (A1) is listed
first, with the larger portion being last (A10). If you were to write
(A10:A1), Excel would adjust this to be (A1:A10).

That is the same thing that is happening in your formula. As it is copied
down, the beginning portion of your range becomes larger than the ending
portion. Therefore, Excel makes the adjustment. The net results should
actually be what you want.

Your formula:
($L106:N$106)

When copied down (would be):
($L107:N$106)

But, since 107 is larger than 106, Excel switches them.
($L$106:N107)

Note that row 106 is still absolute in the formula. And row 107 is still
relative. They've just switched positions.

HTH,
Elkar




" wrote:

Dear All,

I have used absolute references in Excel for many years without any
issues. Today, however, I have come across a problem I have never seen
before.

I have a fomula in cell N162.

The formula is: =SUM($L108:N108)/SUM($L106:N$106)*7

The problem I have is this:

When I drag down or copy this forlmula to cell N163 the following
formula is created:

=SUM($L109:N109)/SUM($L$106:N107)*7

The '$' sign has now gone from the N$106 reference and has been changed
to N107. I required the N$106 to remain the same.

You may suggest that I change it to $N$106, but as I also need to drag
the formula to the cells on the left (and create O$106), I don't
require the column reference to be absolute.

Any suggestions? I do have my formula syntax correct don't I? I don't
recall ever running into this problem before!!

Thanks in advance....

punter1000