Math operation between a column with unique formulas and another column

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
21
Office Version
2019
Platform
Windows
Hello guys!

So I've been having this situation quite often, and I have searched and thought of something to solve my problem but haven't been able to find anything.

I have a two columns. Column F and G.
In Column F I have several rows of information, each row is unique, some are values and some are formulas.
Because of the nature of the work I do, sometimes I need to "affect" each of the cells in Column F with a factor (multiplying, or dividing, or sometimes even adding or substracting) in Column G.

For example, as you can see in the attached image:
1) Cell F10 has a hard-entered number 0.60, in this case I would like the cell to be =(0.60)*G10
2) Cell F11 has the formula =E11*$J$13 and in this case I would like the cell to be =(E11*$J$13)*G11

That is the basic idea. I guess I would have to use some kind of VBA code... but I am not sure.
Anyone could help me out?

Thank you very much in advance! :)
 

Attachments

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,880
Office Version
365, 2010
Platform
Windows, Mobile
Select your cells in column G, copy, Select your cells in column F (must be the exact same size), PasteSpecial, check the Multiply option, click OK.

The F10 with be a hard coded value as a result, the F11 will be a formula.
Where there are blanks in column G then you will need a 1
 

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
21
Office Version
2019
Platform
Windows
Select your cells in column G, copy, Select your cells in column F (must be the exact same size), PasteSpecial, check the Multiply option, click OK.

The F10 with be a hard coded value as a result, the F11 will be a formula.
Thank you very much! This gets the job done, however the the paste special pastes the values of column G.
For example, in F11 the formula ends up being =(E11*$K$13)*1.03
See what I mean? I would like that 1.03 to be G11. This is because I might change the G10 1.03 for 1.05 maybe, and I would like the dynamic relation to stay between the F10 and G10 cells.

I hope I was able to explain myself :)

Thank you!!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,880
Office Version
365, 2010
Platform
Windows, Mobile
Then put 1 in your blank spaces in G, change your formula to =(E11*$K$13)*G11, then for the constants run
VBA Code:
Sub MultConst()
    Dim myCell As Range
    For Each myCell In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
        If Not myCell.HasFormula Then
            With myCell
                .Offset(, 1).Copy
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
                Application.CutCopyMode = False
            End With
        End If
    Next
End Sub
 

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
21
Office Version
2019
Platform
Windows
Then put 1 in your blank spaces in G, change your formula to =(E11*$K$13)*G11, then for the constants run
VBA Code:
Sub MultConst()
    Dim myCell As Range
    For Each myCell In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
        If Not myCell.HasFormula Then
            With myCell
                .Offset(, 1).Copy
                .PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
                Application.CutCopyMode = False
            End With
        End If
    Next
End Sub
Thank you for taking the time to help me out.

However, I am seeing that the VBA code will not be able to be used in a future workbook with different columns (I see that your code has hard-coded the F range and F2 cell i think). Also when I run the code I see that the paste value is performed, but ideally what I am looking for is to "append" the G column references to each of the F column in-cell values... so basically have a VBA to:

1. Select Column F (where I want the code applied)
2. Insert the "=" sign in case the column F cell doesn't have one
3. Insert an open parenthesis "(" after the "=" sign from step 1
4. Insert at the end of the cell a closing parenthesis ")"
(Note.- Now I can safely apply the factor)
5. Insert "*G<row number>" to the end of each cell in Column F... so in row 10 it would be -> =(<whatever is in cell F10>)*G10 and in row 11 it would be -> =(<whatever is in cell F11>)*G11 and so forth...

I would like, if at all possible and with all due respect, to get some code that I could use in the future and that would be applicable in different scenarios.

Thank you very much! :)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,880
Office Version
365, 2010
Platform
Windows, Mobile
3. Insert an open parenthesis "(" after the "=" sign from step 1
4. Insert at the end of the cell a closing parenthesis ")"
Why do you need the ( and the ) in the cells with values? if a cell had the value 32 in F11 then the formula would simply be
=32*G11 no need for =(32*G11) or =(32)*G11 or 32*(G11)

You also don't need brackets with your formula if it is =E11*$J$13
 

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
21
Office Version
2019
Platform
Windows
Why do you need the ( and the ) in the cells with values? if a cell had the value 32 in F11 then the formula would simply be
=32*G11 no need for =(32*G11) or =(32)*G11 or 32*(G11)
Just to make sure it works always in case I have the formula in Cell F10 like this =11-$M$14 where if I dont have the "(" and ")" the code would add the G10 term at the end and the cell would become =11-$M$14*G10 which would be wrong because the multiplication would take priority over the substraction. In this I would definately need =(11-$M$14)*G10

So basically just to be on the safe side always.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,880
Office Version
365, 2010
Platform
Windows, Mobile
Just to make sure it works always in case I have the formula in Cell F10 like this =11-$M$14
You obviously didn't note that I asked why the brackets are needed in the cells with Values.

So basically just to be on the safe side always.
I will write the code based on what you have posted but being honest if this isn't a one-off then really you should review how the data is created because having mixed data of values and formulas in the same column is not a good idea
 

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
21
Office Version
2019
Platform
Windows
You obviously didn't note that I asked why the brackets are needed in the cells with Values.


I will write the code based on what you have posted but being honest if this isn't a one-off then really you should review how the data is created because having mixed data of values and formulas in the same column is not a good idea
Ohh yes, I am sorry sir, I didnt notice the "values"word in your comments... you are correct, if it is values the parenthesis would not be needed.

I thank you very much for taking the time to help me with this problem. I do appreciate all of your help, thank you!!!!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,880
Office Version
365, 2010
Platform
Windows, Mobile
VBA Code:
Sub MultConst()
    Dim myCell As Range
    For Each myCell In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
        If Not myCell.HasFormula Then
            myCell.Formula = "=" & myCell.Value & "*G" & myCell.Row
        Else
            myCell.Formula = "=(" & Replace(myCell.Formula, "=", "") & ")" & "*G" & myCell.Row
        End If
    Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,241
Messages
5,485,597
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top