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



## gtd526 (Nov 17, 2022)

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.xlsmJKLMN19G/L $RankTESTLast Non-Blankaddress of20-$5.45554.55J2721-$15.006622-$0.453323$4.552224$9.101125-$5.004426-$5.004427$4.552228   29   30   31   32   33   34   35   36   37   NBA Betting RanksCell FormulasRangeFormulaJ20: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.


----------



## MCLIFTO8 (Dec 4, 2022)

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


----------



## Peter_SSs (Dec 5, 2022)

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.


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.xlsmJKL19G/L $RankTEST20($5.45)5521($15.00)6622($0.45)3323$4.552224$9.101125($5.00)4426($5.00)4427$4.5522TESTCell FormulasRangeFormulaL20:L27L20=IF(E20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")


----------



## gtd526 (Dec 6, 2022)

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.xlsmJKL19G/L $RankTEST20($5.45)5521($15.00)6622($0.45)3323$4.552224$9.101125($5.00)4426($5.00)4427$4.5522TESTCell FormulasRangeFormulaL20:L27L20=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.xlsmJKL19G/L $RankTEST20-$5.6860.521-$10.457122$4.1030.523$4.5520.524$9.101125-$1.354126-$5.0050.2527$4.1030.528-$5.6860.529-$5.0050.2530-$5.0050.2531-$5.0050.2532$4.5520.533  34  35  36  37  38  NBA Betting RanksCell FormulasRangeFormulaJ20: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)),"")


----------



## Peter_SSs (Dec 6, 2022)

gtd526 said:


> 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 (Dec 6, 2022)

Peter_SSs said:


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


Its still showing a decimal.
Wager History.xlsmJKL19G/L $RankTEST20-$5.6860.521-$10.457122$4.1030.523$4.5520.524$9.101125-$1.354126-$5.0050.2527$4.1030.528-$5.6860.529-$5.0050.2530-$5.0050.2531-$5.0050.2532$4.5520.533   34   35   NBA Betting RanksCell FormulasRangeFormulaJ20: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)),"")


----------



## Peter_SSs (Dec 6, 2022)

gtd526 said:


> 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.xlsmJKL19G/L $RankTEST20($5.68)6621($10.45)7722$4.103323$4.552224$9.101125($1.35)4426($5.00)5527$4.103328($5.68)6629($5.00)5530($5.00)5531($5.00)5532$4.552233  34  35  Sheet1Cell FormulasRangeFormulaJ33: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)),"")


----------



## gtd526 (Dec 7, 2022)

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.
> ...


Here's the sheet.

Wager History.xlsmDEFGHIJKL18ALL Reg Bets: 2+ Bets & +60% (by Highlights)19BetsTeamWLPshW %G/L $RankTEST200MEM000 $0.0041214NOP13025%-$10.4591226UTA33050%-$1.3561235CLE2.52.5150%-$1.1351243SAC21067%$4.1030.5251TOR100100%$4.5520.5262GSW200100%$9.1011271BOS0100%-$5.0070.25283MIL21067%$4.1030.5294PHX1.52.5138%-$5.6881301DEN0100%-$5.0070.25311POR0100%-$5.0070.25321OKC0100%-$5.0070.25331IND100100%$4.5520.534         35         36         37         NBA Betting RanksCell FormulasRangeFormulaD20: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)),"")


----------



## Peter_SSs (Dec 7, 2022)

gtd526 said:


> 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.xlsmFGJMN19WLG/L $2000$0.000.000002113($10.45)-10.450002233($1.35)-1.35000232.52.5($1.13)-1.125002421$4.104.100002510$4.554.550002620$9.109.100002701($5.00)-5.000002821$4.104.10000291.52.5($5.68)-5.675003001($5.00)-5.000003101($5.00)-5.000003201($5.00)-5.000003310$4.554.55000Sheet3Cell FormulasRangeFormulaJ20:J33J20=IF(E20<>"",(F20*4.55)-(G20*5),"")N20:N33N20=(F20*4.55)-(G20*5)


----------



## gtd526 (Dec 8, 2022)

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)
It returns 14.


----------



## gtd526 (Nov 17, 2022)

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.xlsmJKLMN19G/L $RankTESTLast Non-Blankaddress of20-$5.45554.55J2721-$15.006622-$0.453323$4.552224$9.101125-$5.004426-$5.004427$4.552228   29   30   31   32   33   34   35   36   37   NBA Betting RanksCell FormulasRangeFormulaJ20: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.


----------



## Peter_SSs (Dec 9, 2022)

gtd526 said:


> 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.



Peter_SSs said:


> 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?





Peter_SSs said:


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


----------



## gtd526 (Dec 12, 2022)

Peter_SSs said:


> 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.xlsmDEFGHIJKL19BetsTeamWLPshW %G/L $RankTEST202MEM2.000000.000000100%$9.1010.5211BOS0.000001.0000000%-$5.0050.25226NOP2.000004.00000033%-$10.9081231PHI1.000000.000000100%$4.5520.25241NYK1.000000.000000100%$4.5520.25257UTA3.000004.00000043%-$6.3571265CLE2.500002.50000150%-$1.1341273SAC2.000001.00000067%$4.1030.5281TOR1.000000.000000100%$4.5520.25292GSW2.000000.000000100%$9.1010.5303MIL2.000001.00000067%$4.1030.5314PHX1.500002.50000138%-$5.6861321DEN0.000001.0000000%-$5.0050.25331POR0.000001.0000000%-$5.0050.25341OKC0.000001.0000000%-$5.0050.25351IND1.000000.000000100%$4.5520.2536         37         38         39         40         41         42         43         44         45         46         47         48         49         50         51         NBA Betting RanksCell FormulasRangeFormulaD20:D51D20=IFERROR($F20+$G20,"")E20:E51E20=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:F51F20=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:G51G20=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:H51H20=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:I51I20=IFERROR($F20/($F20+$G20),"")J20:J33J20=IF(E20<>"",ROUND((F20*4.55)-(G20*5),2),"")K20:K51K20=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$35)/COUNTIF($J$20:$J$35,$J$20:$J$35)),"")L20:L51L20=IF(J20<>"",SUMPRODUCT(IFERROR((J20<=J$20:J$1000)*(J$20:J$1000<>"")/COUNTIF(J$20:J$1000,J$20:J$1000),0)),"")J34:J51J34=IF(E34<>"",(F34*4.55)-(G34*5),"")Cells with Conditional FormattingCellConditionCell FormatStop If TrueD20:K51Expression=$K20=MIN($K$44:$K$75)textNOD20:K51Expression=AND($D20>=2,$D20<>"",$I20>=0.6,$I20<>"")textNOC19:E19,C17:D17,D18Expression=AND($AO11>0,$AQ11>=2,$AR11>=0.6)textNOC19:E19,C17:D17,D18Cell Value<0textNOI19:K19Expression=#REF!=#REF!textNOI19:K19Expression=AND(#REF!>=0.6,#REF!>1)textNOI19:K19Expression=AND(#REF!<=0.525,#REF!>1)textNO


----------



## Peter_SSs (Dec 12, 2022)

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 (Dec 30, 2022)

Peter_SSs said:


> 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.xlsmDEFGHIJKL19BetsTeamWLPshW %G/L $RankTEST204MIL3.000001.00000075%$8.6533213MEM3.000000.000000100%$13.6511222BOS0.000002.0000000%-$10.001010236NOP2.000004.00000033%-$10.901111241PHI1.000000.000000100%$4.5544251NYK1.000000.000000100%$4.5544267UTA3.000004.00000043%-$6.3599275CLE2.500002.50000150%-$1.1366283SAC2.000001.00000067%$4.1055291TOR1.000000.000000100%$4.5544302GSW2.000000.000000100%$9.1022314PHX1.500002.50000138%-$5.6888321DEN0.000001.0000000%-$5.0077331POR0.000001.0000000%-$5.0077341OKC0.000001.0000000%-$5.0077351IND1.000000.000000100%$4.554436         37         38         NBA Betting RanksCell FormulasRangeFormulaD20:D38D20=IFERROR($F20+$G20,"")E20:E38E20=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:F38F20=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:G38G20=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:H38H20=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:I38I20=IFERROR($F20/($F20+$G20),"")J20:J33J20=IF(E20<>"",ROUND((F20*4.55)-(G20*5),2),"")K20:K38K20=IF(J20<>"",SUMPRODUCT((J20<=$J$20:$J$35)/COUNTIF($J$20:$J$35,$J$20:$J$35)),"")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)),"")J34:J38J34=IF(E34<>"",(F34*4.55)-(G34*5),"")L36:L38L36=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 FormattingCellConditionCell FormatStop If TrueD20:K51Expression=$K20=MIN($K$44:$K$75)textNOD20:K51Expression=AND($D20>=2,$D20<>"",$I20>=0.6,$I20<>"")textNOC19:E19,C17:D17,D18Expression=AND($AO11>0,$AQ11>=2,$AR11>=0.6)textNOC19:E19,C17:D17,D18Cell Value<0textNOI19:K19Expression=#REF!=#REF!textNOI19:K19Expression=AND(#REF!>=0.6,#REF!>1)textNOI19:K19Expression=AND(#REF!<=0.525,#REF!>1)textNO


----------



## Peter_SSs (Dec 30, 2022)

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


----------

