none
Fill Missing Values

    Question

  • SALAM 
    HI Everone i have data on the difference variables from 1984 to 2009 for 84 countries. 
    some of the values are missing for some variables. 
     I want to fill in these last missing values for each country.
    like for country Albania fill in the last missing values from 2005 to 2009. the point is that only fillin the last missing values not the starting missing values. like if for any country values are missing from 1984 to 1989 donot fill these values.
    Now what is the formula to fill in these missing values.
    First of all i have to calulate the growth of last five values(non-missing). than using this growth rate , i have to fill in  the next missing values.
    Note 1: plz make the code flexible i-e before runing the code it asks for how many values you want to calculate the growth rate. and than ask how many values you want to fill in. like if the answer is 5. only fillin the maximum last 5 mssing values. if any where 6 values are missing , leave that. 
    Note 2: I have 82 variables for which i want to do this. 
    after doing this make the fill in values cell formatting bule.

    link of the file

    https://skydrive.live.com/redir?resid=DD0BC148DF66DAC4!162&authkey=!AOAZx5ZHwRy0868

    Tuesday, January 22, 2013 5:05 AM

All replies

  • I have assumed that your data is sorted by country first, then by date, with country in column A. For any column with missing data, select those columns, use Edit Go to..   special   and choose blanks - note the row of the activecell, which we will note as ?. Then type the formula

    =IF($A?<>$A(?-1),"",

    then press the up arrow once, type the closing paren, press Ctrl-Enter, and then re-select all the columns, copy and paste special values. Then you are done.

    Note that if the row of the activecell is, say, 4 then ?-1 is 3, and the start of the formula should be

    =IF($A4<>$A3,"",



    Tuesday, January 22, 2013 6:05 PM