Address of last cell with data that is formulated

[plokplok]

Hi All,

So have to admit defeat on this case.

I am trying to find the address of the last cell that contains numeric data in a selection between CT30:IQ30 that is not 0. (Note that data is set by formulas, not direct numbers)

I currently have the following formula:
=CELL("address",INDEX(CT30:IQ30,MATCH(TRUE,CT30:IQ30<>0,1)))

However this returns the result $IQ$30 which is the last cell in the selection. I suspect this is because the data is generated by formula, and not hardcoded as the formula works correctly when i try with direct data.

Appreciate any help.

 

[seekerarcane]

Mentioned formula works perfectly as an Array Formula, whether the data is generated by formula or direct data.

Press F2 in formula cell, and then Press CRT+SHIFT+ENTER to make it an array formula.

 

[Aladin Akyurek]

Care to post the formula you have in CT30:IQ30? Also, why the cell address instead of the value the cell houses?

 

[plokplok]

Unfortunately i dont have the exact formula at home, however the general formula was:
if(today()>A2, sum(A3:A15),0)

A2 = Is a weekly date cell, ie: 01/03/2017

Logic was:
1: Check if todays date is after the date, if it is then sum a bunch of finances and give me the result. If the above date is in the future, then dont sum results and return 0.

ideally i'd like the cell address of the last cell within CT30:IQ30 that has actual finances in it, as I will be then pulling that cell reference to have an automatically updating graph of finance over time that adjusts its timescale based on when new finances have been applied.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=CELL("address",INDEX(CT30:IQ30,MATCH(TRUE,ISNUMBER(1/CT30:IQ30),0)))

 

[plokplok]

Thanks Aladin, i think this may be the solution.
I have done a mini recreate of my problem and your solution appears to work. Will do final test on Monday with actual file to 100% confirm and let u know.

In mean time, for my own reference, can you explain what the 1/CT30:IQ30 portion does? Unsure what thats saying..

 

[Aladin Akyurek]

Thanks Aladin, i think this may be the solution.
I have done a mini recreate of my problem and your solution appears to work. Will do final test on Monday with actual file to 100% confirm and let u know.

In mean time, for my own reference, can you explain what the 1/CT30:IQ30 portion does? Unsure what thats saying..

1. Dividing 1 by non-zero numbers ends up in no-zero numbers; dividing by 0 in a #DIV/0!; and dividing by text values in #VALUE! errors.

2. ISNUMBER test returns TRUE for numbers, FALSE for all else.

Hence ISNUMBER(1/range).

 

[plokplok]

So an interesting update. The formula works in a small sample and I have validated the values are as expected across my whole timeline.
IE: Future values all show #Div/0 while past values show a number.

However when i expand this formula out to cover more then 10 spaces, it automatically changes the result to be the final address in the selection(IQ30) rather then the last TRUE value which is DE30.

I have replicated this problem in a brand new simplified spreadsheet, so I'm confident its not a bug with my master sheet.

 

[plokplok]

Quick update, I'm still not sure why the above formula stops working when expanded, however have worked out another way to get the same result.
For reference for anyone else who looks at this, i used the MAX function:
=CELL("ADDRESS",INDEX(CT30:IQ30,MATCH(MAX(CT30:IQ30),CT30:IQ30,0)))

I realise this only works due to my figures being in ascending order.

Thanks all for your help with this. Aladin, you put me on the right track to work this out.

 

[Aladin Akyurek]

Quick update, I'm still not sure why the above formula stops working when expanded, however have worked out another way to get the same result.
For reference for anyone else who looks at this, i used the MAX function:
=CELL("ADDRESS",INDEX(CT30:IQ30,MATCH(MAX(CT30:IQ30),CT30:IQ30,0)))

I realise this only works due to my figures being in ascending order.

Thanks all for your help with this. Aladin, you put me on the right track to work this out.

Are you trying to return the cell address of the last non-zero numeric value? If so:

Control+shift+enter, not just enter:

=CELL("address",INDEX(CT30:IQ30,MATCH(9.99999999999999E+307,CT30:IQ30)))

Or if it's hard for you to remember control+shift+enter, then just enter:

=CELL("address",INDEX(CT30:IQ30,MATCH(9.99999999999999E+307,INDEX(1/CT30:IQ30,0))))

​