Microsoft Excel help needed

January 11, 2006 5:24 PM

Microsoft Excel help needed.

I recently found that all of the values in a spreadsheet I'm using are out by a factor of ~0.8. I'd like to do something similar to a search and replace, where every cell that contains a number is replaced by 0.8 times that number. So, cell = 0.8*cell. I want the resulting cell to just be a raw value, not a formula (for easy copying and pasting). Can this be done?

[Running Microsoft Excel X for Mac, by the way]

posted by teem to computers & internet (11 comments total)

Insert new column next to current column.
Enter a formula that multiplies adjacent cell by 0.8.
Copy and paste that formula over the whole column.
Recalc (if on manual recalc).
Copy the new column. Right Click. Paste Special. Values.
posted by randomstriker at 5:26 PM on January 11, 2006

Create a column with the formula you want. Then highlight the column, copy it, then hit Edit>Paste Special... and choose "Values."
posted by i love cheese at 5:29 PM on January 11, 2006

randomstriker: Thanks, but I was hoping for something that didn't require new columns. It's every single number in my spreadsheet (well, the old guy's spreadsheet...): there's lots of them, and they aren't all in orderly columns. I may end up using your method, but something more like a widespread search and replace would be ideal.
posted by teem at 5:30 PM on January 11, 2006

New sheet or tab.
Use a 3D formula (i.e. one with references to cells in a different sheet or tab), apply it across a range of same dimensions as your original table.
Copy, Paste Special.
posted by randomstriker at 5:34 PM on January 11, 2006

Hit alt-F11, select the sheet you're working in, then paste in this macro:

Sub ValuesByPointEight()
For Each cell In Range("A1:D25")
If cell.Value <> "" Then cell.Value = cell.Value * 0.8
Next
End Sub


....adjusting the "A1:D25" to whatever range you're using.
posted by pompomtom at 5:34 PM on January 11, 2006

actually scratch that... that gets broken by any text.
posted by pompomtom at 5:38 PM on January 11, 2006

I believe you can use the multiplier function in Excel - Microsoft help shows the steps to "multiply a range of numbers by a number" (second item on the page).

It doesn't breaks if there is text in the selected range but blank cells will have a zero value.
posted by bCat at 5:45 PM on January 11, 2006

This one works though...


Sub ValuesByPointEight()

For Each cell In Range("a1:d35")
If cell.Value <> cell.Text Then cell.Value = cell.Value * 0.8
Next

End Sub
posted by pompomtom at 5:46 PM on January 11, 2006

You can do it with one extra cell:

1. Select the cells you want to change. With Edit > Go To... > Special... you can select a subset of all the cells, such as all those with numbers.
2. Create a new worksheet, put .8 in a cell, copy it.
3. Go back to original worksheet, choose Edit > Paste Special.... Under Operation, click Multiply. Click Ok.
posted by blm at 6:03 PM on January 11, 2006

Thanks pompomtom, exactly what I was after.
posted by teem at 6:36 PM on January 11, 2006

you could also put .8 in a cell off to the side, copy that cell, highlight your range of cells and then Edit -> Paste Special -> Multiply.

But it sounds like I'm too late :(
posted by mullacc at 7:49 PM on January 11, 2006