-
NOW LIVE! Into the Woods--new character species, eerie monsters, and haunting villains to populate the woodlands of your D&D games.
I have an excel question
- Thread starter Aeson
- Start date
XCorvis
First Post
Are you familiar with the Fill commands? Enter a number or formula in a cell. Select that cell and the ones below it and hit Ctrl-D. It will enter the number in all the other cells. If you used a formula, it will change the formula to use the next appropriate cell. It's hard to explain.
For example: In cell A1, enter "1". In cell A2, enter "2". In cell B1, enter "=A1". Select B1 and B2 and hit Ctrl-D (Fill Down). B1 should read "1" and B2 should read "2". The formula for B2 has changed to read "=A2".
Anyway, using that technique you can change multiple cells at the same time. It still might not be appropriate for your needs.
For example: In cell A1, enter "1". In cell A2, enter "2". In cell B1, enter "=A1". Select B1 and B2 and hit Ctrl-D (Fill Down). B1 should read "1" and B2 should read "2". The formula for B2 has changed to read "=A2".
Anyway, using that technique you can change multiple cells at the same time. It still might not be appropriate for your needs.
Ferghis
First Post
I'm guessing you've already got a spreadsheet filled out that you want to change. There's no easy way I know of that will just take what you have and add 10% to it without changing each cell. I have two suggestions that work work around the problem:
1. Insert a new column between every current column. Fill the new columns with the formula =[cell to the left]*1.1. Hide the old columns.
2. Create a new sheet (if you don't have a free one available). Fill the relevant part of the sheet with the formula =[same cell in other sheet]*1.1. Use the new sheet.
Neither of these solutions does what you asked to do, but solve the problem by hiding the old values. I imagine there is a macro in VB that you can program to replace the values with the increased value, but I don't know it.
1. Insert a new column between every current column. Fill the new columns with the formula =[cell to the left]*1.1. Hide the old columns.
2. Create a new sheet (if you don't have a free one available). Fill the relevant part of the sheet with the formula =[same cell in other sheet]*1.1. Use the new sheet.
Neither of these solutions does what you asked to do, but solve the problem by hiding the old values. I imagine there is a macro in VB that you can program to replace the values with the increased value, but I don't know it.
If you are wanting to increment the same amount over a range, the easiest way to do this is use paste special. You can add, subtract, multiply, and divide.
1. Find an empty cell, enter the amount you want to change, hit enter, and then copy the cell using ctrl-v or copy from the menu bar.
2. Select the range you want to modify, right click, and choose "Paste Special".
3. Select one of the math options.
That's it. The one drawback is if you select a cell that is blank, it will be populated with a number (0 or the number you are adding/subtracting).
Otherwise, you can create a short vba script to go through each cell of the range.
Skaven13
1. Find an empty cell, enter the amount you want to change, hit enter, and then copy the cell using ctrl-v or copy from the menu bar.
2. Select the range you want to modify, right click, and choose "Paste Special".
3. Select one of the math options.
That's it. The one drawback is if you select a cell that is blank, it will be populated with a number (0 or the number you are adding/subtracting).
Otherwise, you can create a short vba script to go through each cell of the range.
Skaven13
Last edited:
Drawmack
First Post
Here's what I would do
1) Add a blank worksheet in the existing workbook (if one does not already exist)
2) In Cell A1 type percent to increment by
3) In Cell B1 enter 110% (be sure to include the % sign)
4) In Cell A2 type =(
5) Click on cell A1 in the already populated sheet
6) type *
7) Click on cell B1 in the new sheet
8) Press enter
9) Use the fill handle (dark black square on the bottom right of the selected cell) to copy the value out until all the columns or the old sheet are highlighted (for example if the old sheet is filled to column 19, fill the new one to column 19) and down until the rows of the old sheet +1 are highlighted (for example if the old sheet it filled to row h highlight to row I)
IMPORTANT
If your old sheet has and text labels you will have to copy those cells and paste them into the new sheet.
1) Add a blank worksheet in the existing workbook (if one does not already exist)
2) In Cell A1 type percent to increment by
3) In Cell B1 enter 110% (be sure to include the % sign)
4) In Cell A2 type =(
5) Click on cell A1 in the already populated sheet
6) type *
7) Click on cell B1 in the new sheet
8) Press enter
9) Use the fill handle (dark black square on the bottom right of the selected cell) to copy the value out until all the columns or the old sheet are highlighted (for example if the old sheet is filled to column 19, fill the new one to column 19) and down until the rows of the old sheet +1 are highlighted (for example if the old sheet it filled to row h highlight to row I)
IMPORTANT
If your old sheet has and text labels you will have to copy those cells and paste them into the new sheet.
Similar Threads
