Pulling the Most Recent Cell from a non-contiguous Range

[Academic_Data]

Hello,

I am working on a tool for teachers to track their students most current reading levels. In column G, I need to pull the most recent cell from a non-contiguous range,

I am attempting to use the lookup formula for this, but I am having trouble stating a non-contiguous range. The formula so far looks like: =lookup(99^99,

The cells that I would like to use in the range are M4, P4, V4, AB4, AH4, and AN4.

I attempted to make another table where adjacent cells equaled these values, but that did not work for my lookup since the blank cell in AN4 was being returned as 0 even though the student had a reading level in AH4.

Any advice?

Thanks!

 

[Aladin Akyurek]

Hi Aladin,

This formula seems to be pulling a 0 since AN4 is blank. How might I be able to return the last non-blank cell in the non-contiguous range?

I should have thought of that...

=LOOKUP(9.99999999999999E+307,1/CHOOSE({1,2,3,4,5,6},M4, P4, V4, AB4, AH4, AN4),
   CHOOSE({1,2,3,4,5,6},M4, P4, V4, AB4, AH4, AN4))

For why 9.99999999999999E+307 and not 99^99, see:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

[/code]

 

[Academic_Data]

I should have thought of that...

=LOOKUP(9.99999999999999E+307,1/CHOOSE({1,2,3,4,5,6},M4, P4, V4, AB4, AH4, AN4),
   CHOOSE({1,2,3,4,5,6},M4, P4, V4, AB4, AH4, AN4))

For why 9.99999999999999E+307 and not 99^99, see:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

[/code]

This works perfectly. Would there be a way to edit the formula so that I could in a separate cell pull the first value in a non-contiguous range? Is the formula you provided preferable to a nested IF statement that uses the LEN function to determine whether cells are blank?

For example, here is what I just started using to pull the first value:

=IF(LEN(O4>0),O4,IF(LEN(S4>0),S4,IF(LEN(Y4>0),Y4,IF(LEN(AE4>0),AE4,IF(LEN(AK4>0),AK4,"No Data")))))

 

[Aladin Akyurek]

This works perfectly. Would there be a way to edit the formula so that I could in a separate cell pull the first value in a non-contiguous range? Is the formula you provided preferable to a nested IF statement that uses the LEN function to determine whether cells are blank?

For example, here is what I just started using to pull the first value:

=IF(LEN(O4>0),O4,IF(LEN(S4>0),S4,IF(LEN(Y4>0),Y4,IF(LEN(AE4>0),AE4,IF(LEN(AK4>0),AK4,"No Data")))))

1) The LOOKUP formula you already have gets the last non-zero numeric value:

=LOOKUP(9.99999999999999E+307,1/CHOOSE({1,2,3,4,5},O4,S4,Y4,AE4,AK4),
  CHOOSE({1,2,3,4,5},O4,S4,Y4,AE4,AK4))

2) The following would get the first non-zero numeric value:

=INDEX(CHOOSE({1,2,3,4,5},O4,S4,Y4,AE4,AK4),
  MATCH(TRUE,ISNUMBER(1/CHOOSE({1,2,3,4,5},O4,S4,Y4,AE4,AK4)),0))

The latter must be confirmed with control+shift+enter, not just enter.
3) The following IF formula will fetch the first numeric value:

=IF(LEN(O4),O4,IF(LEN(S4),S4,IF(LEN(Y4),Y4,IF(LEN(AE4),AE4,
  IF(LEN(AK4),AK4,"No Data")))))

 

[Academic_Data]

1) The LOOKUP formula you already have gets the last non-zero numeric value:

=LOOKUP(9.99999999999999E+307,1/CHOOSE({1,2,3,4,5},O4,S4,Y4,AE4,AK4),
  CHOOSE({1,2,3,4,5},O4,S4,Y4,AE4,AK4))

2) The following would get the first non-zero numeric value:

=INDEX(CHOOSE({1,2,3,4,5},O4,S4,Y4,AE4,AK4),
  MATCH(TRUE,ISNUMBER(1/CHOOSE({1,2,3,4,5},O4,S4,Y4,AE4,AK4)),0))

The latter must be confirmed with control+shift+enter, not just enter.
3) The following IF formula will fetch the first numeric value:

=IF(LEN(O4),O4,IF(LEN(S4),S4,IF(LEN(Y4),Y4,IF(LEN(AE4),AE4,
  IF(LEN(AK4),AK4,"No Data")))))

Thanks! Would IF(LEN) statements like this potentially slow down the workbook? I have read that it can cause calculation issues and have experienced slow worksheets in the past due to very long nested IF statements. Would several statements of this size be insignificant?

 

[Aladin Akyurek]

Thanks! Would IF(LEN) statements like this potentially slow down the workbook? I have read that it can cause calculation issues and have experienced slow worksheets in the past due to very long nested IF statements. Would several statements of this size be insignificant?

IF(LEN(A2),A2,"")

is a simple formula expression. Thousands of such might affect performance indeed. For performance issues, have a look at the Charles Williams site, decisionmodels.com.

If possible, try to avoid non-contiguous ranges which lead to expensive processing.