Frankjake
the Beaver State
Senior Member
Joined: 05/27/2002
View Profile
 Offline
|
I have a column of numbers in Excel. Each number is different or I'l say they don't follow any cerain criteria for formula. They are input as numbers but the fields have no function or formula. Entered like text but the field is formatted as a currency field and shows the dollar sign $.
I want to add $5.00 to every number, or I want that number to be $5.00 more than it is now. I don't want to have to change the look for format of the spreadsheet, I have a lot of columns that are used for text and right now I have it set so when I print it, it prints exactly how I want it to.
Is there a way to quickly and easily increase every number by $5.00?
Not every row in the column has a number, but other columns in the row may have text.
|
flyfishing48
Cheboygan,MI
Senior Member
Joined: 11/19/2004
View Profile
Offline
|
Frankjake,
The first approach that comes to mind would be as follows.
1) insert a column next to the one you want to work on.
2) in the new column (for example R) adjacent to the first number write a formula like (=q1+5)
3)copy the formula and then paste in the rest of the column
4)Having the columns next to each others gives you a quick check. You can then format the new column for $ and delete the old column to get your print format back.
Hope this helps.
Tom & Glo:
Hobbs (Golden Retriver)
Chevy 2500 Ext Cab Duramax
Sundance 2900 MK
.jpg)
|
joshjack
Alabama
Senior Member
Joined: 11/01/2007
View Profile
Offline
|
I agree with the above approach, it is definitely the fastest. There might be a way to use a Visual Basic loop and increment each row by $5 but I think that would take a lot longer. Another option would be to copy the entire column and put it in the next page (or sheet). Then write a formula in the original column like this =Sheet2!C1 + 5. Then highlight the first row of the column with the new formula in it, hit copy (CTRL+C), highlight the rest of the column and paste (CTRL+V)
In this way you preserve the original numbers.
J
03 Ram 1500 Quad Cab with Hemi 5.7, HD tow package, K&N Cold Air, B&W Companion + Ball, Prodigy,
1987 Prowler 27.5',
Macbook Pro 17" (Fastest Vista Laptop  , iPhone)
Me and the DW and our
2 Dogs: Jackson (golden), Pumba (min-pin)
|
PRT
NY/FL
Senior Member
Joined: 05/25/2001
View Profile
|
I just tried flyfisher's plan since I felt like it and it worked perfectly - althought I didn't delete the original column and created a spreadsheet just to try the idea! I did not consider formatting.
Pat
May the road rise with you, the wind be always at your back.
|
flyfishing48
Cheboygan,MI
Senior Member
Joined: 11/19/2004
View Profile
Offline
|
frankjake,
Don't delete the old colum, it will give you a ref error. Sorry about that. Also DW has another approach she will try and I may get back to you with something.
|
|
|
Ryles
Woodstock, GA
Senior Member
Joined: 11/03/2006
View Profile
Offline
|
Use the method above but do not delete the old column, it will give you an error. Instead of deleting the old column:
Copy the new column.
Select the old column.
Instead of pasting the new column over the old column, select "paste special".
Then choose "values". This will paste the new values over the old values without changing the formatting. The values will be just that, not a formula anymore.
Now you can delete the "new" column.
Ryles
|
cryptobrian
maryland
New Member
Joined: 04/28/2008
View Profile
Offline
|
Copy the entire column and paste it into a new "Sheet" or free area of your current sheet.
Next to this pasted column, do as described above ... entering a formula in the adjacent cell to the first in the column that adds 5. Copy that cell all the way down your column (quick tip, select the first cell with your formula, the cell will have a bold box with a small square in the lower right corner ... click and hold on the square and drag it down, all the cells will be filled in automatically with your formula).
Now, copy the new "plus 5" column of values.
Go back to the original location, select the first cell of the column, right click and choose "Paste Special". Choose the option to past values only ... this will preserve all of your original formatting, swapping out the numbers only.
Brian
2009 Rockwood Roo 233
1997 Chevy 3500
|
Campin'Deb
Milford, CT
Full Member
Joined: 03/21/2007
View Profile
Offline
|
Frank, No formulas are necessary. Try these steps:
key a '5' into any unused cell
highlight the '5' and click 'copy'
highlight all of the cells where you want to increase the values by '5'
right-click the mouse
click 'Paste Special'
click the radio button next to 'Add'
click 'OK"
Deb & Cathy
Shilo Ann - 10 yr old Xolo
Milford, CT
2006 Puma 29FKSS
|
Frankjake
the Beaver State
Senior Member
Joined: 05/27/2002
View Profile
Offline
|
EVERYONE, I appreciate all your help but Deb's was the fasest and easiest I think. I got the my entire book done in a matter of minutes. Ihave 8 pages of number in two separate columns. And as I said, there are blank fields in some of the columns. I just selected up to a blank field, copied the $5.00 and did a paste specail, then add and it worked quick and easy.
Thank you again for you suggestions.
|
baywoodbill
California
Senior Member
Joined: 11/10/2005
View Profile
Offline
|
Deb, that's a terrific bit of knowledge to add to my brain. Thanks.
|
|
|
