Need Help with repeating formula

[Kevin Neufeld]

I am performing a new bid to our current supplier as well as 3 new ones. I have different lanes and am wanting to know the % increase we are possibly going to take. In column D I have the formula to calculate the % increase but need something else extra to have the formula automatically change when the Lane ID# changes.

thanks in advance

Col A Col B Col C Col D
LaneID Supplier Rate %Increase
1 Current 1000 =(($c2-$c$2)/$c$2)
1 A 1050 =(($c3-$c$2)/$c$2)
1 B 1100 =(($c4-$c$2)/$c$2)
1 C 1150 =(($c5-$c$2)/$c$2)
2 Current 500 =(($c6-$c$6)/$c$6)
2 A 550 =(($c7-$c$6)/$c$6)
2 B 600 =(($c8-$c$6)/$c$6)
2 C 650 =(($c9-$c$6)/$c$6)

 

[oldbrewer]

if D1 has a formula that includes A1 then when A1 changes, D1 will change.

 

[CharlesChuckieCharles]

add an extra column ( E)

in col D =(($C2-$E2)/$E2) in Col E =IF(A2<>A1,C2,E1)

so in col e when the Lane ID changes copy the Rate from the Rate column C otherwise copy the Rate from above in E

In Col D use col E as the "fixed" value