2nd to last value in column

[Greg]

Hi everyone.

My question has to do with the 2nd to last cell with data in a column. To
get the last data, I am using "=LOOKUP(10^100,A22)" but how can I get the
data from the cell right above that?

For instance, my data looks like this (multiple tabs for different entities):

# of Accounts 1/1/10 45
$ of Accounts 1/1/10 6300
# of Accounts 1/2/10 23
$ of Accounts 1/2/10 1550

So, I want my totals page to show the last 2 entries (# and $).

TIA, any help would be wonderful,

Thanks,

Greg

 

[Jacob Skaria]

Try the below to get the data from the cell right above that?

With data in ColA

=INDEX(A:A,MATCH(10^10,A:A)-1)

 

[Greg]

Worked like a charm.

Thank you very much,

Greg

Try the below to get the data from the cell right above that?

With data in ColA

=INDEX(A:A,MATCH(10^10,A:A)-1)

 

[Mike H]

Greg,

You question is a bit muddled, you ask for the second to last value in a
column yet your formula is a row (A22), which is it. Assumin no blanks try
these

Row
=INDEX(A22,COUNT(A22)-1)
column
=INDEX(A1:A6,COUNT(A1:A6)-1)
--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''''s razor (Abbrev)

 

[Greg]

You are correct. I used a copied formula template (A22) for my example.
The actual number I was looking for was in a column.

My fault.

Thank you for your input. Having both formulas is great.

Greg

 

[L. Howard Kittle]

Hi Greg,

Here's another that seems to do what you want.

=OFFSET(F1,COUNTA(F:F)-2,0)

HTH
Regards,
Howard

 

[David Donald]

Hi guys,
Along a similar line to finding the 2nd last value in a column, I have a question that's a little trickier..

I have a single column of numbers ~100 rows high.. Not all cells have values in them and each week another value is entered at the bottom of the column.. I need to find the 10 lowest values in the last 20 cells that have values in them.. So each week the top value of the 20 'found' values will drop off and the most recent value will become part of the list of 20 values that the 10 lowest values are calculated from..

Any ideas??
Thanks in advance,
Doggy



L. Howard Kittle wrote:

Hi Greg,Here is another that seems to do what you want.
18-Jan-10

Hi Greg

Here is another that seems to do what you want

=OFFSET(F1,COUNTA(F:F)-2,0

HT
Regards
Howard

Previous Posts In This Thread:

2nd to last value in column
Hi everyone

My question has to do with the 2nd to last cell with data in a column. T
get the last data, I am using "=LOOKUP(10^100,A22)" but how can I get th
data from the cell right above that

For instance, my data looks like this (multiple tabs for different entities)

$ of Accounts 1/1/10 630
$ of Accounts 1/2/10 155

So, I want my totals page to show the last 2 entries (# and $)

TIA, any help would be wonderful

Thanks

Greg

Try the below to get the data from the cell right above that?
Try the below to get the data from the cell right above that

With data in Col

=INDEX(A:A,MATCH(10^10,A:A)-1

-
Jaco

:

Worked like a charm.Thank you very much,Greg"Jacob Skaria" wrote:
Worked like a charm

Thank you very much

Gre

:

Greg,You question is a bit muddled, you ask for the second to last value in
Greg

You question is a bit muddled, you ask for the second to last value in
column yet your formula is a row (A22), which is it. Assumin no blanks tr
thes

Ro
=INDEX(A22,COUNT(A22)-1
colum
=INDEX(A1:A6,COUNT(A1:A6)-1
-
Mik

When competing hypotheses are equal, adopt the hypothesis that introduce
the fewest assumptions while still sufficiently answering the question
Occam''''s razor (Abbrev

:

You are correct. I used a copied formula template (A22) for my example.
You are correct. I used a copied formula template (A22) for my example
The actual number I was looking for was in a column

My fault

Thank you for your input. Having both formulas is great

Gre

:

Hi Greg,Here is another that seems to do what you want.
Hi Greg

Here is another that seems to do what you want

=OFFSET(F1,COUNTA(F:F)-2,0

HT
Regards
Howard


Submitted via EggHeadCafe - Software Developer Portal of Choice
Sending SMTP email from within BizTalk Orchestration
http://www.eggheadcafe.com/tutorial...f-1716445b26bc/sending-smtp-email-from-w.aspx

 

[T. Valko]

What do you want to do with these 10 lowest values? Sounds like a golf
handicap calculation.

What if there aren't 20 numbers?