# Determining position of last number in array

#### brownm

##### New Member
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.

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.

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

Code:
``=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()))``

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?

Just change LOOKUP to MATCH, i.e.

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

Just change LOOKUP to MATCH, i.e.

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

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

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!

Just change LOOKUP to MATCH, i.e.

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

that looks much better!!

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

Replies
5
Views
156
Replies
10
Views
557
Replies
6
Views
2K
Replies
1
Views
291
Replies
9
Views
379

1,196,269
Messages
6,014,346
Members
441,816
Latest member
Klingon1960

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

### Which adblocker are you using?

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

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