The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

using the “$” to lock a fomular

using the “$” to lock a fomular

0
Votes
1
Answer

how do I know where to use the “$” and lock a fomular in excel? in the link attached see the sign used in different location here:

 

INDEX(‘1.1 FY2016’!C$5:C$61,MATCH(‘2.1 Database’!$B61,’1.1 FY2016′!$H$5:$H$61,0))

First time, its used to separate C and 5

second time it used before B and not in between B and 61

third time its used before and in between H and 5.

 

Help Please

1 Answer

365 Team
0
Votes

Hi Abieyuwa, 
when we have C$5:C$61 we are fixing the rows. That means when we copy the formula we’re insuring we’ll remain between the rows 5 and 61, which is where our values are.
On the other hand, when we have $B61 we’re fixing the column. That means when we copy this formula we’re sure we’ll remain in the same column. Here we copy the values for code, which are all in column B.
To recap:

  1. If we fix before the letter, i.e. $A1, $B1, $C1 and so on, we’re fixing the column.

       2. If we fix before the numbers A$1, A$2… we’re fixing the row.
       3. If we fix both $A$1, we’re fixing both row and column. This ensures the value won’t change or shift position, when we copy it.
 
Best, 
Eli