User Name
Password

Go Back   Planetarion Forums > Non Planetarion Discussions > General Discussions
Reload this Page Excel help
Register FAQ Members List Calendar Arcade Today's Posts

Reply
View First Unread View First Unread  
Thread Tools Display Modes
Unread 10 Feb 2004, 13:52   #1
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Excel help
I need a bit of help with MS Excel. I'm now making my planning in Excel but I can't figure out how to perform a subtraction on a whole row and display it in another row.

Each row is a task. Column C holds the planned hours, column D holds the actual hours worked on a task. Now, I want column E to show the difference between column C and D and this difference has to be summed in the totals row. How do I get my difference in column E without having to enter the formula manually at each row?

TY!
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 10 Feb 2004, 13:55   #2
Dante Hicks
Clerk
 
Join Date: Jun 2001
Posts: 13,940
Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Excel help
Can you just sum each row/column in the end formula?

So =SUM(B2:B8)-SUM(A2:A8) or something like that?
Dante Hicks is offline   Reply With Quote
Unread 10 Feb 2004, 14:05   #3
Tomkat
:alpha:
 
Tomkat's Avatar
 
Join Date: May 2002
Location: London, UK
Posts: 7,871
Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Excel help
Do one row:

E1=C1-D1

Then hold the cursor over the cell, and move it to the little black square in the bottom right of it. You should get a little cross there. Click and drag down the E column. It'll do it all for you.
__________________
"There is no I in team, but there are two in anal fisting"
Tomkat is offline   Reply With Quote
Unread 10 Feb 2004, 14:07   #4
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: Excel help
@Dante

That'd give me the difference between the planned hours and the actual hours worked. That's not what I want. I want the difference between the planned hours and the actual hours where the task already has been started/completed. A started/completed task can be identified by a value larger than 0 filled in in Culumn D.
But that's not really the issue. Generally what I can't figure out is how to perform an operation on two culumns without having to copy the formula in every field. If I can get that to work I can figure out the rest.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 10 Feb 2004, 14:12   #5
CjC
Throwing Shapes
 
CjC's Avatar
 
Join Date: Apr 2000
Posts: 797
CjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant future
Re: Excel help
What Tomkat said.
__________________
Time is an Illusion, Lunchtime doubly so.
CjC is offline   Reply With Quote
Unread 10 Feb 2004, 14:13   #6
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: Excel help
Quote:
Originally Posted by Tomkat
Do one row:

E1=C1-D1

Then hold the cursor over the cell, and move it to the little black square in the bottom right of it. You should get a little cross there. Click and drag down the E column. It'll do it all for you.
OK, that's getting very close... but it would really be awesome if I could also do "insert row" somewhere and the formula would be automatically copied
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 10 Feb 2004, 14:29   #7
CjC
Throwing Shapes
 
CjC's Avatar
 
Join Date: Apr 2000
Posts: 797
CjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant futureCjC has a brilliant future
Re: Excel help
Not sure its possible. You might be able to set up a vba trigger (again not sure if there is a OnInsertRow event or something like that.)
__________________
Time is an Illusion, Lunchtime doubly so.
CjC is offline   Reply With Quote
Unread 10 Feb 2004, 14:33   #8
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: Excel help
Nah, I don't intend to program Excel macros. If it isn't possible with standard click and drag stuff then nevermind.
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 10 Feb 2004, 14:56   #9
madi
a little bit broken
 
madi's Avatar
 
Join Date: Jun 2003
Posts: 1,405
madi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better place
Re: Excel help
=(SUMIF(D1:D500,"> 0",C1:C500))-(SUMIF(D1:D500,">0",D1:D500))
__________________
i came, i saw, i made a bit of a mess
madi is offline   Reply With Quote
Unread 10 Feb 2004, 14:59   #10
madi
a little bit broken
 
madi's Avatar
 
Join Date: Jun 2003
Posts: 1,405
madi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better place
Re: Excel help
that is assuming you have no more than 500 rows
you can easily change the 500 to read 1000 or more if you need to

this will show the total time planned minus the total time taken where a time taken has been entered
__________________
i came, i saw, i made a bit of a mess
madi is offline   Reply With Quote
Unread 10 Feb 2004, 19:47   #11
Caesar2
Commander
 
Caesar2's Avatar
 
Join Date: Sep 2001
Location: Netherlands
Posts: 146
Caesar2 is just really niceCaesar2 is just really niceCaesar2 is just really niceCaesar2 is just really nice
Re: Excel help
Quote:
Originally Posted by Structural Integrity
OK, that's getting very close... but it would really be awesome if I could also do "insert row" somewhere and the formula would be automatically copied
Select a row, Copy, Insert copied cells?
__________________
Quote:
Originally posted by Cochese
Cathaar are not overpowered.

You were just "bashed", live with it.
Caesar2 is offline   Reply With Quote
Unread 10 Feb 2004, 19:55   #12
Intrepid00
Registered User
 
Join Date: Aug 2000
Posts: 1,967
Intrepid00 is an unknown quantity at this point
Re: Excel help
AutoFill?
Intrepid00 is offline   Reply With Quote
Unread 10 Feb 2004, 20:47   #13
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: Excel help
Quote:
Originally Posted by madi
that is assuming you have no more than 500 rows
you can easily change the 500 to read 1000 or more if you need to

this will show the total time planned minus the total time taken where a time taken has been entered
\o/

In combination with Tomcats suggestion I think I got what I want now.

Caesar2: I'm a lazy bastard. Every click I have to make for updating the planning is one too much.

Intrepid, how do I do autofill?
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 10 Feb 2004, 20:50   #14
Tomkat
:alpha:
 
Tomkat's Avatar
 
Join Date: May 2002
Location: London, UK
Posts: 7,871
Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Tomkat has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Excel help
I think the technical name for what I told you is "autofill".

IT'S TomKat too. Grawr.
__________________
"There is no I in team, but there are two in anal fisting"
Tomkat is offline   Reply With Quote
Unread 10 Feb 2004, 20:50   #15
Dante Hicks
Clerk
 
Join Date: Jun 2001
Posts: 13,940
Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Excel help
Edit=>Fill Down/Fill-Right

Although that's pretty much the same as the whole dragging the corner thingie.
Dante Hicks is offline   Reply With Quote
Unread 10 Feb 2004, 20:55   #16
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: Excel help
oh.... OK
TomKat and Dante
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 11 Feb 2004, 00:14   #17
madi
a little bit broken
 
madi's Avatar
 
Join Date: Jun 2003
Posts: 1,405
madi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better placemadi single handedly makes these forums a better place
Re: Excel help
sorry
i thought you wanted to display the total over all in a single cell
*sigh*
__________________
i came, i saw, i made a bit of a mess
madi is offline   Reply With Quote
Unread 11 Feb 2004, 19:24   #18
Structural Integrity
Rawr rawr
 
Structural Integrity's Avatar
 
Join Date: Dec 2000
Location: Upside down
Posts: 5,300
Structural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriendStructural Integrity needs a job and a girlfriend
Re: Excel help
You pointed me with my nose on the SUMIF and IF functions madi. Those are quit handy.
I'm beginning to like Excel even. It's quite handy. But if it could create the bar diagram belonging to the planning automatically I'd be totally in love with it

http://www.structweb.com/workfolder/planningtable.xls
__________________
"Yay"
Structural Integrity is offline   Reply With Quote
Unread 11 Feb 2004, 19:33   #19
Dante Hicks
Clerk
 
Join Date: Jun 2001
Posts: 13,940
Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.Dante Hicks has ascended to a higher existance and no longer needs rep points to prove the size of his e-penis.
Re: Excel help
I actually like Excel, although it's misused horribly in the business world.

Half the spreadsheets we use at work (and we literally thousands of different ones) would be infinitely better managed in a proper db setup.
Dante Hicks is offline   Reply With Quote
Reply

« Previous Thread | General Discussions | Next Thread »


Forum Jump


All times are GMT +1. The time now is 08:14.
Planetarion - Top

Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2002 - 2018