 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008"IF" Function
Hi, I am using Excel MSOffice Xp (2002) and would appreciate help with the "if" function. I am trying to create a Retirement formula and running into all sorts of errors and will try and explain the scenario. In the most simple context, I am basically trying to create a formula that would subtracts EXPENSES from ASSETS on annual basis and adjust ASSETS to account for the annual expenses. Let me give you a scenario /example, and assume that Assets are $5,000, Income is $100 and Expenses are $200. I am trying to create a "if" formula that would be +100 (income) less 200 (expenses) and since this would be a negative number, I would go to assets and subtract $100 so the formula would be "if" income - expenses is positive the OK but if income - expenses is negative, subtract that amount from assets and reduce assets accordingly. This would be annualized so in effect it would be current year Income less Current year Expenses if this is a positive number, it would be added to the following years assets. If this is negative, then it is subtracted from current year Assets and the following years assets would be reduced by this amount. The actual spreadsheet is obviously a little more complicated, I several sources of income and several expense categories with Inflation Factors but am stuck creating a "IF" formula. Any help would be appreciated. Thanks, Bob Hi Bob, try =IF(Income-Expense>=0,"",Income-Expense+Asset) and replace the words Income etc by the relevant cells > Hi, I am using Excel MSOffice Xp (2002) and would appreciate help with the > "if" function. I am trying to create a Retirement formula and running into [quoted text clipped - 16 lines] > categories with Inflation Factors but am stuck creating a "IF" formula. Any > help would be appreciated. Thanks, Bob Thanks Chris, =IF(Income-Expense>=0,"",Income-Expense+Asset) appears to work, thank you. The formula calculates annual Networth, which is what I am trying to determine but would really be great (and I am not sure if it can be done with Excel) is to also SUBTRACT the amount that was deducted, from the Assets line? To try and simply (rephrase) what I would like to do, the formula has taken care of the Networth line which now "borrows" the shortfall from Assets and calculates the remaining (current) Networth positions. which is great. What it doesn't do is subtract the "shortfall" (that was taken from Assets), from the Asset line i.e. reflecting the current string of Assets. I believe that I could do this by staring a new row which will subtract Current Networth from last years Assets and my question is, is it possible, to incorporated this in the "IF' Formula (i.e. reduce assets accordingly)? Thank you again for your help Jeff, I don't know what I was doing wrong but everything that I tried came back with an error so thank you for pointing me in the right direction. Bob Bob Hi Chris, I realize that my follow-up question was not a very smart question but I do have another question about the "IF" function. I have used the "IF" function in the spreadsheet and later on, created another "IF" formula that included the cell of the 1st "IF" formula and received an error message so I am assuming that you can not refer to a previous "IF" cell with another "IF" formula? Is this true or did I make an error in creating the formula "if" formula and two "ifs" are doable? Thanks, Bob IF() formulas can reference other cells with IF() formulas in them. For instance: A1: =IF(INT(RAND()*2), "One", "Zero") A2: =IF(A1="Zero","One","Zero") > Hi Chris, I realize that my follow-up question was not a very smart question > but I do have another question about the "IF" function. I have used the "IF" [quoted text clipped - 5 lines] > Thanks, > Bob Hey thanks, the "IF and" was very helpful but like everything else, one "problem" solved lead to another problem so I am back with another request for help. This formula: =IF(AND(B39=0,B13-B44>0),B13,0) did not do what I wanted it to do. What I am trying to do is create a formula that says, if the value in B39 is positive, use the value in B13 less B44, and if this value is positive, enter B13 less B44, if it is negative, then enter "0". What I am getting is a POSITIVE number when B13-B44 is <zero (the result should have been a negative number), what I would like is the cell is for it to display "a zero" when a negative result is present. Thank you in advance for your help, Bob >Hey thanks, the "IF and" was very helpful but like everything else, one >"problem" solved lead to another problem so I am back with another request [quoted text clipped - 13 lines] >Thank you in advance for your help, >Bob You also have to specify what result you want if the value in B39 isn't positive. Try this: =IF(B39>0,IF(B13-B44>0, B13-B44, 0), 9999999) replace 9999999 with the value you expect if B39 is not positive. Hope this helps / Lars-Åke Thank you for your help Lars, I appreciate you trying to straighten me out since it appears that I need a LOT of help! Your advised me to "specify what result you want if the value in B39 isn't positive" and I (slightly) modified the formula as follows: =IF(B39>0,IF(B13-B44>0,B13-B44,0),0) but the results now show a POSITIVE value in cell B40 (i.e. the cell with the formula) although cell B39 has a positive value. I might have not stated "the problem" incorrectly so let me try rephrasing it, what I am trying to do in cell B40 is calculate a "zero" when the value in cell B39 is positive, calculate B13-B44 when the value in cell B39 is "zero" and calculate a "zero" in cell B40 when the value of B13-B44 is negative. Thank you again for your help Lars, Bob >Thank you for your help Lars, I appreciate you trying to straighten me out >since it appears that I need a LOT of help! Your advised me to "specify what [quoted text clipped - 14 lines] >Thank you again for your help Lars, > Bob This time you have not specified what you want when B39 is negative. Maybe that will never happen. In that case, try the following: =IF(B39>0,0,IF(B13-B44>0,B13-B44,0)) This table shows what happens in the 9 different cases: B39>0 and B13<B44 gives 0 B39>0 and B13=B44 gives 0 B39>0 and B12>B44 gives 0 B39=0 and B13<B44 gives 0 B39=0 and B13=B44 gives 0 B39=0 and B13>B44 gives B13-B44 B39<0 and B13<B44 gives 0 B39<0 and B13=B44 gives 0 B39<0 and B13>B44 gives B13-B44 Is this what you want? If not, provide a similar table covering ALL possible combinations that should influence the result. Lars-Åke >>Thank you for your help Lars, I appreciate you trying to straighten me out >>since it appears that I need a LOT of help! Your advised me to "specify what [quoted text clipped - 39 lines] > >Lars-Åke The B12 is a typo, should be B13 there as well. |
Reply to this Message |
 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.