Format Formula to not change in Table

JGSmith

New Member
Joined
May 5, 2014
Messages
2
Hello

I am having a problem with what appears to be a type of Absolute vs. Relative references - but I am not too sure.

Using Excel 2016 I have created two tables.

Table 2 is a Summary of Table 1.

Table 1:

ABCD
1Fiscal Period200620072008
2Book Value1.6682.3803.596
3Book Value Growth42.69%51.09%
4Equity.324.561.969
5Equity Growth73.15%72.73%

<tbody>
</tbody>

Table 2:
EFGH
110 Years7 Years5 Years
2Book Rate
3Equity Rate

<tbody>
</tbody>

In G2, for example, I wrote

Code:
=rate(2,,-B2,D2)

That comes up with the proper calculation.
The problem is that when I do this, the rest of the cells in table 2 are automatically populated.

For example, in G3 it is automatically populated with:

Code:
=rate(2,,-B3,D3)

The problem is that I want it to reference B4 and D4. When I attempt to change that cell to reference the correct cells, then it automatically changes how G2 is referenced.

Obviously standard $ signs will not work here to keep it from populating how it wishes.

Can somebody please let me know how to format this formula so that it does not auto populate elsewhere in the table?

Thank you very much for your time and help!

Jason
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
Maybe...
Not elegant but works
Insert the formula in G2
=rate(2,,-B3,D3)
Insert the formula in G3
=rate(2,,-B4,D4)
click Undo

M.
 

JGSmith

New Member
Joined
May 5, 2014
Messages
2
Thank you Marcelo
That does appear to be a workaround. I wonder if someone will also have an idea of how to set the default settings?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,258
Messages
5,527,663
Members
409,781
Latest member
Maxcwy2020

This Week's Hot Topics

Top