EXCEL multiply n cells by 1 coefficient. Solved

Question

Hello,
so here it is, I have a table with prices and I need to multiply all of them by a coefficient (which is supposed to change)
I would like to know if there is a way to multiply all these numbers at once by this coefficient without having to calculate (and enter) each one individually, knowing that this number is supposed to change later and the calculations should be done automatically.
thanks in advance

Configuration: Windows XP Internet Explorer 7.0

m@rina · Accepted Answer

Hello,

If you don't want to use formulas, here's a solution:

- Type your coefficient in a cell, for example 1.10 for 10%, and copy it
- Select your entire price table
- Edit menu, Paste Special => Multiply

m@rina

Smart91 · Answer

Hello,

I'm not sure I understand. But let's assume the price list is A1==>A3
Your coefficient is in B1
and your new prices are in C1==>C3
C1=A1*$B$1
C2=A2*$B$1
C3=A3*$B$1

Smart
--
"If you have no ambitions, you settle on the edge of the abyss" (Kundera)

errornumber666 · Answer

In fact, since the coefficient is supposed to change in the future and I'm not the one who will use the table, I would like all prices to change automatically by changing the coefficient.
Moreover, the coefficient should not appear in the printout, and I do not want to go through formulas because the table is too large.

m@rina · Answer

Hello,  So you need a macro.  Very simple example to refine:  Sub multiply() percentage = InputBox("How much?") For Each c In Range("A2:D10") c.Value = c.Value * percentage Next c End Sub

m@rina · Answer

Have you tested it?...  Change the text in the dialog box, the range that needs to be multiplied... Enforce a data type, possibly include a dropdown list of choices, etc.  m@rina

Raymond PENTIER · Answer

Another method, without going through a macro:

1) The gross amounts are in column B:B and the results with the coefficient applied should be displayed in column C:C
2) In cell K3, enter the coefficient, then assign a name to this cell, Coef for example
3) In column C:C enter the formula =B:B*Coef (in all the relevant cells)
4) The day the coefficient changes value, you just need to modify the content of cell K3.

Does that work for you? (It's actually very similar to what Smart91 suggested to you in post 2 ...).

--
Retirement is great! Especially in the Antilles... :-)
Raymond

eriiic · Answer

Hello,

Mathematically, I agree with Raymond's answer, but be careful that the evolution of the coefficient will quickly become incomprehensible and a source of error.
Let’s imagine that prices increase by 10% each time, the coefficients to enter will successively be:
1.1
1.21
1.331
1.4641
1.61051
1.771561
1.9487171

Not to mention the loss of precision if we exceed 15 significant digits.
We’ll see if users can keep up...
Eric

errornumber666 · Answer

Hello, so the macro works, the only problem is: when I change the coefficient, the modified prices change again I want only the base prices to change... how do I do that?

m@rina · Answer

Hello again,

Indeed, I didn't understand that the new percentage was still applied to the original values.
In that case, you absolutely must keep the original values somewhere.

You could create a tab that contains the original values. This tab can be hidden without any problem.
In the macro, you could add this as the first line (noting that I named "origine" the range of original amounts and "destination" the range that should be increased):

Range("origine").Copy Destination:=Range("destination")

m@rina

fleipzig · Answer

Hello everyone,

I have a problem in Excel that I can't seem to solve :-(

I want to multiply the values in a table by a variable coefficient without using a macro.

Specifically, I have a table F4:K14 that I want to multiply by a variable coefficient located in B2.
By changing the value of this cell, I want the values in the table to also change without having to display the final results in another table.

Maybe someone can provide me with a solution?

Thanks in advance,

Florian

fleipzig · Answer

Great job! Very clever!  Thanks for the help!  See you soon!

Jim · Answer

Hello,

your formula system works perfectly, but on the cell in question, I already have a calculation (a multiplication)

The initial formula:
=SUM(V22*T22)

The formula to calculate the coefficient:
=(V:V*Coef)

I tried:
=(V:V*Coef)+(V24*T24) it adds an extra amount, e.g. 100 x 2 = 200 plus the coefficient, so for a coefficient of 1, it gives me 300 instead of 200

=SUM(V22*T22);(V:V*Coef)
it's an error; something is needed between the two.

How do I add the two?

Thank you

Jim · Answer

Oh sorry, I found its final point:  =SUM(V24*T24*Coef)  just like that, Thanks

EXCEL multiply n cells by 1 coefficient.

13 réponses

How to get the character µ on the android keyboard.

Texture issues in enshrouded

Access issue to my "contact me" profile

Windows spotlight is not working.

Black screen digital camera

Connecting rj45 cable with fiber

Sony bravia tv red led blinks 4 times

Free: associating with the freebox server in progress

Inuyasha in french

Ccleaner starts at boot.