Simple: how to express this named-range reference?

[nomail1983]

Sigh, I'm drawing a blank....

E10:E30 evaluates to additional cash based on values in U10:U30.
Ostensibly, E11 is U11-U10; similarly for E12:E30.

I have named U10:U30 "Cash". So I want to write that formula in a
form similar to:

=Cash - offset(Cash,-1,0)

Or so I thought. The OFFSET(...) expression is wrong.

What is the correct expression for "the same row minus one in the
named range Cash"?

I am looking for a solution that is readable and self-documenting.

 

[Bob Phillips]

Bit cumbersome, but seems to work.

=OFFSET(INDEX(Cash,1),ROW()-MIN(ROW(Cash))-1,0)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[nomail1983]

Bit cumbersome, but seems to work.
=OFFSET(INDEX(Cash,1),ROW()-MIN(ROW(Cash))-1,0)

That's a start. Thanks. Based on that, I came up with the following:

=index(Cash, row() - row(Cash))

It seems to work. But does it make sense?

ROW(Cash) seems to return the first row number of Cash. I don't seem
to need to do MIN(ROW(Cash)). But I don't know if I'm doing the
"right" thing, or if I simply "got away with it".

I thought that I did something even more straight-forward in the
past. Is my INDEX(...) expression as good as it gets?

(Assuming that it makes good sense, in the first place.)

 

[Bob Phillips]

The reason that I used MIN(ROW(Cash)) was because ROW(Cash) returns an array
of all row numbers in the range Cash. If not used in an array formula, it
seems to just use the first element, but I prefer to be explicit and force
it to do so, hence the MIN.

I tried that INDEX formula at first but it seems to suffer two problems to
me:

- first, it doesn't offset by one row as you seemed to ask

- it repeats the first item as as the row numbers are 0, 1, 2, 3, etc., not
1,2,3, etc., but tjis can be corrected with =INDEX(Cash, ROW() -
ROW(Cash)+1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[nomail1983]

I tried that INDEX formula at first but it seems to suffer two problems
to me:
- first, it doesn't offset by one row as you seemed to ask

It offsets by one row just fine.

If you fill U10:U30 with 1,2,...,21, define the name Cash to refer to
$U$10:$U$30, and fill E11:E30 with =index(Cash,row()-row(Cash)), you
will see that E11:E30 evaluates to 1,2,...,20.

The reason that I used MIN(ROW(Cash)) was because
ROW(Cash) returns an array of all row numbers in the range Cash.

Only if ROW(Cash) were used in an array formula. In your previous
posting, you did not indicate that your solution was an array formula.

 

[Bob Phillips]

It offsets by one row just fine.

If you fill U10:U30 with 1,2,...,21, define the name Cash to refer to
$U$10:$U$30, and fill E11:E30 with =index(Cash,row()-row(Cash)), you
will see that E11:E30 evaluates to 1,2,...,20.

Maybe, but try putting it in E10:E30, you neither get the offset, and the
first value is repoeated. Hence it is not very flexible.

Only if ROW(Cash) were used in an array formula. In your previous
posting, you did not indicate that your solution was an array formula.

No, ROW returns an array regardless. Try evaluating the that part of the
formula to see that this is so.

As I said in my reply, ... if NOT used in an array formula, it seems to
just use the first element, but I prefer to be explicit ... So I didn't
indicate it was an array formula because it wasn't.