#### HELP with formula #9

```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?

--

-----
Joe S.

--

-----

```
 0
6/24/2005 1:11:29 AM
excel 39879 articles. 2 followers.

2 Replies
449 Views

Similar Articles

[PageSpeed] 4

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

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

"Joe S." <nobody@nowhere.net> wrote in message
news:d9fmk1026rh@news1.newsguy.com...
>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?
>
>
> --
>
> -----
> Joe S.
>
>
> --
>
> -----
>
>

```
 0
nothanks4548 (968)
6/24/2005 2:04:05 AM
```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

"Earl Kiosterud" <nothanks@nospam.com> wrote in message
news:O8OLCEGeFHA.2124@TK2MSFTNGP14.phx.gbl...
> 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.
>
> --
> Earl Kiosterud
> www.smokeylake.com/
> -------------------------------------------
>
> "Joe S." <nobody@nowhere.net> wrote in message
> news:d9fmk1026rh@news1.newsguy.com...
>>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?
>>
>>
>> --
>>
>> -----
>> Joe S.
>>
>>
>> --
>>
>> -----
>>
>>
>
>

```
 0
dicksmith (2)
6/27/2005 1:47:57 AM

Similar Artilces:

Newbie Help. Is this possible?
Hello, I am just getting into Excel and would like some help very much. I a basically trying to make a spreadsheet for a restuarant that woul specify how many servers would be the floor and their sidework and tha ties into the server schedule. Problem is that different nights have different number of servers on. So my question is? Do I need to make spreadsheet for all the varients of servers (say 4-10 servers) or i there some way I can input a number like 6 and the spreadsheet wil modify itself automatically? Thanks so much -- taltos ------------------------------------------------------...

need help related to mail
Hi everybody, I having problem in my organization that when internal users send mail to internal users sometimes it not reach to recipient and also not given notice to sender that mail delivery fail. Why its happen I don't know? Can anyone tell me how to troubleshoot and how to find out why its happen? But one more thing that in outlook its shows in send item box. then where is mail gone? Anybody tell m or guide me. Thanking you. Yours truly, Vicky Sam. You have to provide a little more information about your architecture before anyone will be able to help you. -- regards, ...

Inserting Lines Makes Formula Inactive
I downloaded a Microsoft template called General Ledger (Green, multi-sheet/one per account code plus YTD Budget Summary and Monthly Expenses sheets). The workbook uses the name manager and some other field names that I can't find definitions for. The document worked fabulously until 1/1/10. Now, when I add a line to any of the individual account sheets, the linked data result for the formulas on the Monthly Expenses table disappear. The formulas are there but the result, and the "Accounting" format just go away. No matter what I do, the Monthly Expense...

Help With Simple Combobox Programming
I have a combo box on sheet1. When the user clicks on the combo box the first time ( it gets focus ) It should add/load all items from sheet2 column A. Now when the user selects from the combobox that item should be copied/placed on sheet1.A5 similarly the next item selected in the combo box should be placed below A6 and so on How can this be accomplished with code thx Hi, Use code like that : Private Sub cboIn_Click() Dim intR As Integer intR = Range("a4").CurrentRegion.Rows.Count Range("a4").Offset(intR, 0).Value = cboIn.Value End Sub Priv...

Data analysis help needed
Hi, I am trying to analyse data = transaction of a big retail store. I need to segment the articles sold and actually I am trying to build a database that will allow me to classify and identify all the articles sold. My problem is how to build this database which should be built only once and then what are the most apropriate tools to be used for data analysis Thanks in advance for your help ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Emmanuel, Big Retail Store ...

Formulae #3
Hi All, I need to reduce a figure by 5% and increase one by £1.50 how do I put this into a forulae. Thanks, kfh If I understand your questions correctly: =YourFigure*.95 for 95% of a number. Example =100*.95 will return 95. For the second part you want to add 1.50 pounds? =YourNumber+1.5 Example: =3+1.5 will return 4.5 The pound symbol should probably be aplied as formatting rather than typed. Format | Cells | Number. Pick either Currency or Accounting and set the appropriate monetary symbol. tj "k f h" wrote: > > Hi All, > > I need to reduce a figure b...

Sumproduct formula problems
Hi All I'm having trouble with a couple of formulas and I think I've written them wrong?? First Formula: In cell CB183 the formula looks at range(\$B\$37:\$H\$165) to finds any occurrences of "production"or "installation" or "non commission" within any STRING of text (hence the "*XX*") and then adds those rows in range(CB37:CM165) that match. {=SUMPRODUCT(--ISNUMBER(SEARCH({"*production*";"*installation*";"*non commission*"},\$B\$37:\$H\$165)))*CB37:CM165)} The formula works when I type "production&...

Help deleting a page from CPropertySheet
I have a CPropertySheet derived class called CAddTenantSheet. The following code creates the sheet fine. CAddTenantSheet sheet; CAddTenantPage1 m_page1; CAddTenantPage2 m_page2; sheet.AddPage(&m_page1); sheet.AddPage(&m_page2); sheet.SetWizardMode(); sheet.DoModal(); I would like to know how to delete m_page2 during the call to m_page1. Once I get the code working, I will use it to delete sheets that aren't needed, based on yes/no responses on earlier sheets. At the moment, may OnInitDialog() function in CAddTenantPage1 is BOOL CAddTenantPage1::OnInitDialog() { CPropertyPage:...

MSChart Row Labels help!
I have a graph "ChtLive" that updates constantly based on values coming in from PLC. The graph updates fine and displays my values on the screen perfectly. However, I am having trouble with the row labels (rows 1-20) updating as current time in VB6. Ex: This function SHOULD do the following: First time around, row label 20 holds current time. frmMain.chtLive.Row = 20 frmMain.chtLive.RowLabel = Format(Now(), "ss") 'seconds on the minute Second time the function in question is called, row 19 label takes value from row 20 label, and row 20...

Practice Mode Locked Me OUT Need Help
Practice mode has locked me out of the system i can not log in or se my system how can i get out of practice mode I am having the same problem. Any work around for this? Icant get through the practice mode wizard because it cannot connect to the database. My POS is SOL until I get this figured out. Any way to "Start over" with the Casio QT-8000 system? --Mike "solor" wrote: > Practice mode has locked me out of the system i can not log in or se my system > > how can i get out of practice mode Close POS first. Next go to the product folder C:\Program File...

Formula Question #10
I am setting up a formula that can have a possible 3 results. I was trying to use an "If, Then" statement but that will only cover two of the results. I have a cell that has a drop down menu with four possible coices: blank, "fixed", "rescheduled" and "pending." The problem I have is that I used the formula: =IF(I34="fixed", 3, 1) What I want is to assign a point value to each choice. Fixed = 3 Pending = 1 Rescheduled = 1 Blank = 0 The problem is that with the formula I have a blank cell will still give one point. Any suggestions? -- l...

Return one instance using formula or filter
Is it possible to filter based on the following example: colA Acalypha Acanthocalycium Acantholimon Acanthopanax Acanthus Acca Acer Acer Acer Acer Acer Aceras Aceriphyllum Achillea Achillea Achimenes result after filtering; Acalypha Acanthocalycium Acantholimon Acanthopanax Acanthus Acca Acer Aceras Aceriphyllum Achillea Achimenes Thankyou if you can help. Pat check advanced filter unique values in help "Pat" wrote: > Is it possible to filter based on the following example: > > colA > > Acalypha > Acanthocalycium > Acantholimon > Acanthopanax >...

Pivot Table--Need Help with data
Dear users: I am trying to create a pivot table (and possibly a pivot chart) that lists my daily expenses. I have 3 categories for expenses: cash, debit, and charge. (I don't really write checks--they would get listed as debit). I also have 3 categories which are a running =SUM of the first 3 categories. So I have 6 columns total with dollar amounts in them and then I have 2 additional columns that list what type of purchase and sub-purchase category it is. (The first 3 columns are date, purchase, and location. The next 3 are cash, debit, charge, the next three are the runni...

Upgrade to Great Plains Dynamics 9.0 error, Column PRODID not foun
Hello, I am attempting a test upgrade from Great Plains 7.5 to Dynamics 9.0 and I get an error during the system tables upgrade portion, right at the 'Load Additional Required Data' step. The error message is: The following SQL statement produced an error: delete from DU000010 where PRODID=258 followed by: ERROR [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'PRODID'. I checked the DU000010 table and the column does NOT exist. I also tried upgrading from 7.5 to 8.0, which worked, but when I then try to upgrade that 8.0 to 9.0, I still get the sa...

Copy formulas via Code
I have a range with formulas on sheet2 called "database". I want to get this range and transfer it's contents to sheet1 called "work", but I want the formulas to update themselves to this new location, and not show the same exact formula that was found in the original range. I had tried : sheets("work").range("a1:b10").formula = sheets("database").range("d1:e10").formula with the following results Cells d1 to d10 on sheet database contain X values, and cells e1 to e10 contain the formula =d1*d1, =d2*d2, and so forth. Wha...

HELP with MS Office 2004 registration (preferences?)
Awhile back, I assisted a friend who was having trouble with her Mac by backing up the computer when she had to send it in for repairs. She owned MS Office 2004. I owned an earlier version. So, when I tried to open office documents it then opened them in her Office 2004. When her computer returned and we made sure all was intact, I deleted all her files and programs. Later still, I purchased my own copy of Office 2004 and registered it. However, since that time when the application launches it shows MY name, but HER company which was shown on her version that I had backed up for her. Does an...

Help with date automatic date problem (probably very simple)...
Hello Could anyone offer any help with this problem, it is probably very ver simple, but I am a beginner to Excel. I would like a date to be entered into Cell D1, when a number '1' i entered into *either* A1, B1, *OR* C1. I am using the following: =IF(OR(A1=1, B1=1, C1=1),TODAY(),"") Now this works, however it enters the current system date at all times and changes day to day. What I need it to do is to enter and store th date that the '1' is entered into the first 3 columns. So if the 1 i entered on 01/01/03, that date will stay forever. Does that make sense t...

Help!! i need to know what my post office, mailbox & password are!
Im just starting to use outlook & i havent got a clue what my post office, mailbox & password are!!! A friend of mine set it up & unfortunately he didnt write these details down for me :( Can anyone help? You should call your ISP to get the information. It's probably looking for your ISP username OR email address, password and smtp/pop addresses so that you can send and receive email. -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ Outlook & Exchange Solutions Center: http://www.slipstick.com "babyred" wrote...

office 2007 will not load, missing file SKU011.CAB...Help!
can not install MS Office Standard 2007, error message states that file SKU011.CAB is missing. Previously did install on lap top (windows 7). Will not install on desk top (windows XP). Can someone help? Office 2007 requires at least XP with SP2. If that's not the problem, then take a look here: http://support.microsoft.com/kb/818234/en-us -- Herb Tyson MS MVP Author of the Word Bible Blog: http://word.herbtyson.com Web: http://www.herbtyson.com "Hoot" <Hoot@discussions.microsoft.com> wrote in message news:98013A41-0F20-4CA6-A31D-68C57B3D5967@mic...

exchange 2k3 help
i did not install our existing exchange 2k3 software so i dont know what format our email addresses woud be. the @?????.com... also where can i manage or configure user email accounts. To manage email accounts, use the regular "Active Directory Users and Computers" MMC plug-in on the Windows server which hosts the Exchange server. Right-click on any user, select "Properties", and there should be several Exchange-related tabs. Take a look at the "Email Addresses" tab for existing users to see what format your current email addresses are using. "excha...

help: Font question
Hi all. I've recently converted an application to unicode, so it can display chinese characters. and it works fine. However, when i install on a friend's computer, the chinese symbols appear as squares. how do i find out what font i need to install on his computer? at present, i'm not assigning any fonts to a control. it's just happened to work. i know i should be, and i will do. but right now as a quick-short-term-fix i need some way of finding out which font my computer is using to display the chinese text, so i can get the chinese text displaying on a friend's computer...

More help needed with a macro
Hi, I wonder if anyone would be kind to explain to me how I would make a macro do the following: I need to make a keyboard shortcut of Ctrl A to insert a new line on Row C12 whenever a new line is required. My formula in D5 is the average of the 5 most recent values from c12.c16, my formula in D3 is the total average of all values c12..c#. So my macro has to insert the new row but still fix the formulas and labels and prevent anyone from changing anything else expect the information they are inputting in Row12. So the macro needs to unprotect the worksheet, do all the required tasks then re-...

How to change a color of a cell using a condition formula
I would like to change the color of a cell automatically using a condition formula.....is this possible? Yes, go to Format | Conditional Formatting. -- Regards Juan Pablo Gonz�lez "Carlos" <Carlos@discussions.microsoft.com> wrote in message news:1330539A-8B3B-44DE-AF70-1E686D172379@microsoft.com... > I would like to change the color of a cell automatically using a condition > formula.....is this possible? Hi see 'Format - Conditional Format' -- Regards Frank Kabel Frankfurt, Germany Carlos wrote: > I would like to change the color of a cell automat...

Formula help #67
I want to drag the formula that exists in d3 (i.e. =d2/d130) to a column of rows. The problem is that by dragging the formula, it increments the d130 to d131, d132, etc. How can I make the formula stop incrementing the second variable? \$d\$130 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "sdmccabe" <sdmccabe@discussions.microsoft.com> wrote in message news:77FE3F27-B67C-4057-855E-C7F2B84FC519@microsoft.com... >I want to drag the formula that exists in d3 (i.e. =d2/d130) to a column of > rows. The problem is that by dragging the formula, it inc...

Rounding formula won't copy to other cells in column
I have a formula in cell G2 that reads: =round(F2,0). I'm using this to round the value in cell F2 and remove decimal places. The entire "F" column has values I want to round, and I am unable to "fill" the formula so that I can get the rounded values of each record in the range. What should I be looking for? So what *does* happen when you copy the formula in G2 to other cells in the column? Check to make sure Calculation is set to Automatic (Tools/Options/Calculation). In article <1933354D-C4AE-4051-87BD-29AF728A57DA@microsoft.com>, LindaO <Linda...