Math operation between a column with unique formulas and another column

dejhantulip

Board Regular
Joined
Sep 9, 2015
Messages
58
Office Version
  1. 365
Platform
  1. 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

  • Problems.png
    Problems.png
    15.6 KB · Views: 15

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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! :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!!!!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,722
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top