Answer to the 5 little Tips (tests) – Part 3/5

The task: Doing simple calculation (add, subtract, multiply, divide) without formula

Answer:  Copy and Paste Special…

First of all, we need to understand what we want to perform: Convert currency (in HKD) into USD, by multiply the Exchange Rate (7.7) to the values in A5:A12 in the GIF above). And remember, no formula!

Step by step instruction shown in the GIF above:

1. Copy the value (A2 in our example)
2. Select the destination range (A5:A12) where you want the value (A2) to multiply to
3. Right-click, Paste Special
4. Select “Values” in the upper portion under Paste; “Multiply” in the middle portion under Operation. 
5. OK

As simple as this! 🙂

Try other operations (Add, Subtract, Divide) to see the results.

Limitation – The result is static.

It won’t change with the “Exchange Rate”.  Therefore it is only good for one-off task.

Wait… Can we do it with dynamic result?

Of course 🙂  Watch this:

Here’s the step by step instruction:

1. In any empty cell, make a direct reference to the cell where the value resides by inputting =$A$2 (Tip: it has to be Absolute reference)
2. Copy the cell with the formula (B2 in our example)
3. Select the destination range (A5:A12) where you want the formula to multiply to
4. Right-click, Paste Special
5. Select “Formulas” in the upper portion under Paste; “Multiply” in the middle portion under Operation. 
6. OK

Now the helper cell (B2) can be clear.  Check the formula in the destination.  They become a formula:  =Original value * ($A$2)

Watch it in action in my YouTube channel:

Let’s try other operations yourself.  Also try and see what if you do not use absolute reference in the helper cell?