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,A2:D2)" 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

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)

--
Jacob


"Greg" wrote:

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,A2:D2)" 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

Worked like a charm.

Thank you very much,

Greg

"Jacob Skaria" wrote:

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)

--
Jacob


"Greg" wrote:

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,A2:D2)" 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

Greg,

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

Row
=INDEX(A2:D2,COUNT(A2:D2)-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" wrote:

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,A2:D2)" 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

You are correct. I used a copied formula template (A2:D2) 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

"Mike H" wrote:

Greg,

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

Row
=INDEX(A2:D2,COUNT(A2:D2)-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" wrote:

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,A2:D2)" 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

Hi Greg,

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

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

HTH
Regards,
Howard

"Greg" wrote in message
...
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,A2:D2)" 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

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)

HTH
Regards,
Howard

Previous Posts In This Thread:

On Monday, January 18, 2010 8:41 AM
Greg wrote:

2nd to last value in column
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,A2:D2)" 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 6300
$ 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

On Monday, January 18, 2010 8:52 AM
Jacob Skaria wrote:

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 ColA

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

--
Jacob


"Greg" wrote:

On Monday, January 18, 2010 8:59 AM
Greg wrote:

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

Thank you very much,

Greg

"Jacob Skaria" wrote:

On Monday, January 18, 2010 8:59 AM
Mike H wrote:

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 a
column yet your formula is a row (A2:D2), which is it. Assumin no blanks try
these

Row
=INDEX(A2:D2,COUNT(A2:D2)-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" wrote:

On Monday, January 18, 2010 9:19 AM
Greg wrote:

You are correct. I used a copied formula template (A2:D2) for my example.
You are correct. I used a copied formula template (A2:D2) 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

"Mike H" wrote:

On Monday, January 18, 2010 2:31 PM
L. Howard Kittle wrote:

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)

HTH
Regards,
Howard


Submitted via EggHeadCafe - Software Developer Portal of Choice
Sending SMTP email from within BizTalk Orchestration
http://www.eggheadcafe.com/tutorials...il-from-w.aspx

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

What if there aren't 20 numbers?

--
Biff
Microsoft Excel MVP


<David Donald wrote in message ...
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)

HTH
Regards,
Howard

Previous Posts In This Thread:

On Monday, January 18, 2010 8:41 AM
Greg wrote:

2nd to last value in column
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,A2:D2)" 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 6300
$ 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

On Monday, January 18, 2010 8:52 AM
Jacob Skaria wrote:

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 ColA

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

--
Jacob


"Greg" wrote:

On Monday, January 18, 2010 8:59 AM
Greg wrote:

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

Thank you very much,

Greg

"Jacob Skaria" wrote:

On Monday, January 18, 2010 8:59 AM
Mike H wrote:

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 a
column yet your formula is a row (A2:D2), which is it. Assumin no blanks
try
these

Row
=INDEX(A2:D2,COUNT(A2:D2)-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" wrote:

On Monday, January 18, 2010 9:19 AM
Greg wrote:

You are correct. I used a copied formula template (A2:D2) for my example.
You are correct. I used a copied formula template (A2:D2) 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

"Mike H" wrote:

On Monday, January 18, 2010 2:31 PM
L. Howard Kittle wrote:

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)

HTH
Regards,
Howard


Submitted via EggHeadCafe - Software Developer Portal of Choice
Sending SMTP email from within BizTalk Orchestration
http://www.eggheadcafe.com/tutorials...il-from-w.aspx