Go Back   Great Lakes 4x4. The largest offroad forum in the Midwest > General 4x4 Stuff > The Pub
GL4x4 Live! GL4x4 Casino

The Pub A friendly forum where everybody is nice, and will answer any questions you have about life.







Search
Reply
 
Thread Tools
Old February 28th, 2007, 06:36 AM   #1
clarkstoncracker
lol
 
clarkstoncracker's Avatar
 
Join Date: 11-03-05
Location: OC - MI
Posts: 42,292
iTrader: (39)
Mentioned: 72 Post(s)
Send a message via AIM to clarkstoncracker
Default excel spreadsheet formula help.

I need to take column D, and subtract column F, and remove the decimal point.

I have 640 rows, and there has to be a better way then editing each formula box to =D5-F5, =D6-F6, and so on. Secretaries do not arrive until 9.

Removing the decimal is not a must, but would be awesome.
__________________
clarkstoncracker is offline   Reply With Quote
Old February 28th, 2007, 06:39 AM   #2
shawn
Resident HVAC/R Jambi
 
shawn's Avatar
 
Join Date: 11-05-05
Location: Milford/Commerce
Posts: 9,939
iTrader: (24)
Mentioned: 1 Post(s)
Send a message via Yahoo to shawn
Default

Once you have a formula for one box, if you take your pointer to the lower right hand corner of that box, it will then show up as a + , click and drag all the way down as far as you can and it will copy all of those boxes with the same formula (but for thas specific row and column). At the end, you may have a box that appears when you let off the mouse button, and it may give you options hon how you will copy, it won't pertain in this situation. Hope that made sense.
shawn is online now   Reply With Quote
Old February 28th, 2007, 06:40 AM   #3
shawn
Resident HVAC/R Jambi
 
shawn's Avatar
 
Join Date: 11-05-05
Location: Milford/Commerce
Posts: 9,939
iTrader: (24)
Mentioned: 1 Post(s)
Send a message via Yahoo to shawn
Default

As far as the decimal, highlight the column and right click then click on cell properties, then the number tab, then select number and 0 for the decimal.
shawn is online now   Reply With Quote
Old February 28th, 2007, 06:45 AM   #4
roll-bar Bob
Senior Member
 
roll-bar Bob's Avatar
 
Join Date: 01-20-06
Location: M CITY
Posts: 2,737
iTrader: (3)
Mentioned: 0 Post(s)
Default

roll-bar Bob is offline   Reply With Quote
Old February 28th, 2007, 06:46 AM   #5
biohazardbill
The Biohazards
 
biohazardbill's Avatar
 
Join Date: 03-02-06
Location: Forsyth, Ga
Posts: 4,147
iTrader: (10)
Mentioned: 2 Post(s)
Default

or round it

however if you need to keep the numbers after the decimal then :

=(D6-F6)*100

Providing it is cents after the decimal

=round(D6-F6)

=roundup(D6-F6) if you need to go to the next whole number

you get the point
biohazardbill is offline   Reply With Quote
Old February 28th, 2007, 06:48 AM   #6
clarkstoncracker
lol
 
clarkstoncracker's Avatar
 
Join Date: 11-03-05
Location: OC - MI
Posts: 42,292
iTrader: (39)
Mentioned: 72 Post(s)
Send a message via AIM to clarkstoncracker
Default

Shawn, you are probably the smartest person evar!

thank you so very much.


Bill, that is exactly what I needed, but I didn't think it was possible. this is going to be a great day.
__________________
clarkstoncracker is offline   Reply With Quote
Old February 28th, 2007, 06:49 AM   #7
Lothos
KD8GKB
 
Lothos's Avatar
 
Join Date: 11-17-05
Location: .5 past lightspeed
Posts: 6,506
iTrader: (3)
Mentioned: 0 Post(s)
Send a message via ICQ to Lothos Send a message via AIM to Lothos Send a message via Yahoo to Lothos
Default

You actually only need to do the subtraction. If you format the cell for the sum to not have decimal places it will automatically round it.
Lothos is offline   Reply With Quote
Old February 28th, 2007, 06:51 AM   #8
Cube
Captain of my pants
 
Cube's Avatar
 
Join Date: 11-06-05
Location: Traverse City
Posts: 4,158
iTrader: (7)
Mentioned: 1 Post(s)
Default

hehe I like getting help on here too alot of good people :)
Cube is offline   Reply With Quote
Old February 28th, 2007, 06:55 AM   #9
biohazardbill
The Biohazards
 
biohazardbill's Avatar
 
Join Date: 03-02-06
Location: Forsyth, Ga
Posts: 4,147
iTrader: (10)
Mentioned: 2 Post(s)
Default

Quote:
Originally Posted by shawn View Post
Once you have a formula for one box, if you take your pointer to the lower right hand corner of that box, it will then show up as a + , click and drag all the way down as far as you can and it will copy all of those boxes with the same formula (but for thas specific row and column). At the end, you may have a box that appears when you let off the mouse button, and it may give you options hon how you will copy, it won't pertain in this situation. Hope that made sense.

Another trick for this is:
Lets say your putting this formula in column G.
As long as there are values in every cell of column F you can just double click that little box in column G Shawn was talking about and it will autofill all the way down until there is a null value in the column F. This way you dont have to pull it all the way down which can be a pain in the ass if you have thousands of records.
biohazardbill is offline   Reply With Quote
Old February 28th, 2007, 06:55 AM   #10
Lothos
KD8GKB
 
Lothos's Avatar
 
Join Date: 11-17-05
Location: .5 past lightspeed
Posts: 6,506
iTrader: (3)
Mentioned: 0 Post(s)
Send a message via ICQ to Lothos Send a message via AIM to Lothos Send a message via Yahoo to Lothos
Default

oh, here was my big thing I learned last year with excel that surprisingly none of the finance people knew how to do. Say you have a situation similar to CC's where you have to copy a formula down a bunch of rows EXCEPT one value in the formula remains constant, but you also need a place to change the value of the constant without having to edit every single rows formula?


Well, come to find out you can change the name of a cell in the upper left and then reference that name as a variable. IE: A1+variable=xxx and then when you do the click and drag to copy down the column its smart enough to copy the variable name whilst incrementing the other.
Lothos is offline   Reply With Quote
Old February 28th, 2007, 06:56 AM   #11
Lothos
KD8GKB
 
Lothos's Avatar
 
Join Date: 11-17-05
Location: .5 past lightspeed
Posts: 6,506
iTrader: (3)
Mentioned: 0 Post(s)
Send a message via ICQ to Lothos Send a message via AIM to Lothos Send a message via Yahoo to Lothos
Default

Quote:
Originally Posted by biohazardbill View Post
Another trick for this is:
Lets say your putting this formula in column G.
As long as there are values in every cell of column F you can just double click that little box in column G Shawn was talking about and it will autofill all the way down until there is a null value in the column F. This way you dont have to pull it all the way down which can be a pain in the ass if you have thousands of records.
sweet. thats a new one for me.
Lothos is offline   Reply With Quote
Old February 28th, 2007, 07:02 AM   #12
84Scrambler
Senior Member
 
Join Date: 11-07-05
Location: Lansing
Posts: 5,943
iTrader: (8)
Mentioned: 0 Post(s)
Send a message via AIM to 84Scrambler
Default

A sweet trick I learned a while back. If you have your spreadsheet filtered to see only certain data, you can't do a simple copy/paste to get that data to a new spreadsheet. Once you have the data filtered do where you only see what you want to copy, highlight the entire area you want to copy, then click Alt+; and that will select only what is visible. Then just go to your new screen and paste it in. This will not carry over any formulas, but you can just copy those and paste-special-formula to carry them over. If you filter a lot of data, this is a very handy trick.
84Scrambler is offline   Reply With Quote
Old February 28th, 2007, 07:05 AM   #13
84Scrambler
Senior Member
 
Join Date: 11-07-05
Location: Lansing
Posts: 5,943
iTrader: (8)
Mentioned: 0 Post(s)
Send a message via AIM to 84Scrambler
Default

If you are only trying to hide the decimal vs. actually cutting it off, the properties-number-decimal=0 thing is fine. If you are actually just trying to eliminate everything to the right of the decimal, you need to use the =trunc() formula (truncate), then do the properties-number-decimal place process.
84Scrambler is offline   Reply With Quote
Old February 28th, 2007, 07:08 AM   #14
Archie_G
Senior Member
 
Archie_G's Avatar
 
Join Date: 11-28-06
Location: Rochester Hills
Posts: 168
iTrader: (4)
Mentioned: 0 Post(s)
Default

Quote:
Originally Posted by Lothos View Post
oh, here was my big thing I learned last year with excel that surprisingly none of the finance people knew how to do. Say you have a situation similar to CC's where you have to copy a formula down a bunch of rows EXCEPT one value in the formula remains constant, but you also need a place to change the value of the constant without having to edit every single rows formula?


Well, come to find out you can change the name of a cell in the upper left and then reference that name as a variable. IE: A1+variable=xxx and then when you do the click and drag to copy down the column its smart enough to copy the variable name whilst incrementing the other.
what I do is pick a cell to hold the constant, and in the equation you add the '$' (ie: $A$2) - then when you copy it around, the A and the 2 never change.
Archie_G is offline   Reply With Quote
Old February 28th, 2007, 07:12 AM   #15
deuce228
Redneck Poser
 
deuce228's Avatar
 
Join Date: 11-07-05
Location: West Branch, MI
Posts: 7,971
iTrader: (8)
Mentioned: 0 Post(s)
Default

Quote:
Originally Posted by Archie_G View Post
what I do is pick a cell to hold the constant, and in the equation you add the '$' (ie: $A$2) - then when you copy it around, the A and the 2 never change.
Or just $a2 or A$2 if you only want one particular value to stay constant.
deuce228 is offline   Reply With Quote
Old February 28th, 2007, 07:19 AM   #16
RyeBread
Catch the wave
 
RyeBread's Avatar
 
Join Date: 11-08-05
Location: Fenton
Posts: 7,945
iTrader: (2)
Mentioned: 0 Post(s)
Default

if you use excel a lot, from everything to the financial, to statistical to even the text transformations, I have found the following add-in a huge time saver - especially if importing/exporting/transforming data in and out of excel.

and, it's free. (or was when I put it on my various machines)

http://www.asap-utilities.com/
RyeBread is online now   Reply With Quote
Old February 28th, 2007, 07:24 AM   #17
Hacksaw
WWSD - What would stan do
 
Hacksaw's Avatar
 
Join Date: 11-04-05
Location: Isle Of TREASURE, FL
Posts: 11,204
iTrader: (5)
Mentioned: 10 Post(s)
Default

I learned that if you type =sum(a1+a2) it adds up the stuff in those little boxes. Fricken computers theese days I tell ya what.
Hacksaw is offline   Reply With Quote
Old February 28th, 2007, 07:51 AM   #18
Lothos
KD8GKB
 
Lothos's Avatar
 
Join Date: 11-17-05
Location: .5 past lightspeed
Posts: 6,506
iTrader: (3)
Mentioned: 0 Post(s)
Send a message via ICQ to Lothos Send a message via AIM to Lothos Send a message via Yahoo to Lothos
Default

Quote:
Originally Posted by deuce228 View Post
Or just $a2 or A$2 if you only want one particular value to stay constant.
I think the reason that didn't work for me is I was using the value on multiple sheets.
Lothos is offline   Reply With Quote
Old February 28th, 2007, 09:32 AM   #19
RyeBread
Catch the wave
 
RyeBread's Avatar
 
Join Date: 11-08-05
Location: Fenton
Posts: 7,945
iTrader: (2)
Mentioned: 0 Post(s)
Default

Quote:
Originally Posted by Lothos View Post
I think the reason that didn't work for me is I was using the value on multiple sheets.
you can specify the worksheet in that absolute reference cell as well, but it may get trickier than just naming a cell/range/variable as you did it.

it would look something like this: =Sheet2!B$4 where "Sheet2" = the name of the worksheet tab
RyeBread is online now   Reply With Quote
Old February 28th, 2007, 09:37 AM   #20
Lothos
KD8GKB
 
Lothos's Avatar
 
Join Date: 11-17-05
Location: .5 past lightspeed
Posts: 6,506
iTrader: (3)
Mentioned: 0 Post(s)
Send a message via ICQ to Lothos Send a message via AIM to Lothos Send a message via Yahoo to Lothos
Default

Quote:
Originally Posted by RyeBread View Post
you can specify the worksheet in that absolute reference cell as well, but it may get trickier than just naming a cell/range/variable as you did it.

it would look something like this: =Sheet2!B$4 where "Sheet2" = the name of the worksheet tab
cool. Now why didn't M$ help have this when i was looking for it?
Lothos is offline   Reply With Quote
Reply
Great Lakes 4x4. The largest offroad forum in the Midwest > General 4x4 Stuff > The Pub
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump



All times are GMT -5. The time now is 10:27 PM.


Powered by: vBulletin, Copyright 2000 - 2012, Jelsoft Enterprises Ltd.
User Alert System provided by Advanced User Tagging (Lite) - vBulletin Mods & Addons Copyright © 2014 DragonByte Technologies Ltd. Runs best on HiVelocity Hosting.
Copyright 2005 - 2012 Cracker Enterprises - Powered by Linux
vB Ad Management by =RedTyger=
Page generated in 0.20895 seconds with 50 queries