Calculate a total in the same cell as one of the values

[MonsieurExcel]

Hi there,

I'm trying to calculate an updated cost based on an updated exchange rate (example below)

I want to be able to multiply Cell A6 * Cell A2 but keep the solution in Cell A6. The same applies for Cell B6 * Cell B2 etc

I have thousands of Costs that need to be applied with the rate and want to keep them in their cells because the rates will change.

Usually, for example, in Cell A6 I could just use =12.214*A2 if it was a small range of values but because there are thousands of costs that's not practical.

Thank you for reading and apologies if I'm missing something really straightforward!!

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]rate[/TD]
[TD]rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.89[/TD]
[TD]0.90[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cost[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]12.214[/TD]
[TD]13.255[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]13.009[/TD]
[TD]13.294[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]12.899[/TD]
[TD]13.111[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]12.687[/TD]
[TD]13.244[/TD]
[/TR]
</tbody>[/TABLE]

 

[Roxxien]

There is 2 solutions at your problem.

1. without VBA : use your other column for making your calcul : D6=A$2*A6 and copy/paste your value back in column A

2. with VBA : a macro like the following that would need to be put in a loop

Range("A6") = Range("A6") * Range("A2")

If you want the solution #2 , I would need to know kind of range you have.

 

[MonsieurExcel]

Thank you - sorry, I should have mentioned that I still want to see the original value in there, if that's at all possible?

So, something like =12.214*A$2
=13.009*A$2
=12.899*A$2
=12.687*A$2

I'm wondering if it's possible to copy that sort of "formula" down 1000 rows and across 30 columns?

I do appreciate your reply - also, I'm terrible at VBA although I'd love to be at least competent in it!

Thanks again

 

[Dave Patton]

1. If you want to multiply a number or range by a factor, put the factor into a cell such as B2. Copy the cell B2. Select a cell or Range then Paste Paste Special Multiply.

2. If one needs to do this in the future, the costs could be linked to the exchange factor (a cell or a named factor). When the exchange rate changes, just edit one cell or named factor.

 

[Roxxien]

The only way that you can do that is in VBA because Excel don't have that kind of self-reference available.

So here is a Macro to do it

Sub example()
Dim r As Range
Application.ScreenUpdating = False
For Each r In Range("A6:P11")
    r.FormulaR1C1 = "=" & r.Value & "*R2C[0]"
Next
Application.ScreenUpdating = True
End Sub

To open the VBA windows, use ALT+F11. Copy that code in a new module. Press F5 to use it.

I HIGHLY suggest you to use a copy of your original file to use VBA.

 

[Roxxien]

I forgot to say that you will need to adjust the range for the loop from A6:P11 to exactly what you need.

 

[Dave Patton]

Try the suggestions in post #4