In a Microsoft Excel spreadsheet, how can I automate this process?

Question

I need to add 5% to the value of the cells in a column, then round the new values to the nearest whole number (or UP to the nearest whole number). I would like to be able to do this to all of cells in a column at once.

Answer 1

Here is one way to perform the computations 'at once'. The following macro will round to the nearest whole number. It assumes the column is column A. If your column is not A, change the three "A" references to your column letter, i.e. "F", "M", etc.



Copy the following macro to the clipboard:



Sub Percentage_and_Round()

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

Cells(i, "A").Value = Application. Round(Cells(i, "A").Value * 1.05, 0)

Next

End Sub



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste the macro into the editing area to the right.



Close the VBE and return to the worksheet.



Press ALT + F8



When the Macros window opens, highlight the macro and click 'Options..'



Enter a letter to be used as a keyboard shortcut and click 'OK'.



Close the Macros window.



To run the macro, press Ctrl + your shortcut letter.

Answer 2

This is how I would do it:



Let's say your original figures are in cells A1, A2, A3, A4 ....., and the 'value plus 5%' column is column B



The short story: put your formula into cell B1 - drag & copy it to the other relevant cells in column B - Set number format of Column B to 'no decimal places'



The 'long story':

* The formula you need to put in B1 is: =A1*1.05 (multiplying by 1.05 gives you 105% of the figure in A1, i.e. it gives you the answer in one move)

* Click on B1 and hover over the little black square at the bottom right corner of the active cell - when the mouse pointer turns into a skinny black cross, Click and drag your formula down the column - Excel will make the necessary address adjustments for each new row

* Select Column B and set the number format to '0 decimal places'

Answer 3

it truly is particularly a possibility with caveats. Its extremely not straightforward although. a million. you should create a scheduled job for your computer...ideally at your time once you're effective now to not be using the computer. 2. you'll want to position a macro that does your job contained in the workbook open journey. you'll likely ought to do somewhat trickeration like monitoring the finest replace date in a hidden worksheet to shop from operating the macro each and every time you open it. 3. The workbook will want to be signed with a digital certificate, or the workbook placed in a relied on area, in the different case the workbook will on the spot the person to allow the macro to run.

Answer 4

u need to use the fx bar to add 5% to the value of the cells in a column



then u can use celling function



CEILING (NUMBER,SIGNIFICANCE)



u can send me a sample data (not the real data) to my academic id [e-mail]

if u want

Answer 5

Do the formula in the first cell. Then just copy into the rest.