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

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
657
Office Version
  1. 2019
Platform
  1. Windows
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
JKLMN
19G/L $RankTESTLast Non-Blankaddress of
20-$5.45554.55J27
21-$15.0066
22-$0.4533
23$4.5522
24$9.1011
25-$5.0044
26-$5.0044
27$4.5522
28   
29   
30   
31   
32   
33   
34   
35   
36   
37   
NBA Betting Ranks
Cell Formulas
RangeFormula
J20:J37J20=IF(K20<>"",(F20*4.55)-(G20*5),"")
K20:K37K20=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$27)/COUNTIF($J$20:$J$27,$J$20:$J$27)),"")
L20:L37L20=IF(E20<>"",SUMPRODUCT((J20<=$J$20:$J$27)/COUNTIF($J$20:$J$27,$J$20:$J$27)),"")
M20M20=LOOKUP(2,1/(J20:J51<>""),J20:J51)
N20N20="J"&MATCH(2,1/(J:J<>""),1)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
gtd526 said:
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.
Click to expand...
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
JKL
19G/L $RankTEST
20($5.45)55
21($15.00)66
22($0.45)33
23$4.5522
24$9.1011
25($5.00)44
26($5.00)44
27$4.5522
TEST
Cell Formulas
RangeFormula
L20:L27L20=IF(E20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
 
Upvote 0
Peter_SSs said:
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
JKL
19G/L $RankTEST
20($5.45)55
21($15.00)66
22($0.45)33
23$4.5522
24$9.1011
25($5.00)44
26($5.00)44
27$4.5522
TEST
Cell Formulas
RangeFormula
L20:L27L20=IF(E20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
Click to expand...
Hello,
I added your formula but I'm getting a decimal? Some values have been added, it done automatically.

Wager History.xlsm
JKL
19G/L $RankTEST
20-$5.6860.5
21-$10.4571
22$4.1030.5
23$4.5520.5
24$9.1011
25-$1.3541
26-$5.0050.25
27$4.1030.5
28-$5.6860.5
29-$5.0050.25
30-$5.0050.25
31-$5.0050.25
32$4.5520.5
33  
34  
35  
36  
37  
38  
NBA Betting Ranks
Cell Formulas
RangeFormula
J20:J38J20=IF(E20<>"",(F20*4.55)-(G20*5),"")
K20:K38K20=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$32)/COUNTIF($J$20:$J$32,$J$20:$J$32)),"")
L20:L32L20=IF(E20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
 
Upvote 0
gtd526 said:
I added your formula but I'm getting a decimal?
Click to expand...
Yes, I forgot to allow for all the 'blank' formulas below your numerical data. Try this instead.

Excel Formula: 
=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
 
Upvote 0
Peter_SSs said:
Yes, I forgot to allow for all the 'blank' formulas below your numerical data. Try this instead.

Excel Formula: 
=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
Click to expand...
Its still showing a decimal.
Wager History.xlsm
JKL
19G/L $RankTEST
20-$5.6860.5
21-$10.4571
22$4.1030.5
23$4.5520.5
24$9.1011
25-$1.3541
26-$5.0050.25
27$4.1030.5
28-$5.6860.5
29-$5.0050.25
30-$5.0050.25
31-$5.0050.25
32$4.5520.5
33   
34   
35   
NBA Betting Ranks
Cell Formulas
RangeFormula
J20:J35J20=IF(E20<>"",(F20*4.55)-(G20*5),"")
K20:K35K20=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$32)/COUNTIF($J$20:$J$32,$J$20:$J$32)),"")
L20:L35L20=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
 
Upvote 0
gtd526 said:
Its still showing a decimal.
Click to expand...
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
JKL
19G/L $RankTEST
20($5.68)66
21($10.45)77
22$4.1033
23$4.5522
24$9.1011
25($1.35)44
26($5.00)55
27$4.1033
28($5.68)66
29($5.00)55
30($5.00)55
31($5.00)55
32$4.5522
33  
34  
35  
Sheet1
Cell Formulas
RangeFormula
J33:J35J33=IF(E33<>"",(F33*4.55)-(G33*5),"")
L20:L35L20=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
 
Upvote 0
Peter_SSs said:
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
JKL
19G/L $RankTEST
20($5.68)66
21($10.45)77
22$4.1033
23$4.5522
24$9.1011
25($1.35)44
26($5.00)55
27$4.1033
28($5.68)66
29($5.00)55
30($5.00)55
31($5.00)55
32$4.5522
33  
34  
35  
Sheet1
Cell Formulas
RangeFormula
J33:J35J33=IF(E33<>"",(F33*4.55)-(G33*5),"")
L20:L35L20=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
Click to expand...
Here's the sheet.

Wager History.xlsm
DEFGHIJKL
18ALL Reg Bets: 2+ Bets & +60% (by Highlights)
19BetsTeamWLPshW %G/L $RankTEST
200MEM000 $0.0041
214NOP13025%-$10.4591
226UTA33050%-$1.3561
235CLE2.52.5150%-$1.1351
243SAC21067%$4.1030.5
251TOR100100%$4.5520.5
262GSW200100%$9.1011
271BOS0100%-$5.0070.25
283MIL21067%$4.1030.5
294PHX1.52.5138%-$5.6881
301DEN0100%-$5.0070.25
311POR0100%-$5.0070.25
321OKC0100%-$5.0070.25
331IND100100%$4.5520.5
34         
35         
36         
37         
NBA Betting Ranks
Cell Formulas
RangeFormula
D20:D37D20=IFERROR($F20+$G20,"")
E20:E37E20=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:F37F20=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:G37G20=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:H37H20=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:I37I20=IFERROR($F20/($F20+$G20),"")
J20:J37J20=IF(E20<>"",(F20*4.55)-(G20*5),"")
K20:K37K20=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$32)/COUNTIF($J$20:$J$32,$J$20:$J$32)),"")
L20:L37L20=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")
 
Upvote 0
gtd526 said:
Here's the sheet.
Click to expand...
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
FGJMN
19WLG/L $
2000$0.000.00000
2113($10.45)-10.45000
2233($1.35)-1.35000
232.52.5($1.13)-1.12500
2421$4.104.10000
2510$4.554.55000
2620$9.109.10000
2701($5.00)-5.00000
2821$4.104.10000
291.52.5($5.68)-5.67500
3001($5.00)-5.00000
3101($5.00)-5.00000
3201($5.00)-5.00000
3310$4.554.55000
Sheet3
Cell Formulas
RangeFormula
J20:J33J20=IF(E20<>"",(F20*4.55)-(G20*5),"")
N20:N33N20=(F20*4.55)-(G20*5)
 
Upvote 0
Peter_SSs said:
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
FGJMN
19WLG/L $
2000$0.000.00000
2113($10.45)-10.45000
2233($1.35)-1.35000
232.52.5($1.13)-1.12500
2421$4.104.10000
2510$4.554.55000
2620$9.109.10000
2701($5.00)-5.00000
2821$4.104.10000
291.52.5($5.68)-5.67500
3001($5.00)-5.00000
3101($5.00)-5.00000
3201($5.00)-5.00000
3310$4.554.55000
Sheet3
Cell Formulas
RangeFormula
J20:J33J20=IF(E20<>"",(F20*4.55)-(G20*5),"")
N20:N33N20=(F20*4.55)-(G20*5)
Click to expand...
One more thing to test, in an empty cell, what does this formula return?
=COUNT(J20:J1000)
It returns 14.
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question
Create List of Cell Values with Criteria
Replies
1
Views
226
gtd526
gtd526
M
  • Solved
Update formula on mouse click without macro
Replies
3
Views
204
Fluff
Fluff
M
  • Question
Lookup based on Person in top row
Replies
1
Views
84
awoohaw
awoohaw
K
  • Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
233
Eric W
Eric W
N
  • Question
Count - specific consecutive order
Replies
6
Views
486
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,601
Messages
6,120,462
Members
448,965
Latest member
grijken

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top