The Joel on Software Discussion Group (CLOSED)

A place to discuss Joel on Software. Now closed.

This community works best when people use their real names. Please register for a free account.

Other Groups:
Joel on Software
Business of Software
Design of Software (CLOSED)
.NET Questions (CLOSED)
TechInterview.org
CityDesk
FogBugz
Fog Creek Copilot


The Old Forum


Your hosts:
Albert D. Kallal
Li-Fan Chen
Stephen Jones

How to subtract two columns in Excel?

Hi,
 I just finished joel's nice article about Painless schedule and wanna to create a template like that. I have tree columns called: Current Estimate, Elapsed, Remain and it is obvious that Remain = Curr. Est - Elaps.
 So my questions is that how could I declare this formula in Excell or OpenOffice SpreadSheet to automatically perform the operation whenever I insert data into Curr. Est and Elaps?
 -Thanks
Ach Send private email
Saturday, July 22, 2006
 
 
You just write the formula in as the cell value. This is very basic excel stuff.
Stephen Jones Send private email
Saturday, July 22, 2006
 
 
" Creating Formulas

  1. Click the cell that you want to enter the formula.
  2. Type = (an equal sign).
  3. Click the Function Button
  4. Select the formula you want and step through the on-screen instructions "

or just type "=(a2-b2)" (no quotes) in C2
dan levine Send private email
Saturday, July 22, 2006
 
 
Thanks Dan,
 But I wanna to perform this subtract for ALL cells in B and C column. So something like this would be my goal:
  Ax = Bx - Cx
where x is in 1..number_of_rows.
 How could I accomplish this?
-Thanks again
Ach Send private email
Saturday, July 22, 2006
 
 
Just use the sum function before the list of cells you wish to add.

So basically it's C2 =(Sum A2 - Sum B2)

Check out the spacing and correct number of brackets
Stephen Jones Send private email
Saturday, July 22, 2006
 
 
Put the formula in the first cell in the column, so:

=A1-B1 or whatever.

Then select the cell with the formula in, put the mouse over the little box in the bottom right and click and drag the selection so it covers all relevant cells.

C2 will now be A2-B2, etc.
Graham Thorpe Send private email
Saturday, July 22, 2006
 
 
Ach,

- Type the formular =a1+b1 in cell c1.
- Click on c1 so that the cursor is on it.
- Press Ctrl-C to copy the cell.
- Now mark all cells c2-cx with the mouse (click on the first and drag down with pressed button).
- Press Ctrl-V to insert the formular into all the marked cells. Indices will be changed automatically.

You could mark the complete column c, but I doubt you will need THAT much rows...

Personally, I hate those "Click here, click tha, click tralalah" descriptions, because they don't answer the "Why, and what I'm actually doing there with what unexpected side-effects?" questions. First learn and understand the tools that you wish to use for your productive work -- else you will be in serious trouble when something important suddenly doesn't work anymore.
Secure
Saturday, July 22, 2006
 
 
To my mind, this discussion highlights some of the more deep seated problems with Excel (and all traditional spredsheets):

* There is no obvious way of creating column or row level formulae.
* Formatting data cannot be easily altered in the cell (sorry, a "conditional formatting dialog box" doesn't cut it).
* Very poor integration between the macro and the formulae languages.
* =if(if(if(etc...) is incredibly ugly, and hard to debug.

I wish there was a modern version of Lotus Improv!

Cheers,

Robert
Robert Smithson Send private email
Saturday, July 22, 2006
 
 
Thanks friends,
 Your help was really saved my time. I think Excel's way is not such intuitive at least for a rare user like me!
 -Thanks again
Ach Send private email
Saturday, July 22, 2006
 
 
"To my mind, this discussion highlights some of the more deep seated problems with Excel (and all traditional spredsheets):"

You might like Excel 2007 then.  It lets you use column names in formulas.  So in the example from this thread, in the first empty cell under the "Remain" column, you'd enter:
  =[Current Estimate] - [Elapsed]

Excel automatically applies this to the full column -- no need for the user to expand the formula to other cells manually. 

The conditional formatting features have also been improved considerably.  It seems like one of their design goals for Excel 2007 was to eliminate a lot of the cases where you had to drop to VBA in prior versions to do fancier conditional formatting.

And I've seen some examples of new error handling features that should cut down on some of the need for "=if(if(if(" though I haven't played around with this yet. 

I've been playing around with the beta for a week or two and older versions of Excel just feel dated and clunky to me now.  Strangely, the one thing that I don't like so far is the ribbon.
SomeBody Send private email
Saturday, July 22, 2006
 
 
"You might like Excel 2007 then.  It lets you use column names in formulas."

Excel 2003 does this.  It even uses a name rather than a cell reference if you navigate to put an already named cell in a formula.  You can also name a range and do a sum.  Nothing new there.
Cade Roux Send private email
Saturday, July 22, 2006
 
 
No, this is different.
SomeBody Send private email
Sunday, July 23, 2006
 
 
I'm impressed that you guys are good enough to go into such detail explaining this basic stuff to this guy.

I feel low, humble and insignificant in my contempt for such ignorance.

Ignore me.
Locutus of Borg Send private email
Monday, July 24, 2006
 
 
Two things you might find handy are:

1 Highlight a cell and some cells below and then press CTRL-D to "fill down". This will help you copy a formula quickly.

2 Look up "array formulas" in the help. A neater way of achieving the above.
John Ridout Send private email
Wednesday, July 26, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz