Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / June 2005

Tip: Looking for answers? Try searching our database.

HELP with formula

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Joe S. - 24 Jun 2005 02:11 GMT
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.

Signature

-----
Joe S.

--

-----

Earl Kiosterud - 24 Jun 2005 03:04 GMT
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.

Signature

Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

>I am building a house, acting as my own general contractor.  I am setting
>up
[quoted text clipped - 28 lines]
>
> Thanks in advance.
Dick Smith - 27 Jun 2005 02:47 GMT
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

> Joe,
>
[quoted text clipped - 48 lines]
>>
>> Thanks in advance.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.