RSS Feed

The Joel on Software Discussion Group (CLOSED)

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

You just write the formula in as the cell value. This is very basic excel stuff.

" 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

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

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

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.

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.

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

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

"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.

"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.

No, this is different.

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.

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.