Excel/Cell Reference

Advertisement


Question
QUESTION: =(1+((INDIRECT(ADDRESS(ROW()-1,COLUMN()))/INDIRECT(ADDRESS(ROW()-5,COLUMN())))^(1/COUNT(INDIRECT(ADDRESS(ROW()-1,COLUMN())):INDIRECT(ADDRESS(ROW()-5,COLUMN()))))-1))

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
=D22*(1+(($D$22/$D$18)^(1/COUNT($D$18:$D$22))-1))
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.  
=D22*(1+((INDIRECT(ADDRESS(ROW()-1,COLUMN()))/INDIRECT(ADDRESS(ROW()-5,COLUMN())))^(1/COUNT(INDIRECT(ADDRESS(ROW()-1,COLUMN())):INDIRECT(ADDRESS(ROW()-5,COLUMN()))))-1))
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.

but ........

please suggest what to do.

link of the file
https://www.dropbox.com/s/oknuw6yltjdjwoq/Cell%20reference%20.xlsx
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 a

Answer
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.
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


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.