Conditional formula with an if statement

I am wanting to create a "envilope" budget sheet and have ran into a problem. 
What I am trying to do is make everything in Column B all negative unless I 
put a figure in Column A, which would make the Column B Positive.
Example:

Column A             Column B
Deposit                 Car
(nothing here)       $50 (this will show up negative)
$500                     $500 (This figure will show up positive)


I want to be able to add numbers in both column A and B but reflect the 
appropriate sign as an expense (with nothing being added into deposits) or as 
income, when I deposit something in that account.

So when I sum up this column, it will equal $450 left in the car envilope.  
I am struggling to figure out the conditional if statement, or am I looking 
at this wrong and should place this in Macros?

0
Utf
5/27/2010 10:04:43 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
677 Views

Similar Articles

[PageSpeed] 21

try this
=if(len(trim(a3))>0,a3,-50)

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Tyler2006" <Tyler2006@discussions.microsoft.com> wrote in message 
news:6446BA34-2B00-4A98-AB86-EBA2538B8345@microsoft.com...
>I am wanting to create a "envilope" budget sheet and have ran into a 
>problem.
> What I am trying to do is make everything in Column B all negative unless 
> I
> put a figure in Column A, which would make the Column B Positive.
> Example:
>
> Column A             Column B
> Deposit                 Car
> (nothing here)       $50 (this will show up negative)
> $500                     $500 (This figure will show up positive)
>
>
> I want to be able to add numbers in both column A and B but reflect the
> appropriate sign as an expense (with nothing being added into deposits) or 
> as
> income, when I deposit something in that account.
>
> So when I sum up this column, it will equal $450 left in the car envilope.
> I am struggling to figure out the conditional if statement, or am I 
> looking
> at this wrong and should place this in Macros?
> 

0
Don
5/27/2010 10:33:07 PM
Reply:

Similar Artilces:

how do i write formula for if condition in excel?
??? =IF(A1=10,"yes","no") ?? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Anjali" <Anjali@discussions.microsoft.com> wrote in message news:307D83C3-34BD-4E0D-AF7C-4E10AA77BB1A@microsoft.com... > =IF(question_is_asked_in_a_detailed-manner,"quality, quick reply received","the best you get is a guess") -- HTH Bob Phillips (replace somewhere in email address with gm...

find formula between worksheets
Worksheet 2 is a calendar with cell A1 labeled Oct. 2 , B1 labeled Oct.3, and so on. Worksheet 1 is a chart with column A labeled clients and column B labeled with the dates that someone met with the client. In worksheet2 I would like a formula that would pull Client names from column A on worksheet 1 (dates are in column b) into the calendar on worksheet2 in cell A2, for Oct. 2, and B2, Oct.3. For example if on worksheet1 if cell A1 said Kerry and cell B1 said Oct. 3, I would want a formula that would pull the client name Kerry into worksheet2 on cell B2 under the heading in cell B2 (...

Logical Test if statements with text--Excel 2002
I am trying to format a cell to check and see values for a range of other cells. If the value of the cell is a text letter (r, y, g, or c) I want the value to be returned to be whatever letter it was. How can I do thies? huh? >>be returned to be whatever letter it was -- Don Guillett SalesAid Software donaldb@281.com "Matt" <Matt@discussions.microsoft.com> wrote in message news:B0223028-7A92-4506-A369-C8D248897D65@microsoft.com... > I am trying to format a cell to check and see values for a range of other > cells. If the value of the cell is a text letter (r,...

copy formula+value
I have this 2 columns. I want to copy this two and paste them to a new row with exact the same formula, value and cellreference. =3D'Z:\F=F6retag\[filename1.xls]Best=E5nd'!$A$4 =3D'Z:\F=F6retag\[filename1.xls]Best=E5nd'!B18 The result: =3D'Z:\F=F6retag\[filename1.xls]Best=E5nd'!$A$4 =3D'Z:\F=F6retag\[filename1.xls]Best=E5nd'!B29 I want the B29 to be B18. I do understand that I could change B18 to $B$18 above but I don't want to do that. In some cases when I copy I want the B18 to be B19,B20,B21,... and this works fine. But how could I make a copy without ...

"IF Statements in Microsoft Excel
What do you do if you have a formula you need to enter that is more than 9 "IF" statements!!!! It won't allow more than 9 "IF statements in Excel and it is so frustrating!! http://www.cpearson.com/excel/nested.htm But why not post your problem description so we can help you with something more manageable then a 9-IF formula? -- Kind regards, Niek Otten "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:5C68F7EF-6825-44E6-BA78-2BD0C840EE3C@microsoft.com... > What do you do if you have a formula you need to enter that is more than 9 ...

Formula obscuring cell
I know this has been discussed before, but I can't seem to find it with Google. Situation: A long, multiline formula in one of the top rows of the worksheet. When I select that cell, the formula (from the formula bar) obscures the cell and makes dragging the cell impossible. I have found some ways to work around this. If I want to copy from B1:I1, I can select B1:I1 starting at I1 (rather than B1), and then use the <ctrl><R>. But I'm wondering if there is some other way so that long formulas won't interfere with the adjacent cell. --ron Hi Ron. I don't under...

Filter formula 05-19-10
HI I have a database with repeated informations as listed below and is increasing daily Nat Firm Name SPAIN VIAJES EL CORTE INGLES SPAIN VIAJES EL CORTE INGLES SPAIN VIAJES EL CORTE INGLES GREECE AMEX INTERNATIONAL- HOLLAND GERMANY AIRTOURS INTERNATIONAL-GERMAN ITALY DERTOUR GMBH & CO. KG UNITED KINGDOM INTERNATIONAL TRAVEL CONNE BELGIUM RAINBOW TRAVEL LTD I would like to have a formula which will list the firm name once when choosing the country. I don't want to perform it through a pivot table as I am using the sumproduct to add the qty and amount to have a com...

Mixing Formulae and Text in one cell
Can I write a sentence in an Excel cell which contains both text and a formula eg "The total therefore is SUM(A1:A5)" --- Message posted from http://www.ExcelForum.com/ like this ="the "&sum(a1:a5) -- Don Guillett SalesAid Software donaldb@281.com ":-) >" <<.1bd1s3@excelforum-nospam.com> wrote in message news:.1bd1s3@excelforum-nospam.com... > Can I write a sentence in an Excel cell which contains both text and a > formula eg "The total therefore is SUM(A1:A5)" > > > --- > Message posted from http://www.ExcelForum...

No Formulas are working!
I'm using a workbook that was setup by a different user. When I double click into ANY cell that contains a formula, the formula stops working and is then changed into text in the cell. When I try to enter a new formula (even VERY simple one's) the only thing that appears in the cell is the formula text! I have NO clue. I've made sure the autocalcuate is turned on. This is because the cell format is set on "text" and not on anything else (usual: standard") maybe you can change it for every cell: go into the menue format > Style and change this form...

Sorting of worksheet, half of columns are formulas referring to 1st worksheet...
I want to sort my worksheet alphabetically by last name. Half of my worksheet columns contain contact information that are formulas referring to another worksheet in the file. The other half is regular values of dates in different columns. (We use the file to track correspondence). I added 100 more rows to the worksheet.(about 300 total). Now I need to sort, but everything I try results in the rows getting switched around. Ex: Dr. Smith's dates aren't correct. They're on Dr. Smyth's row. Dr. Smith has Dr. Snyder's dates. Any ideas? Thanks. Angela -------------...

Cell formulas
Hi, I have a request that I can't seem to perform. I have two columns. COL1 contains user inputed values. COL2 has a formula. Basically what I want to do is: In COL2, I have the IF statement. If the statement = true, then I want a "happy face" to show up in the cell. If not, make the cell blank. Is this possible? TIA, Eric Hi see your post in excel.worksheet.functions P.S.: please don't multipost -- Regards Frank Kabel Frankfurt, Germany "Eric D." <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:1397501c44401$80f9d810$a501280a@phx.g...

Excel Formula Issue
I'm working on a AHT (average handle time) tracking sheet for a cal center. When calculating the average time however, I'm having a littl difficulty. I need to perform this caculation for rows 5-47. =((B5*D5)+(C5*E5))/SUM(B5:B48) I need to add the results of (B5*D5)+(C5*E5) + (B6*D6)+(C6*E6)..... and so on right to (B47*D47)+(C47*E47) and then divide by the sum o b5:b48 to give me an accurate average. The average function will no work in this situation due to the varied number of calls being take each day (b5) by each agent. If I take the average of each day' score, days with...

How do I reference a cell as a row number in a formula?
The formula I currently have is: =SQRT(SUM(F5280:F6240)/(N3-M3)) the row numbers 5280 and 6240 are values found in cells M3 and N3, respectively. However I need to copy this formula down a column, and I currently must go through and edit each formula to change those two values to the neighboring cells. i.e. that formula is in cell O3, and when copied to O4 the desired rows of column F to be referenced will be the numerical values in M4 and N4. =SQRT(SUM(F____:F____)/(N3-M3)) please fill in the blanks? =SQRT(SUM(INDIRECT("F"&M3&":F"&N3))/(N...

Need help with a formula 02-10-10
Here is my problem. I have to enter apt # on a spreadsheet for tracking material used. The problem is I am always finding duplicates. What I would like to be able to do is this. Enter the unit number ain a single cell. That will then move to a specific row in numeric order. If i enter a number that already been entered it will flag me. Does this make sense. Dear Ron, Is it should be highlited if you enter a Apt #, which is alredy entered? Then Conditional Format ("Format only unique or Duplicate values" in Excel 2007) may help you. Thanks & Regards "...

Formula Problem #6
I am trying to develop a worksheet where my sum will vary depending on whether another cell is greater than one number, greater than or less than another number and less than another number. A second multiplier depends on this criteria. An example would be if cell L20was <30 then C20 would be multiplied by cell L20. If it was <36> It would be multiplied by I20and if it was >36 it would be multiplied by cell F20. I am trying to enter the formula into cell M20. Hope this makes sense. Thanks in advance. On Jul 3, 10:25 pm, "JoeH" <jbh8...@yahoo.com> wrot...

Null value in multiple condition formula
Hi, I wonder if anybody out there could tell me how to do this? I am wanting to count the number of times in my worksheet a cell in column N is empty, WHEN the value of column C is "C" AND the value of column H (which contains dd/mm/yyyy data) is within the month of January. I am using the formula: =SUMPRODUCT((C2:C100="C")*(MONTH(H2:H100)=1)) to count those cells where Column C = "C" AND the month in column H = Jan (thanks to you wise and helpful people on the forum, I might add) but cannot work out how to do the null value in column N bit! Any help w...

Copy VLOOKUP Formula
I need to copy a vlookup formula down a specific column. However, the lookup_value needs to change from row to row when I copy the formula since it references the contents of the cells in column A. Also the contents of the cells in column A are text not numbers therefore the INDIRECT function is not working for me. Example A B 1 ROAA010A VLOOKUP("ROAA010A",'[Allocation Data.xls]Sheet1... 2 ROAB070A VLOOKUP("ROAB070A",'[Allocation Data.xls]Sheet1... 3 ROAM050A VLOOKUP("ROAM050A",'[Allocation Data.xls]Shee...

Convert string into formula
really needing this... let's say A1 = "SUM" B1 = "(" C1 = ")" D1 = "A3:C8" How can I put everything together and get the FORMULA =SUM(A3:C8) and its result INSTEAD of the STRING "=SUM(A3:C8)"? it's drivinhg me mad....I've tried CONCATENATE, &,... and all I get is the string... Thanks a lot Dario - Brazil Dario Maybe better ways but this works: Using your example, in E1 type the formula =CONCATENATE("=",A1,B1,D1,C1). Then, with cell E1 selected, do Edit Copy then Edit PasteSpecial and choose Values. Then hit F2...

Cease Excel's "formula making"
I am writing a research article and I'm using Excel to create my results tables. In order to indicate significant findings I need to include a three digit number followed by two * symbols. Currently when I try this Excel assumes I am trying to create a formula of some kind. How can I display -0.817** in a cell just as I've typed it here? Thanks for the help. Hi Format cell/column as text before. Arvi Laanemets "Buff" <Buff@discussions.microsoft.com> wrote in message news:17DB0E66-0420-49E9-81C1-E8C44FAC2AAB@microsoft.com... > I am writing a research article...

Spreadsheet Comparison, nested If statement
This works =IF(B4='[CSNA TEST DATA.xls]Sheet1'!$B$2,IF(F4='[CSNA TEST DATA.xls]Sheet1'!$E$2,"yes","check")) This doesn’t work =IF(B2='[CSNA TEST DATA.xls]Sheet1'!$B$2:$B$55,IF(F2='[CSNA TEST DATA.xls]Sheet1'!$E$2:$E$55,"yes","check")) The difference is that I have changed from looking at specific cells to looking at ranges of cells. What I am trying to do is compare my spreadsheet with a customer’s spreadsheet, both 3,700 lines long, and if: 1. The serial number data in a given cell in my spreadsheet matches the seria...

Excel 2007 Bug? Conditional Formatting
My client has the following situation: Sheet1 A1 = 5% A2 = $3.00 A3 = (a list option to determine if they want to use the % or the $) On Sheet2 the formulas use the value of A3 in an if condition to apply either A1 or A2 to calcuations. This all works fine. On Sheet2 they have a cell that simply reports the A1 or A2 value being used in the calculations. The conditional formatting I wrote compares the value in that cell to the original A1/A2 cells and sets the formatting to either currency or % as appropriate. At first blush it seems to work but as I change the selected item in A3 and fli...

Increase by % Formula
Hello, I have a row of #'s, in the row below I would like to increase each cell's number by 3.5%, in the next row 4%, etc. What is the formula? Example of what I'm doing: In Cell A1 is 60,000 In Cell B1 I put the following formula: =A1*3.5% (Answer comes out to 2,100, which is correct, but then I want ithe answer to be 62,100 - it's not adding the 2,100 to the 6,000 for the answer). Please help! Thank you. "Trying Hard" <TryingHard@discussions.microsoft.com> wrote in message news:7E9E886F-7405-4BFD-8467-35C18ABDF891@microsoft.com... > Hello, > ...

IF THEN FORMULA #4
this is my formula for my excel project =IF(OR(D13="Necking",D13="Caution"),"Caution"," ") This means if the word Necking appears in my table, then in the cell next to it so should the word Caution. Now i also want to include the word Fracture in the formula, so when my table shows the word Fracture, the word Caution will appear in the cell next to it as well. is there anyway i can simply put the word "Fracture" into the formula? try: =IF(OR(D13="Necking",D13="Caution",d13="Fracture"),"Caution",&qu...

Printing formulas in a cell
I want to print the formulas in each cell of a work sheet. How is this done? See here for one way:- http://www.cpearson.com/excel/excelM.htm#PrintFormulas or you could try Tools / Options / View / Formulas, but it's not as friendly. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "BDC...

Need to hide linked rows based on condition
I am creating a template to be used for calculating and preparing sales proposal. Various parts of the proposal are created on differen worksheets within the workbook, and are formatted to allow a certai number of entries, say 50. The rows contain info such as quantity description, and price. Usually, all 50 entries won't be needed, bu sometimes they will. Once the calculations have been performed, I need to compile th proposal worksheet. I had planned to link the data from the variou worksheets over to the proposal worksheet. However, I don't want t include rows that have no qua...