Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i enter a lot of data in xl. cani create a form | Excel Discussion (Misc queries) | |||
Can you create a formula to add 1 to the # you enter | Excel Worksheet Functions | |||
How do I create a rolling average chart, adding most recent data? | Charts and Charting in Excel | |||
I want to create a macro to where after we enter data in a spread. | Excel Worksheet Functions | |||
How To: create a form for a user to enter data | Excel Programming |