Is there a better way tro reference a different row in a table?

[JenniferMurphy]

Columns J & K in this table need to reference the cell in the row above the one making the reference. This is the code that Excel generated. It seems to work, but the cell references do not look like the other table expressions. I would think this would come up all the time as in my example for calculating cumulative values. But I couldn't find anything in the Excel help about it. Is there another, better way to do this?

Wages by Profession.xlsx
	G	H	I	J	K
3	#	Top	Rate	Width	Cum Tax
4	0	$0	0.00%	--	$0
5	1	$10,000	10.00%	$10,000	$1,000
6	2	$40,000	12.00%	$30,000	$4,600
7	3	$86,000	22.00%	$46,000	$14,720
8	4	$165,000	24.00%	$79,000	$33,680
9	5	$209,000	32.00%	$44,000	$47,760
10	6	$524,000	35.00%	$315,000	$158,010
11	7	--	37.00%		--
Tax Brackets

Cell Formulas
Range	Formula
J5:J10	J5	=[@Top]-H4
K5:K10	K5	=K4+[@Width]*[@Rate]

 

[Peter_SSs]

What about for
J5:

=[@Top]-OFFSET([@Top],-1,0)

K5:

=OFFSET([@[Cum Tax]],-1,0)+[@Width]*[@Rate]

 

[JenniferMurphy]

What about for
J5:

=[@Top]-OFFSET([@Top],-1,0)

K5:

=OFFSET([@[Cum Tax]],-1,0)+[@Width]*[@Rate]

That's what I used to do before you guys turned me onto tables. Now I can't live without 'em. ??

I was hoping that M$FT had built in a better offset syntax. (sigh) But then, I've been wanting a better offset syntax for all of Excel, something like,

=[@Top]-[@Top](-1,0)
=[@[Cum Tax]](-1,0)+[@Width]*[@Rate]

The offset syntax should be built into the language, not cobbled on afterwards with the Offset function. ?

Thanks

 

[Peter_SSs]

I was hoping that M$FT had built in a better offset syntax. (sigh) But then, I've been wanting a better offset syntax for all of Excel, something like,

=[@Top]-[@Top](-1,0)
=[@[Cum Tax]](-1,0)+[@Width]*[@Rate]

The offset syntax should be built into the language, not cobbled on afterwards with the Offset function. ?

On the contrary, from my point of view, I think there is great merit in consistency. Excel has (I think) nearly 500 functions. Why would you have/want one out of that list that did not use its actual name?
And why the less used and volatile OFFSET function and not, say, the much more commonly used INDEX function?

 

[JenniferMurphy]

On the contrary, from my point of view, I think there is great merit in consistency. Excel has (I think) nearly 500 functions. Why would you have/want one out of that list that did not use its actual name?

I also think there is great merit in consistency, but I don't know what that has to do with this.

Excel has lots of notation "shortcuts". Using your point of view, we should replace all but the basic math operators with functions. A couple of examples:

We already have the implicit intersection operator (@). Let's replace that with an explicit function (Intersect?). Now instead of the compact, but implicit =@rangename, we could have the more explicit, but less compact =Intersect(rangename).

The $ sign controls whether a reference is relative or absolute. Why not replace that with four functions? Instead of the very compact and easy to read =$A$1, we could have the much more awkward =AbsAbs(A1). Instead of =A$1, we would get =RelAbs(A1).

And why the less used and volatile OFFSET function and not, say, the much more commonly used INDEX function?

If someone can come up with a nice clean, clear notation, I would vote to replace Index. That one is so complicated and variable that a function is probably warranted. At least, I cannot immediately see a reasonable notational replacement.

If you are referring to to my application, I don't see how Index is relevant. I need a reference relative to a specific cell, not an intersection. But I assume you are not since you suggested Offset, not Index.

I use Offset all the time, much more than Index, because I have a lot of situations where I am calculating some type of cumulative value.
And I hate it every time. I long for a simple notation that will allow me to access the cell just above another cell.

ymmv

 

[RoryA]

The offset syntax should be built into the language, not cobbled on afterwards with the Offset function

IME it is quite rare to want to refer to a cell strictly by its relation to a cell that is not the cell with the formula in.

Structured referencing, unlike normal formula syntax, is severely lacking in its implementation (but then I find tables annoying for anything other than reference/source data for other things) and I tend to avoid it in tables whenever possible.

 

[JenniferMurphy]

IME it is quite rare to want to refer to a cell strictly by its relation to a cell that is not the cell with the formula in.

Hmmm... I guess I a quite rare individual as I do it all the time.

How would you do the table in my original post without the Offset function?

Structured referencing, unlike normal formula syntax, is severely lacking in its implementation (but then I find tables annoying for anything other than reference/source data for other things) and I tend to avoid it in tables whenever possible.

Not sure I understand this point. What specifically is lacking in the structured reference syntax that is not lacking in the normal formula syntax? By "structured reference", I assume you mean references to cells within a table.

 

[RoryA]

What specifically is lacking in the structured reference syntax that is not lacking in the normal formula syntax

The thing you asked for. There is no direct way of saying 'the cell above the current row' in structured referencing. In normal syntax you just say C24 rather than C25 for example. If you view that in R1C1 format it equates to something like 'the cell one row up and three columns to the left' There's also no simple way of locking a column or row reference - you have to use the 'double column name in square brackets' syntax which I find somewhat ridiculous.

How would you do the table in my original post without the Offset function?

I'd use a normal cell reference.

 

[JenniferMurphy]

The thing you asked for. There is no direct way of saying 'the cell above the current row' in structured referencing. In normal syntax you just say C24 rather than C25 for example. If you view that in R1C1 format it equates to something like 'the cell one row up and three columns to the left' There's also no simple way of locking a column or row reference - you have to use the 'double column name in square brackets' syntax which I find somewhat ridiculous.

Unless I'm missing something, there is also no way of saying 'the cell above the current row' in normal syntax. If I just say C24 from C25, it is not absolutely tied to C25. That is, it is not absolutely the same as Offset(C24,-1,0). If I move that row (24), most of the time the C24 adjusts. But I have had problems with it not adjusting so I always use Offset.

I'd use a normal cell reference.

I have had problems with that in the past.

 

[Peter_SSs]

Excel has lots of notation "shortcuts". Using your point of view, we should replace all but the basic math operators with functions. A couple of examples:

We already have the implicit intersection operator (@). Let's replace that with an explicit function (Intersect?). Now instead of the compact, but implicit =@rangename, we could have the more explicit, but less compact =Intersect(rangename).

The $ sign controls whether a reference is relative or absolute. Why not replace that with four functions? Instead of the very compact and easy to read =$A$1, we could have the much more awkward =AbsAbs(A1). Instead of =A$1, we would get =RelAbs(A1).

Not at all. My point was about functions, not operators or other notations.