Problem using named range in Offset

[JenniferMurphy]

I thought I understood this from some previous discussions here, but apparently I do not.

This table shows three different ways to calculate the number of days between two dates. Column C has been assigned the name "Date".

The first one (Col D), uses relative cell addresses. The second uses the named range for the first term, but a cell reference in the Offset call. Both of these work.

The third one, which uses the named range in the Offset call, gets an error.

R/C	C	D	E	F	G	H	I
5	Date	Days	Formula	Days	Formula	Days	Formula
6	4/06/20​	5	D6: =C6-C7	5	F6: =Date-OFFSET(C6,1,0)	#REF!​	H6: =Date-(OFFSET(Date,1,0))
7	4/01/20​	40	D7: =C7-C8	40	F7: =Date-OFFSET(C7,1,0)	#REF!​	H7: =Date-(OFFSET(Date,1,0))
8	2/21/20​	98	D8: =C8-C9	98	F8: =Date-OFFSET(C8,1,0)	#REF!​	H8: =Date-(OFFSET(Date,1,0))
9	11/15/19​	n/a	n/a	n/a	n/a		n/a

I tried several variations that I thought were suggested by various people here, but none of them work.

The expression

=Date-(OFFSET(+Date,1,0))

, gets an error.

My attempt to use intersect operator,

=Date-(row(H6)+1 Date)

, gets an error.

Is there a simple expression to subtract the date in the previous row from the one in the current row that uses only the named range (no cell references)?

 

[jasonb75]

See if this works,

=Date-(OFFSET(Date,1,0,1,1))

I Tried copying your sample to excel but I'm getting #VALUE! instead of #REF! which is because the named range refers to more than 1 cell, not sure why I get different a error to you, perhaps it's the way that you have defined the name.

 

[jasonb75]

The proverbial penny has just dropped, if you have defined 'Date' as = $C:$C then it will return #REF because offsetting an entire column by 1 row takes you off of the bottom of the sheet.
C:C is the same as C1:C1048576 a 1 row offset would be C2:C1048577. As C1048577 does not exist, it creates the #REF! error.

With this in mind, a revision to the formula that I suggested which I think should do the trick.

=Date-(OFFSET(Date,ROW(),0,1,1))

or

=Date-INDEX(Date,ROW()+1)

Both should yield the same result.

 

[JenniferMurphy]

See if this works,

=Date-(OFFSET(Date,1,0,1,1))

Nope. It returns the value in the Date column in that row. For row 6, I get 43927, which is the internal number for 4/06/20. I tried just =OFFSET(Date,1,0,1,1). It returns Zero,

 

[JenniferMurphy]

The proverbial penny has just dropped, if you have defined 'Date' as = $C:$C then it will return #REF because offsetting an entire column by 1 row takes you off of the bottom of the sheet.
C:C is the same as C1:C1048576 a 1 row offset would be C2:C1048577. As C1048577 does not exist, it creates the #REF! error.

OK, if you say so.

With this in mind, a revision to the formula that I suggested which I think should do the trick.

=Date-(OFFSET(Date,ROW(),0,1,1))

Yep

or

=Date-INDEX(Date,ROW()+1)

Both should yield the same result.

Yes!!!

I like the second one better as I understand it better.

Thank you

 

[Tetra201]

@ JenniferMurphy: jasonb75 has already provided a working solution.

I will try to explain what's wrong with you intersect approach:

- row(H6) returns number 6;
- row(H6)+1 is 7;
- (7 Date) is not a valid intersect operation;
- for this approach to work, it should be =Date-INDIRECT(ROW()+1&":"&ROW()+1) Date

 

[JenniferMurphy]

@ JenniferMurphy: jasonb75 has already provided a working solution.

I will try to explain what's wrong with you intersect approach:

- row(H6) returns number 6;
- row(H6)+1 is 7;
- (7 Date) is not a valid intersect operation;
- for this approach to work, it should be =Date-INDIRECT(ROW()+1&":"&ROW()+1) Date

For some reason, I cannot get my head around the Intersect operator.

These two expressions work and give the same answer, the contents of C7:

=C:C 7:7
=7:7 C:C

But this one gets an error:

=C:C 7

 

[Tetra201]

Intersect needs two ranges as operands.

7 is not a range, same as C is not a range.

7:7 is a range, and C:C is a range, so =C:C 7:7 works, whereas =C:C 7 does not.

Hope this helps.

 

[JenniferMurphy]

Intersect needs two ranges as operands.

7 is not a range, same as C is not a range.

7:7 is a range, and C:C is a range, so =C:C 7:7 works, whereas =C:C 7 does not.

Hope this helps.

Ok, but then if Date is the name for C:C, why do these work

=C:C 7:7
=Date 7:7

and

=ROW()&":"&ROW()

is "7:7", why do these all get errors?

=date row():row()
=date row()&":"&row()
=date (row()&":"&row())

 

[Tetra201]

This is because when you enter 7:7 in a formula, Excel interprets this as a range (row #7).

If you construct "7:7" as =ROW()&":"&ROW(), its a text string, not row #7, and =range string does not work. For it to work, the string should be wrapped in INDIRECT.