Determining position of last number in array

[brownm]

Hi there

I am trying to figure out a way of determining the position of the last number in an array, where that array includes both spaces and error messages at irregular intervals, and includes multiple values with 3 decimal positions. ie. in a column containing the following values, I would want a value of 9.

1 1.012
2 #N/A
3 1.020
4
5 1.010
6 1.001
7 #N/A
8 #N/A
9 1.001
10 #N/A
11 #N/A

​

using =LOOKUP(9.99E+307,A1:A11) I can reliably determine the value of whatever the last number is (in this case 1.001).

I've then tried using =MATCH(LOOKUP(9.99E+307,A1:A11),A1:A11,0) but this returns the first occurrence of 1.001 (in this case 6)

I've heard that you can use LOOKUP again to determine the last matching value, but this option seems to have issues with my use of decimal places.

Any help would be appreciated! Ideally I would like a solution that could work with both vertical and horizontal arrays.

 

[Rick Rothstein]

Hi there

I am trying to figure out a way of determining the position of the last number in an array, where that array includes both spaces and error messages at irregular intervals, and includes multiple values with 3 decimal positions. ie. in a column containing the following values, I would want a value of 9.

1 1.012
2 #N/A
3 1.020
4
5 1.010
6 1.001
7 #N/A
8 #N/A
9 1.001
10 #N/A
11 #N/A

​

using =LOOKUP(9.99E+307,A1:A11) I can reliably determine the value of whatever the last number is (in this case 1.001).

I've then tried using =MATCH(LOOKUP(9.99E+307,A1:A11),A1:A11,0) but this returns the first occurrence of 1.001 (in this case 6)

I've heard that you can use LOOKUP again to determine the last matching value, but this option seems to have issues with my use of decimal places.

Any help would be appreciated! Ideally I would like a solution that could work with both vertical and horizontal arrays.

Give this formula a try...

=LOOKUP(9.9E+307,A1:A1000)

Change the size of the range to handle existing and future values.

 

[Fazza]

If there are a few to do, I might prefer a user defined function via VBA. How does this look? Enter with CTRL-SHIFT-ENTER

=IF(ROWS(rng)=1,MAX(ISNUMBER(rng)*COLUMN(rng))-COLUMN(rng)+1,IF(COLUMNS(rng)=1,MAX(ISNUMBER(rng)*ROW(rng))-ROW(rng)+1,NA()))

 

[brownm]

Thanks Rick, though your solution there gives me the value of the last number (ie. 1.001), whereas I'm after the row number of the last number (ie. 9)

Fazza - I'm not very clued up with VBA, but its looking like I might be needing to delve into it more! I'm struggling to get my head around that formula you've posted. Is it possible to try and explain how it's trying to work it out? And what ranges I should be using?

 

[barry houdini]

Just change LOOKUP to MATCH, i.e.

=MATCH(9.9E+307,A1:A11)

 

[Rick Rothstein]

Just change LOOKUP to MATCH, i.e.

=MATCH(9.9E+307,A1:A11)

Yes... I did not read the original question carefully enough.

 

[collegeitdept]

Hi Rick Rothstein,
When you get a chance, can you please help me a question that I had (no one seems to have replied when I first posted a thread over a week ago).

I was wondering if you could help me with a formula to calculate averages for weeks (currently averages for months)

=SUM(IF(FREQUENCY(IF((ROUNDUP(MONTH($A$2:$A$100)/3,0)=MID(AG$4,2,1)+0)*(YEAR($A$2:$A$100)=VALUE(RIGHT(AG$4,4)))*($C$2:$C$100=AG$5),MONTH($A$2:$A$100)),{1,2,3,4,5,6,7,8,9 ,10,11,12}),1))

http://www.mrexcel.com/forum/excel-questions/748926-question-sum-product-weekly-averages.html


Thanks!

 

[Fazza]

Just change LOOKUP to MATCH, i.e.

=MATCH(9.9E+307,A1:A11)

that looks much better!!

 

[brownm]

Thanks everyone, thats now working awesomely! Simple but effective!