1. Fill Missing Values

SALAM HI

Everyone

I HAVE DATA ON 65 VARIABLES FROM 1980 TO 2000 FOR 94 INDIVIDUALS

THE ISSUE IS THAT , I HAVE TO FILLING THE MISSING VALUES.

1) IF THE IN BETWEEN VALUES ARE MISSING THEN, HAVE TO USE THE FOLLOWING FORMULA.

 Year Indiviual Values Cell address 1984 Abida 69.96089 F6 1985 Abida Missing values Formula=\$F\$6+(\$F\$9-\$F\$6)*1/3 F7 1986 Abida Missing values Formula=\$F\$6+(\$F\$9-\$F\$6)*2/3 F8 1987 Abida 62.47225 F9

AS IN THE ABOVE CASE TWO VALUES ARE MISSING, SO ONE HAS TO USE 3 IN THE FORMULA. IF 3 VALUES ARE MISSING THEN 4 AND SO ON.

2) BUT IF THE VALUES ARE MISSING AT THE START OR END THEN FIRST OF ALL ONE HAS TO FINE THE GROWTH RATE OF THE LAST 5 KNOW VALUES AND AFTER FIND THE GROWTH , ONE WILLL FORECAST THE NEXT VALUES.

LIKE
 YEAR IND VALUES CELL REFERENCE 1991 Aqueela 39.94412 G34 1992 Aqueela 93.36344 G35 1993 Aqueela 53.42548 G36 1994 Aqueela 5.890043 G37 1995 Aqueela 41.64539 G38 1996 Aqueela Missing values =G38*(1+((\$G\$38/\$G\$34)^(1/(COUNT(\$G\$34:G\$38)- 1)) -1)) G39 1997 Aqueela Missing values =G39*(1+((\$G\$38/\$G\$34)^(1/(COUNT(\$G\$34:G\$38)- 1)) -1)) G40 1998 Aqueela Missing values =G40*(1+((\$G\$38/\$G\$34)^(1/(COUNT(\$G\$34:G\$38)- 1)) -1)) G41 1999 Aqueela Missing values =G41*(1+((\$G\$38/\$G\$34)^(1/(COUNT(\$G\$34:G\$38)- 1)) -1)) G42 2000 Aqueela Missing values =G42*(1+((\$G\$38/\$G\$34)^(1/(COUNT(\$G\$34:G\$38)- 1)) -1)) G43

NOTE : IF MORE THAN FIVE VALUES ARE MISSING FOR ANY INDIVIDUAL DO NOT FILL IN THOSE.

3)

IF THE STARTING FIVE VALUES ARE MISSING I HAVE TO DO THE SAME AS THE VALUES ARE MISSING AT THE LAST.
FIND THE GROWTH RATE OF LAST FIVE KNOW VALUES AND THEN FORECAST THE PREVIOUS VALUES.

LIKE

 Year Indiviuals VALUES CELL REFERENCE 1980 Abida Missing values =D3/(1+((\$D\$11/\$D\$7)^(1/(COUNT(\$D\$7:\$D\$11) - 1)) -1)) D2 1981 Abida Missing values =D4/(1+((\$D\$11/\$D\$7)^(1/(COUNT(\$D\$7:\$D\$11) - 1)) -1)) D3 1982 Abida Missing values =D5/(1+((\$D\$11/\$D\$7)^(1/(COUNT(\$D\$7:\$D\$11) - 1)) -1)) D4 1983 Abida Missing values =D6/(1+((\$D\$11/\$D\$7)^(1/(COUNT(\$D\$7:\$D\$11) - 1)) -1)) D5 1984 Abida Missing values =D7/(1+((\$D\$11/\$D\$7)^(1/(COUNT(\$D\$7:\$D\$11) - 1)) -1)) D6 1985 Abida 84.81609 D7 1986 Abida 81.81585 D8 1987 Abida 78.81561 D9 1988 Abida 75.81537 D10 1989 Abida 72.81513 D11

ANY HELP ON THIS
THANKS

2. I have only uploaded the sample work file because the website does not allow me to upload all the data.

LINK OF THE FILE with whole data
http://sdrv.ms/Vi8cMP

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•