How do I create a formula that will enter the most recent data?
 

I need to create a formula that will enter only the most recent data.
Example: A1=Jan05 B1=Jan06 C1=Jan07. If there is no data for Jan 07 and Jan
06 I need it to be able to pull the data from Jan 05.

How do I create a formula that will enter the most recent data?
 

Here is one way

=OFFSET(A1,0,COUNTA(A1:F1)-1)

the range can be as many columns as you need
This assumes that you will not have any missing data in your range
it will count how many entries there are and bring in the last one.

another version that allows you to have missing data uses the lookup function

=LOOKUP(MAX(A1:F1)+1,A1:F1)

it adds one to the max of your values and looks for a value that doesn't
exist, that forces it to bring in the last value in the range

hope this helps

David
"Kim" wrote:

I need to create a formula that will enter only the most recent data.
Example: A1=Jan05 B1=Jan06 C1=Jan07. If there is no data for Jan 07 and Jan
06 I need it to be able to pull the data from Jan 05.

How do I create a formula that will enter the most recent data
 

Thanks for your help.
The lookup function worked without the +1. However, sometimes it is pulling
a 0. Not sure why!? Still need help!
Example:
2005 2006 2007 Most recent 12 months
Jan 10 15 0 (should pull
15)
Feb 11 16 0 (should pull 16)
Mar 9 0 0 (should pull
9)




"dkinn" wrote:


Here is one way

=OFFSET(A1,0,COUNTA(A1:F1)-1)

the range can be as many columns as you need
This assumes that you will not have any missing data in your range
it will count how many entries there are and bring in the last one.

another version that allows you to have missing data uses the lookup function

=LOOKUP(MAX(A1:F1)+1,A1:F1)

it adds one to the max of your values and looks for a value that doesn't
exist, that forces it to bring in the last value in the range

hope this helps

David
"Kim" wrote:

I need to create a formula that will enter only the most recent data.
Example: A1=Jan05 B1=Jan06 C1=Jan07. If there is no data for Jan 07 and Jan
06 I need it to be able to pull the data from Jan 05.

How do I create a formula that will enter the most recent data
 

Either of the first formula work if you don't have the 0 in your last columns
they are looking for the last non blank cell and the 0's look like values to
the formula

here is a one that looks for the 0 and then backs up one column (offset is
base 0 so you actually have to back up 2 to get the correct result)

=OFFSET(A1,0,MATCH(0,A1:F1,0)-2)

offset your first value by a match of the first 0 in the range and back up
one column(again 2 because of base o)

hope this helps

David

"Kim" wrote:

Thanks for your help.
The lookup function worked without the +1. However, sometimes it is pulling
a 0. Not sure why!? Still need help!
Example:
2005 2006 2007 Most recent 12 months
Jan 10 15 0 (should pull
15)
Feb 11 16 0 (should pull 16)
Mar 9 0 0 (should pull
9)




"dkinn" wrote:


Here is one way

=OFFSET(A1,0,COUNTA(A1:F1)-1)

the range can be as many columns as you need
This assumes that you will not have any missing data in your range
it will count how many entries there are and bring in the last one.

another version that allows you to have missing data uses the lookup function

=LOOKUP(MAX(A1:F1)+1,A1:F1)

it adds one to the max of your values and looks for a value that doesn't
exist, that forces it to bring in the last value in the range

hope this helps

David
"Kim" wrote:

I need to create a formula that will enter only the most recent data.
Example: A1=Jan05 B1=Jan06 C1=Jan07. If there is no data for Jan 07 and Jan
06 I need it to be able to pull the data from Jan 05.