Solved

Help with find the last number(s) in a tabel

Posted on 2013-12-11
8
204 Views
Last Modified: 2013-12-11
Folks I have a table ranged K3:P7 with numerous numbers.
I also have a table K11:L15 when K11:K15 is numbered 3,4,5,6,7 to correspond to the row numbers.
In L11:L15 I want to place the last values from each row in my K3:P7 table using the array function.What I have below, which is about to 20th version is no better than version one.
=INDEX(11:11,MAX(ISNUMBER(11:11)*COLUMN(11:11)))

Open in new window

I'm really confused on the INDEX part here also.
0
Comment
Question by:Frank Freese
  • 4
  • 2
  • 2
8 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39712900
Can you post the Excel?
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39712921
In L11 use this formula copied down to L15

=LOOKUP(9.99E+307,K3:P3)

That will get you the last number in each row

regards, barry
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39712927
Better than mine! Thanks for the tip Barry :)
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:Frank Freese
ID: 39712929
barry,
how you came up with this....anyway, IT WORKED!
thanks
I'll accept your answer.
0
 

Author Closing Comment

by:Frank Freese
ID: 39712931
thanks to the both of you
good job barry
0
 

Author Comment

by:Frank Freese
ID: 39712951
Barry,
could you briefy explain to me what this formula is saying?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39712991
9.99E+307 is the biggest number you can input in a cell in Excel (equivalent to 9.99 with 305 zeroes) so when you lookup using that number as a lookup value it probably won't be found.

If LOOKUP can't find the lookup value then it matches with the last number in the lookup range (because that should be the largest in a sorted range), so it does what you want here, finds the last number.

For last text value

=LOOKUP(REPT("z",255),range)

or for last value, number or text

=LOOKUP(2,1/(range<>""),range)

regards, barry
0
 

Author Comment

by:Frank Freese
ID: 39713003
thanks barry
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dynamic Chart Range 13 38
Office 2016 without internet 6 39
Excel formula Sumif not working 4 28
Need to get a Word Count from a Word file 19 20
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question