Lambda Function that works in a TABLE

[realmike]

Good evening, hope someone can help.

I am trying to create lambda functions that work in various tables in a workbook.

The worksheet calculates "Year To Date" (a running total) and "Previous Year to Date" (YTD from the previous row)
The functions are kind of long (especially the Previous function) and used in many columns in the tables.

These functions work fine, I'd just like to have them as LAMBDA functions stored as names
Failing that, is there a simpler formula for "previous"? (Avoiding volatile functions)

I can't figure out the syntax, and searching online, I haven't found an answer or even syntax guide. Maybe LAMBDA function needs to 'know' the table name and not just column names?

I have a workbook with about 70 sheets and an identical table on each sheet, just different data. (sales production, one sheet per salesperson). I don't want a separate LAMDA function for each table.

Thank you in advance for any guidance!!!

running total.xlsx
	A	B	C
1	Sales	Sales_YTD	Sales_Prev_YTD
2	$ 3,000.00	$ 3,000.00	$ -
3	$ 5,812.50	$ 8,812.50	$ 3,000.00
4	$ 89.06	$ 8,901.56	$ 8,812.50
5	$ 1,181.25	$ 10,082.81	$ 8,901.56
6	$ 15,500.00	$ 25,582.81	$ 10,082.81
7	$ 4,375.00	$ 29,957.81	$ 25,582.81
8	$ 45.00	$ 30,002.81	$ 29,957.81
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=SUM(INDEX([Sales],1):[@Sales])
C2:C8	C2	=IF(ROW()>ROW(Production[#Headers])+1,INDEX([Sales_YTD],ROW()-ROW(Production[#Headers])-1),0)

 

[Eric W]

You can try:

=LET(r,ROW(@[Sales])-ROW(Production[#Headers])-1,IF(r=0,0,INDEX([Sales_YTD],r)))

which is a little shorter. But as you've noticed, there is no good way to reference other rows in a table. The rigmarole using the ROW() function is about the only way to do it.

 

[realmike]

Thanks, Eric! that works, too, and I suppose it is a little shorter....
Next, I tried to define that as a LAMBDA function, this is the type of error I am getting. Any clues what I am doing wrong?

 

[realmike]

Anyone have ideas? Still trying to learn how to use a LAMDA function inside a RABLE

 

[Eric W]

Well, that's not how exactly how to define a LAMBDA. It doesn't have LAMBDA in it. I tried before and didn't get too far, which is why I didn't reply again. But I took another shot at it and came up with this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1
2		A	B	C	D	E
3		a	1	Ant	Cricket	N/A
4		b	2	Beetle	Dragonfly	N/A
5		c	3	Cricket	Earwig	1
6		d	4	Dragonfly	Fly	2
7		e	5	Earwig	Gnat	3
8		f	6	Fly	N/A	4
9		g	7	Gnat	N/A	5
10
Sheet1

Cell Formulas
Range	Formula
E3:E9	E3	=Table_Offset(Table1[#Data],[@C],2,0,"N/A")
F3:F9	F3	=Table_Offset(Table1[#Data],[@C],-2,-1,"N/A")

It's sort of a universal "offset" tool to use within a table. It requires 5 parameters. The data section of the table you're looking at, the origin within the table you want to offset from, the number of rows you want to offset (positive or negative), the number of columns you want to offset (positive or negative), and what you want to display if the cell is outside the table. This is the LAMBDA formula I defined:

=LAMBDA(tbl,orig,row,col,nf,LET(tr,MIN(ROW(tbl)),br,ROWS(tbl)+tr-1,lc,MIN(COLUMN(tbl)),rc,COLUMNS(tbl)+lc-1,or,ROW(orig)+row,oc,COLUMN(orig)+col,IF(OR(or<tr,or>br,oc<lc,oc>rc),nf,INDEX(Sheet1!$1:$1048576,or,oc))))

Create a new name, call it Table_Offset, then put that formula in the Refers to: box.

Let me know how it works.