Excel Extending Equations

Question

So I have an excel table that is multiply 1 value against several other values. It looks like this:

So I want the equations inside cells C14 to F14 to be B14*C5, B14*C6, B14*C7, B14*C8 respectively. So I can obviously do that manually but I want to learn the faster way. So I know I should use absolute reference for B14, so I can input =$B$14*C5 for cell C14.

But then when I do the CTRL extend method where you put the cursor on the bottom right corner of the cell and hold CTRL while you extend the cells. The problem is since I am extending the equation in B14 horizontally to F14, it is incrementing the equation horizontally. So the equation in D14 becomes =$B$14*D5 instead of =$B$14*C6.

So how exactly do I increment the equation downwards while I extend the equation horizontally?

Answer 1

There is an easy way to do this. Enter

= $D$5 * $B13 | = $D$6 * $B13 | = $D$7 * $B13 | = $D$8 * $B13

In the top 4 cells. Select the 4 cells and copy into the rows you want, in this case 14 and 15.

Answer 2

Make life easy by putting the exchange rates in a row instead of a column to match the section below that and then it will no longer be an issue.

exchange rates
British Pound | E.U Euro | Swiss Franc | Norwegian Krona
0.067         | 0.897    | 1.34        | 7.34

Answer 3

Auto-fill can't do what you want. Use VLOOKUP with the correct mixture of absolute and non-absolute cell references and then auto-fill down and/or right will work nicely.

So in the slightly modified example below, cell C13 would contain:

=VLOOKUP(C$12,$B$5:$D$8,3,0)*$B13

How does this work?

Each cell in the yellow area references the country name in the header, then does a VLOOKUP on the country in the exchange reate table, taking the value in the third column, then the usual arithmetic.

Auto-fill works because the country header is row-fixed, the USD currency amount is column-fixed and the exchange rate table is row- and column-fixed in the formula.