You are here:

Excel/Fill Missing Values Help

Advertisement


Question
SALAM HI Everyone
Need your help in Excel
i have data on 65 variables from 1990 to 2010 for 94 individuals. Some of the values are missing and i have to filling the missing values.
https://skydrive.live.com/redir?resid=451697C085B3FAE0!121&authkey=!ABKn2VZPjYNRiMg
if the values are missing at the end then first of all one has to fine the growth rate of the last 5 know values and after find the growth, one will forecast the next values.
like for one individual named b the var4 has five missing values, so fill the missing values from 2005 to 2010. si have to calculate the growth rate of the last non-missing values 2001-2005 and the known value*(1+g) and so on till last value.
Individual   B
Year/Variable   Var4
2001      85.5365994672412
2002      1.06356964680864
2003      74.5753429352989
2004      87.3370130378642
2005      29.0410577502473
2006      =I18*(1+(($I$18/$I$14)^(1/(COUNT($I$14:I$18)- 1)) -1))
2007      =I19*(1+(($I$18/$I$14)^(1/(COUNT($I$14:I$18)- 1)) -1))
2008      =I20*(1+(($I$18/$I$14)^(1/(COUNT($I$14:I$18)- 1)) -1))
2009      =I21*(1+(($I$18/$I$14)^(1/(COUNT($I$14:I$18)- 1)) -1))
2010      =I22*(1+(($I$18/$I$14)^(1/(COUNT($I$14:I$18)- 1)) -1))
note: if more than five values are missing for any individual do not fill in those. Like for individual b var3 has more than 5 values missing, so i donít have to fill them up. 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
Indiviuals   A   A
Year/Variables   Var1   Var2
1990.00      =C4/(1+(($C$12/$C$8)^(1/(COUNT($C$8:$C$12) - 1)) -1))
1991.00      =C5/(1+(($C$12/$C$8)^(1/(COUNT($C$8:$C$12) - 1)) -1))
1992.00      =C6/(1+(($C$12/$C$8)^(1/(COUNT($C$8:$C$12) - 1)) -1))
1993.00      =C7/(1+(($C$12/$C$8)^(1/(COUNT($C$8:$C$12) - 1)) -1))
1994.00      =C8/(1+(($C$12/$C$8)^(1/(COUNT($C$8:$C$12) - 1)) -1))
1995.00      92.53
1996.00   11.83   78.32
1997.00   49.74   66.47
1998.00   26.25   59.45
1999.00   16.40   34.43
¨as var2 has five values missing so I have to fill them. But var1 has more than five values missing so I donít fill them up.
Please help on this that how i can do this easily, i have 65 variable and more than 84 individuals so it's very difficult to by hand

Thanks

Answer
Hello Lubna,

Thanks for the detailed question, but I'm having a bit of confusion when I try to match this question with the excel file you have shared. For example, I'm unable to find individuals AA, B etc in the file. The numbers you have indicated in the question above are very different from the file which you have shared.

Can you double check and update me - either I'm reading the question wrong, or you need to send me a correct file.

Can you mail me the file to - gulshanrajpurswani@gmail.com with subject containing AllExperts.

Thanks,
Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.