Share via

Adding Multiplication to Range of Cells

Anonymous
2025-01-20T08:18:34+00:00

Hello,

I have forecast turnover from January to December in cells A1 to A12 and I want to multiply the range by 100% in cell B1, how can I add multiplication formulae number in A1*B1, number in A2*B1 ......... number in A12*B1 in A1, A2 ........ A12 so that I can use Goal Seek function to arrive at desired total sales by changing B1.

It is not practical for me to do the calculation in cell C1 as I have too many sheets to repeat the formula.

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2025-01-20T23:32:59+00:00

    Hi,

    In cell B1, type 100%. In cell C1, type = $B1$1. Copy cell C1, select the range A1:A12, right click > Paste Special > Multiply > OK. Delete cell C1. Now run Goal seek.

    Hope this helps.

    1 person found this answer helpful.
    0 comments

10 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-01-20T13:08:18+00:00

    Hi, TarigE

    I'm sorry I gave the wrong solution and I apologize for my mistake.

    Regarding your problem, it can only be solved using VBA without creating a secondary column:

    Press Alt + F11 to open the VBA editor.

    In the VBA editor, click Insert and then select Module.

    Paste the following code in the Module window: 

    Sub MultiplyRange() 

    Dim cell As Range 

    Dim multiplier As Double 

    multiplier = Range("B1").Value 

    For Each cell In Range("A1:A12") 

        cell.Value = cell.Value * multiplier 

    Next cell 

End Sub

    Close the VBA editor.

    Go back to Excel and press Alt + F8 to open the Macro dialog box.

    Select the MultiplyRange macro and click Run.

    This macro multiplies the value of each cell in the range A1:A12 by the value in B1

    I hope the above information can help you. Feel free to send a message if you need further help.

    Best wishes

    Aiden.C - MSFT |Microsoft Community Support Specialist

    0 comments
  2. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2025-01-20T12:33:34+00:00

    If you enter that formula in A1:A12 you will create circular references, since the formulas refer to the cells they are in.

    0 comments
  3. Anonymous
    2025-01-20T12:01:14+00:00

    Thanks

    Excel added { } to the formula and gave me 0 as a result

    0 comments
  4. Anonymous
    2025-01-20T11:19:45+00:00

    Hi, TarigE

    Thank you for using Microsoft products and posting them to the community.

    To achieve this, you can use an array formula in Excel. Here's how you can do it:

    Select the range A1:A12 where you have your forecast turnover.

    Enter the following formula in the formula bar: 

    =A1:A12 * B1

    Press Ctrl+Shift+Enter to enter it as an array formula. This will multiply each cell in the range A1:A12 by the value in B1.

    Now, you can use the Goal Seek function to adjust B1 to reach your desired total sales. Here's how:

    Go to the Data tab on the ribbon.

    Click on What-If Analysis and select Goal Seek.

    Set the Goal Seek parameters:

    Set cell: Select the cell where you want the total sales to appear (e.g., a cell where you have the sum of A1:A12).

    To value: Enter your desired total sales.

    By changing cell: Select cell B1.

    Click OK and Excel will adjust B1 to achieve your desired total sales.

    This way, you don't need to create additional columns for the calculations, and you can easily apply this to multiple sheets.

    I hope the above information can help you. Feel free to send a message if you need further help.

    Best wishes

    Aiden.C - MSFT |Microsoft Community Support Specialist

    0 comments