0

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

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?

3 Answers 3

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.

1

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
2
  • This would provide a much more coherent layout when comparing the two tables, e.g. Euro rate would be directly above Euro values.
    – Mike Honey
    Jun 2, 2014 at 3:44
  • 1
    I would also convert the ranges into Tables (Insert / Table) as these will automatically copy formulas for you and will use more robust references.
    – Mike Honey
    Jun 2, 2014 at 3:45
0

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.

Excel screendump

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.