HELP with formula

[Joe S.]

I am building a house, acting as my own general contractor. I am setting up
an Excel spreadsheet to track actual cost versus estimate so I'll know when
I'm running out of money.

I need to set up the spreadsheet with each cost item -- grading, fill,
footers, framing lumber, shingles, etc., etc. I then need to put an entry
for ESTIMATE, ACTUAL, DIFFERENCE. The DIFFERENCE column should conform to
these rules:
-- If ESTIMATE and ACTUAL are equal, nothing should appear in the DIFFERENCE
cell.
-- If ESTIMATE is greater than ACTUAL (that is, if I budgeted more than it
cost), then a positive dollar figure should show in DIFFERENCE.
-- If ESTIMATE is less than ACTUAL (if it cost more than I estimated), then
a negative number should appear in the DIFFERENCE cell (in parens on the
printout, in red on the screen).

For example:
Item Estimate Actual Difference
Fill $500 $475 $25
Form labor $800 $1,000 -$200
Plans $1,200 $1,200
(At this point I will show a total difference of -$175, thus, I will know
that I am $175 over budget.

I can't figure out the formula for the DIFFERENCE cells -- can someone help?

Thanks in advance.

 

[Earl Kiosterud]

Joe,

If ESTIMATE is column A, starting in A2 (row 1 is usually for headings), and
ACTUAL is in B2 and down, then in C2 you'd put

=A2 - B2

Copy this down the column with the Fill Handle (little block in the lower
right corner). If B2 is greater than A2, you'll get a negative value, as
you want. Then you can total any of the columns with something like:

=SUM(A2:A100)
=SUM(B2:B100) etc.

Generally the Autosum button is used to create such a formula, but you can
type it.

 

[Dick Smith]

Then to continue with Earl's example, to set those "Difference" cells to
display in red when they're negative, select them all, then click
Format|Conditional Formatting. Then fill the pulldown boxes to read "Cell
Value Is" "less than" "0", then click Format to set Font|Color to Red.

If you think about it right after you define C2, then do Conditional
Formatting on C2 by itself, when you copy the formula down the column you'll
also copy the Conditional Formatting as well.

Dick