multiplying a column 4

[chris123321]

If I have values in column A that need to be multiplied by 10, how do I do that?

 

[xlhelp]

Enter 10 in a blank cell. Edit, cOpy.

Highlight cells in Column, go Edit, Paste Special, Multiply.

Member- AAAA Association Against Acronym Abusers

 

[chris123321]

very cool..thank you

 

[chris123321]

how about if some cells are empty in the column and after multiplying by 10 I dont want to have 0 displayed in those empty cells

 

[Rofeu]

Hi,

autofilter on nonblanks and then select and copy --> multiply.

OR

=IF(A1="","",A1*10) in a new column and then copy/pastevalues over column A and delete the column with the formula.

Cheers,

Roel

 

[chris123321]

Roel,

I tried autofiltering the column on nonblanks and then adding 10 to a cell copying, paste special -> multiply, but it still showed 0s in the empty columns.

Did I do something wrong? I'm still trying to understand the 2nd option you mentioned.

Thanks!

 

[xlhelp]

Enter 10 in a blank cell. Edit, Copy.

Highlight cells in Column, Press F5, click on Special, select Constants, then go to Edit, Paste Special, Multiply.

Member- AAAA Association Against Acronym Abusers

 

[Rofeu]

Sorry about that filter option. That doesn't work.

Very nice solution there, xlhelp, didn't know about that.

Cheers,

Roel

 

[chris123321]

very cool, thanks.

 

[KenWright]

The downside to this of course is that you lose the underlying data, albeit that may not matter. Personally i usually prefer to keep the raw data and then have say the multiplication factor out in another cell so that i can see what I am actually doing to that raw data.

In case that was of interest, it can be achieved by using a helper cell, eg A1 and in that cell put a value of 10. Now in any other blank cell, put in the formula =$A$1.

Now copy that cell with the formula, select all your records as previously described, and then do Edit / Paste Special / Multiply / Formulas.

You will now see that every cell of your data contains the original value but is now also multiplied by the value in cell A1, and any change to this value will be reflected in your data. Setting it back to 1 puts your data back as it was.

You can also now delete the helper cell that contained the =$A$1 bit.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[chris123321]

Interesting point..

I guess if you were doing a statistcal calculation with your raw data and later on needed to revert back to the original numbers you would apply the method you recommended.

In this case where all I needed to do is multiply by 10, it's fine to use the solution recommended by xlhelp.

Is that correct?

 

[KenWright]

Absolutely. Just wanted to offer you something that would allow you to know where you had come from depending on your data needs. (It's a little known trick, so thought it may benefit someone)

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[rezaxyz]

Hi all,

How can I do it in MS Word?

Any help appreciated

 

[Gavona]

How can I do it in MS Word?

I reckon you should try:
copy to excel, do as above, copy back to word!


Gavin

 

[rezaxyz]

Thank you Gavin, It works fine but is there any way by Table Formula?

 

[macropod]

Hi rezaxyz,

In a word, no. Word isn't Excel and what it can do with a table is severely restricted by comparison. You could use a macro.

Cheers

[MS MVP - Word]

 

[rezaxyz]

Thank you macropod,

Macro is a good idea, However I'll do it by Gavin suggestion.

Regards
Reza

 

[macropod]

Hi rezaxyz,

Here's some code to make the change in-situ:

Sub UpdateCellValues()
Dim a As Variant
Dim b As String
Dim c As String
Dim d As Variant
Dim e As String
e = "Please select one or more table cells before running this macro."
Dim oCell As Cell
With Selection
    If .Information(wdWithInTable) = True Then
        If Selection.Range.Text = "" Then
            MsgBox e
            Exit Sub
        End If
        a = InputBox("Please input the amount by which to vary the cell values.")
        b = InputBox("Please indicate the required action: (A)dd, (S)ubtract, (M)ultiply or (D)ivide?")
        c = InputBox("Skip Blanks? (Y/N)")
        For Each oCell In .Cells
            d = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
            If UCase(Left(c, 1)) = "N" Then
                If d = "" Then d = "0"
            End If
            If IsNumeric(d) = True Then
                If UCase(Left(b, 1)) = "A" Then oCell.Range.Text = d * 1 + a
                If UCase(Left(b, 1)) = "S" Then oCell.Range.Text = d * 1 - a
                If UCase(Left(b, 1)) = "M" Then oCell.Range.Text = d * a
                If UCase(Left(b, 1)) = "D" Then oCell.Range.Text = d / a
            End If
        Next oCell
    Else
        MsgBox e
    End If
End With
End Sub

Cheers

[MS MVP - Word]

 

[yogia]

Hi Chris:

how about if some cells are empty in the column and after multiplying by 10 I dont want to have 0 displayed in those empty cells

If it is simply a matter of not displaying 0, then you have couple of other options ...

1) You can go to TOOLS|Options|View|Windows_Options ... and uncheck Zero Display. This will of course suppress display of 0 everywhere.

2) If you want to suppress display of 0 on select cells, then you can use Custom Number Formatting as

#;General;


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi rezaxyz:

How can I do it in MS Word?

Within Word, you can insert an EXCEL Worksheet Object, create your Table, and do the Edit--Multiply operation, and when you are done, click outside the worksheet layout, you will have the table created in the Word document, but with the added feature that it is still editable as an EXCEL Worksheet Object.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com