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.

 

[MCLIFTO8]

INDIRECT("$J$20:" & MAX((N:N<>"")*ROW(N:N)))

 

[Peter_SSs]

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.

To do that and use it will involve using volatile functions like INDIRECT as suggested above. I would be trying to avoid using volatile functions if possible.

What about something like this instead?

22 12 05.xlsm
	J	K	L
19	G/L $	Rank	TEST
20	($5.45)	5	5
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
TEST

Cell Formulas
Range	Formula
L20:L27	L20	=IF(E20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")

 

[gtd526]

To do that and use it will involve using volatile functions like INDIRECT as suggested above. I would be trying to avoid using volatile functions if possible.

What about something like this instead?

22 12 05.xlsm
	J	K	L
19	G/L $	Rank	TEST
20	($5.45)	5	5
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
TEST

Cell Formulas
Range	Formula
L20:L27	L20	=IF(E20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")

Hello,
I added your formula but I'm getting a decimal? Some values have been added, it done automatically.

Wager History.xlsm
	J	K	L
19	G/L $	Rank	TEST
20	-$5.68	6	0.5
21	-$10.45	7	1
22	$4.10	3	0.5
23	$4.55	2	0.5
24	$9.10	1	1
25	-$1.35	4	1
26	-$5.00	5	0.25
27	$4.10	3	0.5
28	-$5.68	6	0.5
29	-$5.00	5	0.25
30	-$5.00	5	0.25
31	-$5.00	5	0.25
32	$4.55	2	0.5
33
34
35
36
37
38
NBA Betting Ranks

Cell Formulas
Range	Formula
J20:J38	J20	=IF(E20<>"",(F20*4.55)-(G20*5),"")
K20:K38	K20	=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$32)/COUNTIF($J$20:$J$32,$J$20:$J$32)),"")
L20:L32	L20	=IF(E20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")

 

[Peter_SSs]

I added your formula but I'm getting a decimal?

Yes, I forgot to allow for all the 'blank' formulas below your numerical data. Try this instead.

=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")

 

[gtd526]

Yes, I forgot to allow for all the 'blank' formulas below your numerical data. Try this instead.

=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")

Its still showing a decimal.

Wager History.xlsm
	J	K	L
19	G/L $	Rank	TEST
20	-$5.68	6	0.5
21	-$10.45	7	1
22	$4.10	3	0.5
23	$4.55	2	0.5
24	$9.10	1	1
25	-$1.35	4	1
26	-$5.00	5	0.25
27	$4.10	3	0.5
28	-$5.68	6	0.5
29	-$5.00	5	0.25
30	-$5.00	5	0.25
31	-$5.00	5	0.25
32	$4.55	2	0.5
33
34
35
NBA Betting Ranks

Cell Formulas
Range	Formula
J20:J35	J20	=IF(E20<>"",(F20*4.55)-(G20*5),"")
K20:K35	K20	=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$32)/COUNTIF($J$20:$J$32,$J$20:$J$32)),"")
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)),"")

 

[Peter_SSs]

Its still showing a decimal.

It is not for me - see below. However, I cannot replicate your sheet exactly as I do not have the data from columns E, F & G which are required to produce the column J values. So I have had to use the values as shown for J20:J32.

Can you post your mini sheet again after hiding columns H & I and then select E19:L35 to produce your mini sheet.
I suspect the issue has to do with rounding of decimal values.

gtd526.xlsm
	J	K	L
19	G/L $	Rank	TEST
20	($5.68)	6	6
21	($10.45)	7	7
22	$4.10	3	3
23	$4.55	2	2
24	$9.10	1	1
25	($1.35)	4	4
26	($5.00)	5	5
27	$4.10	3	3
28	($5.68)	6	6
29	($5.00)	5	5
30	($5.00)	5	5
31	($5.00)	5	5
32	$4.55	2	2
33
34
35
Sheet1

Cell Formulas
Range	Formula
J33:J35	J33	=IF(E33<>"",(F33*4.55)-(G33*5),"")
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)),"")

 

[gtd526]

It is not for me - see below. However, I cannot replicate your sheet exactly as I do not have the data from columns E, F & G which are required to produce the column J values. So I have had to use the values as shown for J20:J32.

Can you post your mini sheet again after hiding columns H & I and then select E19:L35 to produce your mini sheet.
I suspect the issue has to do with rounding of decimal values.

gtd526.xlsm
	J	K	L
19	G/L $	Rank	TEST
20	($5.68)	6	6
21	($10.45)	7	7
22	$4.10	3	3
23	$4.55	2	2
24	$9.10	1	1
25	($1.35)	4	4
26	($5.00)	5	5
27	$4.10	3	3
28	($5.68)	6	6
29	($5.00)	5	5
30	($5.00)	5	5
31	($5.00)	5	5
32	$4.55	2	2
33
34
35
Sheet1

Cell Formulas
Range	Formula
J33:J35	J33	=IF(E33<>"",(F33*4.55)-(G33*5),"")
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)),"")

Here's the sheet.

Wager History.xlsm
	D	E	F	G	H	I	J	K	L
18	ALL Reg Bets: 2+ Bets & +60% (by Highlights)
19	Bets	Team	W	L	Psh	W %	G/L $	Rank	TEST
20	0	MEM	0	0	0		$0.00	4	1
21	4	NOP	1	3	0	25%	-$10.45	9	1
22	6	UTA	3	3	0	50%	-$1.35	6	1
23	5	CLE	2.5	2.5	1	50%	-$1.13	5	1
24	3	SAC	2	1	0	67%	$4.10	3	0.5
25	1	TOR	1	0	0	100%	$4.55	2	0.5
26	2	GSW	2	0	0	100%	$9.10	1	1
27	1	BOS	0	1	0	0%	-$5.00	7	0.25
28	3	MIL	2	1	0	67%	$4.10	3	0.5
29	4	PHX	1.5	2.5	1	38%	-$5.68	8	1
30	1	DEN	0	1	0	0%	-$5.00	7	0.25
31	1	POR	0	1	0	0%	-$5.00	7	0.25
32	1	OKC	0	1	0	0%	-$5.00	7	0.25
33	1	IND	1	0	0	100%	$4.55	2	0.5
34
35
36
37
NBA Betting Ranks

Cell Formulas
Range	Formula
D20:D37	D20	=IFERROR($F20+$G20,"")
E20:E37	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:F37	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:G37	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:H37	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:I37	I20	=IFERROR($F20/($F20+$G20),"")
J20:J37	J20	=IF(E20<>"",(F20*4.55)-(G20*5),"")
K20:K37	K20	=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$32)/COUNTIF($J$20:$J$32,$J$20:$J$32)),"")
L20:L37	L20	=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")

 

[Peter_SSs]

Here's the sheet.

Thanks. Unfortunately that doesn't allow me to fully check since some of those extra formulas that I can now see are drawing from another workbook that I cannot see.
However, I am now almost certain that the problem is related to rounding as I mentioned before.

The values that we can see in the mini sheet in column J are not the true underlying values in those cells. To demonstrate, below I have put the calculation part of the column J formulas in column N. You can see that the two yellow cells are not the same as what is showing in column J. I'm pretty sure that there is more than that though. 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?

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

If nothing still stands out as a likely problem, what happens if you change the column J formula to this?
=IF(E20<>"",ROUND((F20*4.55)-(G20*5),2),"")

gtd526.xlsm
	F	G			J			M	N
19	W	L			G/L $
20	0	0			$0.00				0.00000
21	1	3			($10.45)				-10.45000
22	3	3			($1.35)				-1.35000
23	2.5	2.5			($1.13)				-1.12500
24	2	1			$4.10				4.10000
25	1	0			$4.55				4.55000
26	2	0			$9.10				9.10000
27	0	1			($5.00)				-5.00000
28	2	1			$4.10				4.10000
29	1.5	2.5			($5.68)				-5.67500
30	0	1			($5.00)				-5.00000
31	0	1			($5.00)				-5.00000
32	0	1			($5.00)				-5.00000
33	1	0			$4.55				4.55000
Sheet3

Cell Formulas
Range	Formula
J20:J33	J20	=IF(E20<>"",(F20*4.55)-(G20*5),"")
N20:N33	N20	=(F20*4.55)-(G20*5)

 

[gtd526]

Thanks. Unfortunately that doesn't allow me to fully check since some of those extra formulas that I can now see are drawing from another workbook that I cannot see.
However, I am now almost certain that the problem is related to rounding as I mentioned before.

The values that we can see in the mini sheet in column J are not the true underlying values in those cells. To demonstrate, below I have put the calculation part of the column J formulas in column N. You can see that the two yellow cells are not the same as what is showing in column J. I'm pretty sure that there is more than that though. 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?

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

If nothing still stands out as a likely problem, what happens if you change the column J formula to this?
=IF(E20<>"",ROUND((F20*4.55)-(G20*5),2),"")

gtd526.xlsm
	F	G			J			M	N
19	W	L			G/L $
20	0	0			$0.00				0.00000
21	1	3			($10.45)				-10.45000
22	3	3			($1.35)				-1.35000
23	2.5	2.5			($1.13)				-1.12500
24	2	1			$4.10				4.10000
25	1	0			$4.55				4.55000
26	2	0			$9.10				9.10000
27	0	1			($5.00)				-5.00000
28	2	1			$4.10				4.10000
29	1.5	2.5			($5.68)				-5.67500
30	0	1			($5.00)				-5.00000
31	0	1			($5.00)				-5.00000
32	0	1			($5.00)				-5.00000
33	1	0			$4.55				4.55000
Sheet3

Cell Formulas
Range	Formula
J20:J33	J20	=IF(E20<>"",(F20*4.55)-(G20*5),"")
N20:N33	N20	=(F20*4.55)-(G20*5)

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