how i can change the relative cell addressing to absolute cell addressing in the above formula
ANSWER: The use of indirect with Row and Column gives you complete relativity in the formula - but replacing either the row part or the column part with a fixed number would make this partialy absolute. If you want to go fully absolute, I would simply remove the indirect entirely and type the absolute reference.
(question found in the question pool)
---------- FOLLOW-UP ----------
QUESTION: Sir actually the problem is that i have a data set for 84 individual on 65 variables . some of the values are missing in the data set and i have to fill them by the formula below. if i will use the absolute cell addressing then i will have to a lot lot of editing . I have the following formula for the
=Previous know value *(1+((End Value/Start Value)^(1/(Periods - 1)) -1))
the part of formula after the 1+(End value... is the growth rate , which is supposed to fixed to the next values.
if I use the absolute cell addressing then i have
but if i apply this formula then i will have to do a lot of editing.
so to avoid the lot of editing i have use the following formula. but the issue with the following formula is that the growth rate in this formula is not fix, its varies when i copy this formula down.
What i thought was that I will sort the data by years and then by selecting the blanks cells for the last five years , i will past this formula and then again i will sort the data with the individuals and then again by GO TO SPECIAL , i can replace this indirect formula with its absolute version part.
please suggest what to do.
link of the file
I have two sheets sheet1 , contains the data for two individuals and where as Sheet2 contains the data for all the individuals.
In column D of sheet on i have applied the Indirect formula where as in Column E i have applied the =D22*(1+(($D$22/$D$18)^(1/COUNT($D$18:$D$22))-1)) formula.
Please help how i can fill the missing value easily.
I'm not 100% sure which the correct result is supposed to be, so with any method I think I would check one set carefully to ensure the result is the expected result. I can see that this is a tricky process - I THINK I would therefore do it with a series of "Helper" columns which will enable you to build up a full set of records. Once done, the resultant column can be pasted using paste special over the existing data, and then the helper columns can be deleted. see http://aidanheritage.byethost3.com/excel/Cell%20reference%20.xls
for a method of doing what you want which I hope helps.