Complete a Range of cells using a formula to find address of last occupied cell

[gtd526]

Hello,
Looking to find the Last Occupied Cell to complete the Range of Cells in a formula.
Using 'Test' L:L. Replace the $J$27 with a formula to find the address of the last non-blank cell. ie $N$20, to complete the formula in L:L.
Thank you.

Wager History.xlsm
	J	K	L	M	N
19	G/L $	Rank	TEST	Last Non-Blank	address of
20	-$5.45	5	5	4.55	J27
21	-$15.00	6	6
22	-$0.45	3	3
23	$4.55	2	2
24	$9.10	1	1
25	-$5.00	4	4
26	-$5.00	4	4
27	$4.55	2	2
28
29
30
31
32
33
34
35
36
37
NBA Betting Ranks

Cell Formulas
Range	Formula
J20:J37	J20	=IF(K20<>"",(F20*4.55)-(G20*5),"")
K20:K37	K20	=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$27)/COUNTIF($J$20:$J$27,$J$20:$J$27)),"")
L20:L37	L20	=IF(E20<>"",SUMPRODUCT((J20<=$J$20:$J$27)/COUNTIF($J$20:$J$27,$J$20:$J$27)),"")
M20	M20	=LOOKUP(2,1/(J20:J51<>""),J20:J51)
N20	N20	="J"&MATCH(2,1/(J:J<>""),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

One more thing to test, in an empty cell, what does this formula return?
=COUNT(J20:J1000)
It returns 14.

Thanks. But that was only one of the things that I asked.

What happens if you re-format columns F & G to show say 5 decimal places? Does it then show any different values in those columns compared to what we can see in your mini sheet above?

... what happens if you change the column J formula to this?
=IF(E20<>"",ROUND((F20*4.55)-(G20*5),2),"")

 

[gtd526]

Thanks. But that was only one of the things that I asked.

I've added the decimal places and changed the formula in J:J as you indicated.
Nothing has changed.

Wager History.xlsm
	D	E	F	G	H	I	J	K	L
19	Bets	Team	W	L	Psh	W %	G/L $	Rank	TEST
20	2	MEM	2.00000	0.00000	0	100%	$9.10	1	0.5
21	1	BOS	0.00000	1.00000	0	0%	-$5.00	5	0.25
22	6	NOP	2.00000	4.00000	0	33%	-$10.90	8	1
23	1	PHI	1.00000	0.00000	0	100%	$4.55	2	0.25
24	1	NYK	1.00000	0.00000	0	100%	$4.55	2	0.25
25	7	UTA	3.00000	4.00000	0	43%	-$6.35	7	1
26	5	CLE	2.50000	2.50000	1	50%	-$1.13	4	1
27	3	SAC	2.00000	1.00000	0	67%	$4.10	3	0.5
28	1	TOR	1.00000	0.00000	0	100%	$4.55	2	0.25
29	2	GSW	2.00000	0.00000	0	100%	$9.10	1	0.5
30	3	MIL	2.00000	1.00000	0	67%	$4.10	3	0.5
31	4	PHX	1.50000	2.50000	1	38%	-$5.68	6	1
32	1	DEN	0.00000	1.00000	0	0%	-$5.00	5	0.25
33	1	POR	0.00000	1.00000	0	0%	-$5.00	5	0.25
34	1	OKC	0.00000	1.00000	0	0%	-$5.00	5	0.25
35	1	IND	1.00000	0.00000	0	100%	$4.55	2	0.25
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
NBA Betting Ranks

Cell Formulas
Range	Formula
D20:D51	D20	=IFERROR($F20+$G20,"")
E20:E51	E20	=IFERROR(LOOKUP(2, 1/((COUNTIF(E$19:$E19,'[My Wagers.xlsm]NBA'!$H$324:$H$501)=0)*('[My Wagers.xlsm]NBA'!$H$324:$H$501<>"")),'[My Wagers.xlsm]NBA'!$H$324:$H$501),"")
F20:F51	F20	=IFERROR(COUNTIFS('[My Wagers.xlsm]NBA'!$A$324:$A$501,"W",'[My Wagers.xlsm]NBA'!$H$324:$H$501,"<>",'[My Wagers.xlsm]NBA'!$H$324:$H$501,$E20)+$H20*0.5,"")
G20:G51	G20	=IFERROR(COUNTIFS('[My Wagers.xlsm]NBA'!$A$324:$A$501,"L",'[My Wagers.xlsm]NBA'!$H$324:$H$501,"<>",'[My Wagers.xlsm]NBA'!$H$324:$H$501,$E20)+$H20*0.5,"")
H20:H51	H20	=IF(E20<>"",COUNTIFS('[My Wagers.xlsm]NBA'!$A$324:$A$501,"Psh",'[My Wagers.xlsm]NBA'!$H$324:$H$501,"<>",'[My Wagers.xlsm]NBA'!$H$324:$H$501,$E20),"")
I20:I51	I20	=IFERROR($F20/($F20+$G20),"")
J20:J33	J20	=IF(E20<>"",ROUND((F20*4.55)-(G20*5),2),"")
K20:K51	K20	=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$35)/COUNTIF($J$20:$J$35,$J$20:$J$35)),"")
L20:L51	L20	=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
J34:J51	J34	=IF(E34<>"",(F34*4.55)-(G34*5),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D20:K51	Expression	=$K20=MIN($K$44:$K$75)	text	NO
D20:K51	Expression	=AND($D20>=2,$D20<>"",$I20>=0.6,$I20<>"")	text	NO
C19:E19,C17:D17,D18	Expression	=AND($AO11>0,$AQ11>=2,$AR11>=0.6)	text	NO
C19:E19,C17:D17,D18	Cell Value	<0	text	NO
I19:K19	Expression	=#REF!=#REF!	text	NO
I19:K19	Expression	=AND(#REF!>=0.6,#REF!>1)	text	NO
I19:K19	Expression	=AND(#REF!<=0.525,#REF!>1)	text	NO

 

[Peter_SSs]

I keep forgetting about some version differences. What if you confirm the formula in L20 with Ctrl+Shift+Enter, not just Enter, before copying it down?

 

[gtd526]

I keep forgetting about some version differences. What if you confirm the formula in L20 with Ctrl+Shift+Enter, not just Enter, before copying it down?

I did not think of an array.....and it WORKS!
Thank you.

Wager History.xlsm
	D	E	F	G	H	I	J	K	L
19	Bets	Team	W	L	Psh	W %	G/L $	Rank	TEST
20	4	MIL	3.00000	1.00000	0	75%	$8.65	3	3
21	3	MEM	3.00000	0.00000	0	100%	$13.65	1	1
22	2	BOS	0.00000	2.00000	0	0%	-$10.00	10	10
23	6	NOP	2.00000	4.00000	0	33%	-$10.90	11	11
24	1	PHI	1.00000	0.00000	0	100%	$4.55	4	4
25	1	NYK	1.00000	0.00000	0	100%	$4.55	4	4
26	7	UTA	3.00000	4.00000	0	43%	-$6.35	9	9
27	5	CLE	2.50000	2.50000	1	50%	-$1.13	6	6
28	3	SAC	2.00000	1.00000	0	67%	$4.10	5	5
29	1	TOR	1.00000	0.00000	0	100%	$4.55	4	4
30	2	GSW	2.00000	0.00000	0	100%	$9.10	2	2
31	4	PHX	1.50000	2.50000	1	38%	-$5.68	8	8
32	1	DEN	0.00000	1.00000	0	0%	-$5.00	7	7
33	1	POR	0.00000	1.00000	0	0%	-$5.00	7	7
34	1	OKC	0.00000	1.00000	0	0%	-$5.00	7	7
35	1	IND	1.00000	0.00000	0	100%	$4.55	4	4
36
37
38
NBA Betting Ranks

Cell Formulas
Range	Formula
D20:D38	D20	=IFERROR($F20+$G20,"")
E20:E38	E20	=IFERROR(LOOKUP(2, 1/((COUNTIF(E$19:$E19,'[My Wagers.xlsm]NBA'!$H$324:$H$501)=0)*('[My Wagers.xlsm]NBA'!$H$324:$H$501<>"")),'[My Wagers.xlsm]NBA'!$H$324:$H$501),"")
F20:F38	F20	=IFERROR(COUNTIFS('[My Wagers.xlsm]NBA'!$A$324:$A$501,"W",'[My Wagers.xlsm]NBA'!$H$324:$H$501,"<>",'[My Wagers.xlsm]NBA'!$H$324:$H$501,$E20)+$H20*0.5,"")
G20:G38	G20	=IFERROR(COUNTIFS('[My Wagers.xlsm]NBA'!$A$324:$A$501,"L",'[My Wagers.xlsm]NBA'!$H$324:$H$501,"<>",'[My Wagers.xlsm]NBA'!$H$324:$H$501,$E20)+$H20*0.5,"")
H20:H38	H20	=IF(E20<>"",COUNTIFS('[My Wagers.xlsm]NBA'!$A$324:$A$501,"Psh",'[My Wagers.xlsm]NBA'!$H$324:$H$501,"<>",'[My Wagers.xlsm]NBA'!$H$324:$H$501,$E20),"")
I20:I38	I20	=IFERROR($F20/($F20+$G20),"")
J20:J33	J20	=IF(E20<>"",ROUND((F20*4.55)-(G20*5),2),"")
K20:K38	K20	=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$35)/COUNTIF($J$20:$J$35,$J$20:$J$35)),"")
L20:L35	L20	=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
J34:J38	J34	=IF(E34<>"",(F34*4.55)-(G34*5),"")
L36:L38	L36	=IF(J36<>"",SUMPRODUCT(IFERROR((J36<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D20:K51	Expression	=$K20=MIN($K$44:$K$75)	text	NO
D20:K51	Expression	=AND($D20>=2,$D20<>"",$I20>=0.6,$I20<>"")	text	NO
C19:E19,C17:D17,D18	Expression	=AND($AO11>0,$AQ11>=2,$AR11>=0.6)	text	NO
C19:E19,C17:D17,D18	Cell Value	<0	text	NO
I19:K19	Expression	=#REF!=#REF!	text	NO
I19:K19	Expression	=AND(#REF!>=0.6,#REF!>1)	text	NO
I19:K19	Expression	=AND(#REF!<=0.525,#REF!>1)	text	NO

 

[Peter_SSs]

You're welcome. Glad it works now. Sorry it took me so long to think of that.