You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Fill Missing Values Help

Advertisement

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

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.

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

Answers by Expert:

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.

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