Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"IF" Function
Hi Bob,
try =IF(Income-Expense=0,"",Income-Expense+Asset) and replace the words Income etc by the relevant cells "Boblink" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"IF" Function (expanded)
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can a cell with "IF" Function be referred to by another "IF" formu
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can a cell with "IF" Function be referred to by another "IF" formu
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") In article , Boblink wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with "IF" Function
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-B440),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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with "IF" Function
On Sat, 17 May 2008 08:05:00 -0700, Boblink
wrote: 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-B440),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 You also have to specify what result you want if the value in B39 isn't positive. Try this: =IF(B390,IF(B13-B440, B13-B44, 0), 9999999) replace 9999999 with the value you expect if B39 is not positive. Hope this helps / Lars-Ĺke |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=IF(B390,IF(B13-B440,B13-B44,0),0)
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(B390,IF(B13-B440,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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=IF(B390,IF(B13-B440,B13-B44,0),0)
On Sat, 17 May 2008 09:41:00 -0700, Boblink
wrote: 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(B390,IF(B13-B440,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 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(B390,0,IF(B13-B440,B13-B44,0)) This table shows what happens in the 9 different cases: B390 and B13<B44 gives 0 B390 and B13=B44 gives 0 B390 and B12B44 gives 0 B39=0 and B13<B44 gives 0 B39=0 and B13=B44 gives 0 B39=0 and B13B44 gives B13-B44 B39<0 and B13<B44 gives 0 B39<0 and B13=B44 gives 0 B39<0 and B13B44 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=IF(B390,IF(B13-B440,B13-B44,0),0)
On Sat, 17 May 2008 17:08:25 GMT, Lars-Ĺke Aspelin
wrote: On Sat, 17 May 2008 09:41:00 -0700, Boblink wrote: 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(B390,IF(B13-B440,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 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(B390,0,IF(B13-B440,B13-B44,0)) This table shows what happens in the 9 different cases: B390 and B13<B44 gives 0 B390 and B13=B44 gives 0 B390 and B12B44 gives 0 B39=0 and B13<B44 gives 0 B39=0 and B13=B44 gives 0 B39=0 and B13B44 gives B13-B44 B39<0 and B13<B44 gives 0 B39<0 and B13=B44 gives 0 B39<0 and B13B44 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 The B12 is a typo, should be B13 there as well. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=IF(B390,IF(B13-B440,B13-B44,0),0)
Hi. On the part of your formula where you use:
=IF(B13-B440,B13-B44,0) Consider the following idea... =MAX(B13-B44,0) -- HTH :) Dana DeLouis "Boblink" wrote in message ... 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(B390,IF(B13-B440,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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Follow-up If and MAX function questions?
"Thank you for your help guys, fyi I am trying to create a Retirement Spreadsheet so I have replicated the formulas across columns "B" thru "AL" (which represent years 2008-2044), and think that the problem is NOT with the formula in row 40 but with a formula in ROW 13 (which is one of the cells that the formula in row 40 references). The cells in row 13 currently have a value of +99, and I tried this formula: =IF(AND(B110,99-B440),99,B44-99) but it does not come up with the results that I am looking for. What I want is: - if the value in cell B11 is positive, then the value +99 should appear in cell B13 - if the value of cell B11 is negative, then the next calculation should be +99 less the value in cell B44. If this result in a positive number, than the POSITIVE value only should appear in cell B13 - if this result in a positive number, then that number should appear in cell B13 - If the result is a negative number, then a "zero" should be appear in cell B13 The formula that I have (=IF(AND(B110,99-B440),99,B44-99) displays +99 for B13 when the value in cell B11 is positive (which is what I want), but it displays a negative number when the value in B11 is "zero". What I would like is, when the value in cell B11 is "zero", the value in cell B13 should be +99-B44 but only until the value is POSITIVE, a positive number ONLY should be displayed in B13. Once the value in B11 is negative and the result of €ś+99 €“B44€ť has become negative, then "zero" should be displayed in cell B13. Example: - B11=125 - B44= 80 - C11=0 - C44=120 - D11=0 - D44=150 B13 - the calculations for cell B13 should be: "+99" , because there is a positive value <+125 in cell B11 C13 - the calculations for cell C13 should be: "+99€ť, because 99-120= -21, therefore only a positive value, "+99" should be displayed / should appear, in cell C13 D13 - the calculations for cell D13 should be: "zero", because the results in C13 resulted in a negative number (i.e. the only number that should appear is "+99" or "a POSITIVE number only when +99 is subtracted from cell 44 or "zero" when the calculation of "+99 less the value in cell 44" is negative) Dana, as you can probably tell, I am just familiarizing myself with the €śIF€ť function and have NOT used the €śMAX€ť function. From your example, where you suggested: =MAX(B13-B44,0) is to be used instead of =IF(B13-B440,B13-B44,0) It certainly would make sense to use the MAX function (i.e. smaller formula = less chance for errors), if it gets the same results. I have not located where I used: =IF(B13-B440,B13-B44,0) but being that this is a dynamic exercise, it might have been in a earlier iteration nevertheless, when I tried to incorporate it into the correct SpreadSheet, it did not result in the same calculations so I must have misunderstood and / or, NOT followed what you suggested. Lars, I assume that you are Swedish? What part of Sweden do you live in? My wife grew up in Malma. Thank you again for your help guys, Bob |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Follow-up If and MAX function questions?
Example: - B11=125 - B44= 80 - C11=0 - C44=120 - D11=0 - D44=150 B13 - the calculations for cell B13 should be: "+99" , because there is a positive value <+125 in cell B11 C13 - the calculations for cell C13 should be: "+99”, because 99-120= -21, therefore only a positive value, "+99" should be displayed / should appear, in cell C13 D13 - the calculations for cell D13 should be: "zero", because the results in C13 resulted in a negative number (i.e. the only number that should appear is "+99" or "a POSITIVE number only when +99 is subtracted from cell 44 or "zero" when the calculation of "+99 less the value in cell 44" is negative) Please explain again the principle difference between the examples in columns C and D. Should the result in column D be dependent on the result in column C as you write, or is that just a typo? Lars-Ĺke |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification of the calculations for cells "C and "D"
Hello Lars, I apologize for the confusion and will try to explain the
scenarios a little better. The calculations for B13 should result in +99 because there is a positive value in cell B11 (125-80=45), so +99 is entered in cell B13. When the value in cell 11 is positive, +99 (and only up to +99) should be entered as the value for cell 13. The calculation for C13 should also be +99 because in this case, the value in cell B11 is -120 (0-120= -120), but cell B13 can not be negative and the maximum allowable value is +99, so +99 is entered in cell B13. If the value in cell 11 is positive, +99 should be entered as the value for cell 13. If the value of cell 11 is <+99, than value in cell 11 is added to +99 and the positive integer, up to +99 is entered in cell 13. If the value in cell 11 is +99, then +99 is entered in cell 13 and for all subsequent years / following years, the entry for cells 13 will be "zero". Once the +99 is used, it is no longer available and "zero" will be the entry for cell 13. The calculation for D13 cells should be "zero" because there is a negative value in cell D11 and the " +99" has been already used the previous year, so "zero" is the entry for cells 13 from this point forward. I don't know if this makes sense to you Lars, if not, please advise and I will try and send a different scenario / example. I also have an unrelated question that you may or may not be able to help me with Lars. I am using a Vista system and when I tried to post a REPLY in the Newsgroup form, nothing happened. My screen went blank (refreshes) for a second or so and then the same page (with all the previous post) appeared, I was not presented with the REPLY window. I had to go to another (WindowsXp) system to post this note, I was unable to get the Reply screen with the Vista system. Have you heard of any other people with this problem of not being able to access the REPLY screen? It certainly sounds like it is Vista related problem, most likely with a security feature in Vista. If you have any thoughts on this, I would appreciate hearing what you might suggest to allow me to access the REPLY screen with the Vista system Thank you again for your ALL help Lards, Bob Example: - B11=125 - B44= 80 - C11=0 - C44=120 - D11=0 - D44=150 B13 - the calculations for cell B13 should be: "+99" , because there is a positive value <+125 in cell B11 C13 - the calculations for cell C13 should be: "+99€ť, because 99-120= -21, therefore only a positive value, "+99" should be displayed / should appear, in cell C13 D13 - the calculations for cell D13 should be: "zero", because the results in C13 resulted in a negative number (i.e. the only number that should appear is "+99" or "a POSITIVE number only when +99 is subtracted from cell 44 or "zero" when the calculation of "+99 less the value in cell 44" is negative) Please explain again the principle difference between the examples in columns C and D. Should the result in column D be dependent on the result in column C as you write, or is that just a typo? Lars-Ă…ke |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification of the calculations for cells "C and "D"
Sorry, but I am totally lost with your descriptions.
You keep changing the examples all the time. Or have a lot of typos. A few lines below you say that the value in cell B11 is 45, a bit further down you say that the value in cell B11 is -120. And in a previous post, where you presented this example, you said that the value in cell B11 is 125. Which of these are the correct one? You write that C13 should be +99 for some strange reason involving B11 and B13 that I can't follow, but it ends up that also B13 should be +99. A bit further down you write that once +99 has been entered in a cell on row 13 all subsequent entries on row 13 should be "zero". To me this is contradictory and makes no sense at all. Please try to explain the problem more accurately and consistantly, and it will increase the chances to get the help you ask for. Try to describe, for one cell at a time, not all in the same sentence, on which other cells this cells value will be dependent. For example: The value in a cell on row 11, - will that be dependant on other cells in the same column (year) if so, state which cells and which dependencies one at a time - will that be dependant on any cells in any other column (year) if so, state which cells and which dependencies one at a time The value in a cell on row 13, - will that be dependant on other cells in the same column (year) if so, state which cells and which dependencies one at a time - will that be dependant on any cells in any other column (year) if so, state which cells and which dependencies one at a time Lars-Ĺke On Sun, 18 May 2008 05:53:00 -0700, Boblink wrote: Hello Lars, I apologize for the confusion and will try to explain the scenarios a little better. The calculations for B13 should result in +99 because there is a positive value in cell B11 (125-80=45), so +99 is entered in cell B13. When the value in cell 11 is positive, +99 (and only up to +99) should be entered as the value for cell 13. The calculation for C13 should also be +99 because in this case, the value in cell B11 is -120 (0-120= -120), but cell B13 can not be negative and the maximum allowable value is +99, so +99 is entered in cell B13. If the value in cell 11 is positive, +99 should be entered as the value for cell 13. If the value of cell 11 is <+99, than value in cell 11 is added to +99 and the positive integer, up to +99 is entered in cell 13. If the value in cell 11 is +99, then +99 is entered in cell 13 and for all subsequent years / following years, the entry for cells 13 will be "zero". Once the +99 is used, it is no longer available and "zero" will be the entry for cell 13. The calculation for D13 cells should be "zero" because there is a negative value in cell D11 and the " +99" has been already used the previous year, so "zero" is the entry for cells 13 from this point forward. I don't know if this makes sense to you Lars, if not, please advise and I will try and send a different scenario / example. I also have an unrelated question that you may or may not be able to help me with Lars. I am using a Vista system and when I tried to post a REPLY in the Newsgroup form, nothing happened. My screen went blank (refreshes) for a second or so and then the same page (with all the previous post) appeared, I was not presented with the REPLY window. I had to go to another (WindowsXp) system to post this note, I was unable to get the Reply screen with the Vista system. Have you heard of any other people with this problem of not being able to access the REPLY screen? It certainly sounds like it is Vista related problem, most likely with a security feature in Vista. If you have any thoughts on this, I would appreciate hearing what you might suggest to allow me to access the REPLY screen with the Vista system Thank you again for your ALL help Lards, Bob Example: - B11=125 - B44= 80 - C11=0 - C44=120 - D11=0 - D44=150 B13 - the calculations for cell B13 should be: "+99" , because there is a positive value <+125 in cell B11 C13 - the calculations for cell C13 should be: "+99”, because 99-120= -21, therefore only a positive value, "+99" should be displayed / should appear, in cell C13 D13 - the calculations for cell D13 should be: "zero", because the results in C13 resulted in a negative number (i.e. the only number that should appear is "+99" or "a POSITIVE number only when +99 is subtracted from cell 44 or "zero" when the calculation of "+99 less the value in cell 44" is negative) Please explain again the principle difference between the examples in columns C and D. Should the result in column D be dependent on the result in column C as you write, or is that just a typo? Lars-Ĺke |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clarification of the calculations for cells "C and "D"
Hi Lars, I apologize for being sloppy, I usually am not but I am trying to describe €śIF€ť Formulas, which is something that I am not very familiar with, so I am confusing myself and obviously, confusing you as well. Let me try this, I am enclosing a spreadsheet and will try and describe what I am trying to do. As I mentioned when we started, this is a Retirement calculation, which lists various sources of funds, adjusts them for inflation and taxes, and then subtracts expenses, to determine how long €śmoney€ť will last. So taking this into considerations: Row 11 has an €śIF€ť formula that adds the totals of rows 6 and 9, and subtracts the amount in row 37. The €śIF€ť Formula for row 11 should be, if the value in row 11 (which is the sum of row 6 and 9, less row 37) is positive, the positive number should be entered in row 11. If the number is negative, €śzero€ť should be entered in row 11 and the negative value should be added to the next source of funds, which will be row 13 (and €śzero€ť should be entered in row 11 from that point on). Row 13 is one fixed number (+99 in this example), and the €śIF€ť formula for row 13 should be, if the sum of row 6 + row 9 €“ row 37, is positive, enter the positive number in row 13. If the number is negative, then this negative number should be brought down to / be added, to the next source of funds, which is row 25 (and €śzero€ť should be entered for line 13 from that point on). Row 25 is the sum of rows 21-23 adjusted for taxes and the €śIf €ś Formula for line 25 should be , if the results of row 6 + row 9 + row 13 less row 37 is positive, row 25 should display the sums of row 21-23. If the result if the results of row 6 + row 9 + row 13 less row 37 is negative, then negative number should be added to the next source of funds, which is row 31 (and €śzero€ť should be entered for line 13 from that point on). Row 31 is the sum of row 28-30 adjusted for taxes and the €śIF€ť Formula for line 31, should be, the sum of row 28-30 less 37 should be entered on line 31. Note, there are no other sources of funds so if expenses exceed income / assets, a negative number will appear on row 31, which will represent a shortfall. Column €śA€ť €śB€ť €śC€ť €śD€ť 1 2 Taxable Retirement Accounts 3 2008 2009 2010 401K 4 $200 $123 $46 Cash (withdrawals) 5 $50 $50 $50 Withdrawls WITH Taxes (.35%) 6 $77 $77 $77 IRA 7 $100 $62 $23 Cash (withdrawals) 8 $25 $25 $25 Withdrawls WITH Taxes (.35%) 9 $38 $38 $38 10 Taxable Retirement Accounts 11 $185 $69 ($46) 12 ROTH IRA 13 $99 $99 $99 14 15 Year End Retirement - Totals 16 $284 $168 $53 17 18 19 Equity assets (flat) 20 stocks 21 $3,000 $3,000 $3,000 bonds 22 $1,000 $1,000 $1,000 Anuity 23 $500 $500 $500 Total Equity Assets 24 $4,500 $4,500 $4,500 Equities AFTER Taxes (.35%) 25 $2,925 $2,925 $2,925 26 27 Cash (.02%) 28 $1,000 $1,020 $1,040 Pensions 29 $75 $75 $75 Social Security (.01%) 30 $300 $303 Cash + Pensions + Social 31 $699 $907 $922 Security AFTER Tax 32 33 34 Available assets 35 $3,907 $3,600 $3,480 36 Expenses (.05%) 37 $400 $420 $441 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting all garbled
Once again I need to apologize, I see that the Spreadsheet that I enclosed
was formatted to fit the message screen and is all garbled. The spreadsheet had 37 lines, many of them blanks (like lines / rows 1,2,10,12.........33,34 and 36). I hope that the explainations that I included will give you a better idea of what I am trying to achieve with this spreadsheet. Thank you again for your help Lars, Bob |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Windows Vista POSTing Update
Just wanted to let you know, whatever was causing my Vista computer to not
allow me to access the REPLY screen, is no longer a problem. I was able to POST the two previous notes with this Vista system. Whatever was causing this problem appears to have "gone away". |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
One more try (IF Forumals for 4 scenarios)
* Cells 11, 13, 25 and 31 contain positive numbers. * Cells 11, 13 and 25 can NOT be negative, cell 31 CAN be negative * Cell 37 should be subtracted from each of these cells individually in the following sequence: - The first calculation is, cell 37 subtracted from cell 11 - Once cell 11 is negative, cell 37 should be subtracted from cell 13 - Once cell 13 is negative, cell 37 should be subtracted from cell 25 - Once cell 25 is negative, cell 37 should be subtracted from cell 31 - If cell 31 is negative, cell 31 CAN contain a negative (if cell <37 less cell 31 is negative, the negative number should appear in cell 31) * Cells 11, 13 and 25 can not be negative; they can only contain a positive number. *If the results of the calculations are negative (that is cell 37 is greater than the one of these cells), a €śzero€ť should be entered into this cell and the negative results should be brought forward to the next cell. Example: if B37 less B11 is positive, the positive number should appears in cell 11. If C37 less C11 is negative, a €śzero€ť should be entered for cell C11 and the negative results be carried to the next cell in the sequence, C13. The calculations for C13 would be (C37 less C13) less (C37 less C11). The calculations for the €śD€ť cells would be: - Cell D11 is €śzero€ť - D13 would be the results of (D37 less D13) This continues until (D37 less D13) is a negative number When D13 becomes negative, a €śzero€ť should be entered in cell D13 and the negative results brought to cell D25 (as was done when cell 11 became negative) Cells 11 and 13 would be €śzero€ť and the calculations (cell 37 less cell 25) continue until cell 25 is negative. Once cell 25 becomes negative, the process is repeated for cell 31 If (cell 37- cell 31) results in a negative number, the NEGATIVE number SHOULD be displayed in cell 31. Cell 31 CAN display BOTH positive and negative numbers. |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
One more try (IF Forumals for 4 scenarios)
On Mon, 19 May 2008 06:04:00 -0700, Boblink
wrote: * Cells 11, 13, 25 and 31 contain positive numbers. * Cells 11, 13 and 25 can NOT be negative, cell 31 CAN be negative * Cell 37 should be subtracted from each of these cells individually in the following sequence: - The first calculation is, cell 37 subtracted from cell 11 - Once cell 11 is negative, cell 37 should be subtracted from cell 13 - Once cell 13 is negative, cell 37 should be subtracted from cell 25 - Once cell 25 is negative, cell 37 should be subtracted from cell 31 - If cell 31 is negative, cell 31 CAN contain a negative (if cell <37 less cell 31 is negative, the negative number should appear in cell 31) * Cells 11, 13 and 25 can not be negative; they can only contain a positive number. *If the results of the calculations are negative (that is cell 37 is greater than the one of these cells), a “zero” should be entered into this cell and the negative results should be brought forward to the next cell. Example: if B37 less B11 is positive, the positive number should appears in cell 11. If C37 less C11 is negative, a “zero” should be entered for cell C11 and the negative results be carried to the next cell in the sequence, C13. The calculations for C13 would be (C37 less C13) less (C37 less C11). The calculations for the “D” cells would be: - Cell D11 is “zero” - D13 would be the results of (D37 less D13) This continues until (D37 less D13) is a negative number When D13 becomes negative, a “zero” should be entered in cell D13 and the negative results brought to cell D25 (as was done when cell 11 became negative) Cells 11 and 13 would be “zero” and the calculations (cell 37 less cell 25) continue until cell 25 is negative. Once cell 25 becomes negative, the process is repeated for cell 31 If (cell 37- cell 31) results in a negative number, the NEGATIVE number SHOULD be displayed in cell 31. Cell 31 CAN display BOTH positive and negative numbers. I try to rewrite this in other word. Is this still what you want? First compare the values in cell 37 and cell 11. If the value in cell 37 is less than or equal to the value in cell 11 then just subtract the value in cell 37 from the value in cell 11, store the result in cell 11 and do nothing more to any cell. Is this correct??? If the value in cell 37 is greater than the value in cell 11 then calculate the difference between the value in cell 37 and the value in cell 11. Call this difference x. x is a positive number. Store 0 in cell 11. Now compare the value x to the value in cell 13. If the value x is less than or equal to the value in cell 13 then just substract the value x from the value in cell 13, store the result in cell 13 and do nothing more to any cell. Is this correct?? If the value x is greater than the value in cell 13 then calculate the difference between the value x and the value in cell 13. Call this difference y. y is a positive number. Store 0 in cell 13. Now compare the value y to the value in cell 25. If the value y is less than or equal to the value in cell 25 then just subtract the value y from the value in cell 25, store the result in cell 25 and do nothing moer to any cell. Is this correct?? If the value y is greater than the value in cell 25 then calculate the difference between the value y and the value in cell 25. Call this difference z. z is a positive number. Store 0 in cell 25. Finally subtract the value z from the value in cell 31 and store the result in cell 31. Is this correct?? Did you answer "YES" on all four questions above? In that case the solution is near, because the problem is formulated. To get shorter formulas it can sometimes be useful to make use of some helper cells to store intermediate values, like x, y, and z above. Assuming that we can use cell 101 for x, cell 102 for y and cell 103 for z, here is a number of formulas that you can try. The formulas that you currently have in cell 11, cell 13, cell 25, and cell 31 I call formula11, formula13, formula 25, and formula31 respectively. In cell B11 you put the following: =IF(B37<formula11, formula11-B37, 0) In cell B101 you put the following: =IF(B37<formula11, 0, B37-formula11) In cell B13 you put the following: =IF(B101<formula13, formula13-B101,0) In cell B102 you put the following: =IF(B101<formula13, 0, B101-formula13) In cell B25 you put the following: =IF(B102<formula25, formula25-B102, 0) In cell B103 you out the following: =IF(B102<formula25, 0, B102-formula25) In cell B31 you finally put the following: =formula31-B103 Exampel: The original formulas in cells 11,13,25,31,and 37 have the values 11,13,25,31,and 90 respectively as their result. After applying the above formulas the values will have changed to 0,0,0,-10,and 90 respectively. And the helper cells 101,102,and 103 hold the values 79, 66, and 41 respectively. When you have the results you expect with the aid of these helper cells you can start to try to eliminate them. The way ot doing this is to replace B101 wherever it occurs with the formula in cell B101 (except the =) and the same for cells 102 and 103 But if you don't mind these helper cells you can just hide rows 101, 102, and 103. Hope this helps. Lars-Ĺke |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
the solution is near
I try to rewrite this in other word. Is this still what you want? First compare the values in cell 40 and cell 10. If the value in cell 40 is less than or equal to the value in cell 10 then just subtract the value in cell 40 from the value in cell 10, store the result in cell 10 and do nothing more to any cell. Is this correct??? YES If the value in cell 40 is greater than the value in cell 10 then calculate the difference between the value in cell 40 and the value in cell 10. Call this difference x. x is a positive number. Store 0 in cell 10. Now compare the value x to the value in cell 12. If the value x is less than or equal to the value in cell 12 then just subtract the value x from the value in cell 12, store the result in cell 12 and do nothing more to any cell. Is this correct?? YES If the value x is greater than the value in cell 12 then calculate the difference between the value x and the value in cell 12. Call this difference y. y is a positive number. Store 0 in cell 12. Now compare the value y to the value in cell 22. If the value y is less than or equal to the value in cell 22 then just subtract the value y from the value in cell 22, store the result in cell 22 and do nothing more to any cell. Is this correct?? YES If the value y is greater than the value in cell 22 then calculate the difference between the value y and the value in cell 22. Call this difference z. z is a positive number. Store 0 in cell 22. Finally subtract the value z from the value in cell 35 and store the result in cell 35. Is this correct?? YES Did you answer "YES" on all four questions above? YES But I made some modifications to the spreadsheet and changed the row numbers (using Excel REPLACE function) as well as the start column which is now €śD€ť instead of €śB€ť. I believe the formulas that you provided (below) reflect these changes. I also added other Source of Revenue, ROW 29, and believe that the formula below is what should be used for ROW 29. In that case the solution is near, because the problem is formulated. To get shorter formulas it can sometimes be useful to make use of some helper cells to store intermediate values, like x, y, and z above. Assuming that we can use cell 101 for x, cell 102 for y and cell 103 for z, AND 104 for w, here is a number of formulas that you can try. The formulas that you currently have in cell 10, cell 12, cell 22, and cell 35 I call formula10, formula12, formula 22, and formula35 respectively. In cell D10 you put the following: =IF(D40<formula10, formula10-D40, 0) In cell D101 you put the following: =IF(D40<formula10, 0, D40-formula10) In cell D12 you put the following: =IF(D101<formula12, formula12-D101,0) In cell D102 you put the following: =IF(D101<formula12, 0, D101-formula12) In cell D22 you put the following: =IF(D102<formula22, formula22-D102, 0) In cell D103 you out the following: =IF(D102<formula22, 0, D102-formula22) In cell D29 you put the following: =IF(D102<formula29, formula29-D102, 0) In cell D104 you out the following: =IF(D102<formula22, 0, D102-formula29) In cell D35 you finally put the following: =formula35-D103 Example: The original formulas in cells 10,12,22,35,and 40 have the values 10,12,22,35,and 90 respectively as their result. I understand this (above) Lars, but everything from here are (below), I do NOT follow: After applying the above formulas the values will have changed to 0,0,0,-10,and 90 respectively. And the helper cells 101,102,and 103 AND 104 hold the values 79, 66, and 41 respectively. When you have the results you expect with the aid of these helper cells you can start to try to eliminate them. The way to doing this is to replace D101 wherever it occurs with the formula in cell D101 (except the =) and the same for cells 102 and 103 But if you don't mind these helper cells you can just hide rows 101, 102, and 103. Hope this helps. Thank you Lars, not only does it help me create the spreadsheet but more importantly, it provides me with education / helps me understand the use of these functions, so hopefully in the future, I will not require SO MUCH help. Bob As far as the results, as soon as I placed the first formula =IF(D40<formula10, formula10-D40, 0) in cell D10, cell D-10 displayed #NAME? I then placed the following formula in cell D101: =IF(D40<formula10, 0, D40-formula10) and #NAME? is what was displayed for D40. I copied the appropriate formulas for D12, D22, D29 and D35 (as well as D102, D103 and D104), and #NAME? is what was displayed on ALL these cells. I understand that these formulas are VERY TRICKY (sensitive), if you have a comma or a parenthesis or a number, in the WRONG position, the formula will NOT work so I question, is this caused by Operator Error (i.e. sloppiness on my part) or does the formula need to be modified? If it helps Lars, I will be glad to POST the spreadsheet or send it to you? Thank you again for everything that you have done, Bob |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lars
Hello Lars, I tried to send you the spreadsheet but received the following
message: wrote: Sorry, I couldn't find any host named REMOOOVE.telia.com If you would like me to send you the file, please send me a note FORREGISTRY at YAHOO dot COM and I reply with the spreadsheet. Thank you again for ALL your help, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
function to return day in the form "Monday", "Tuesday" etc given . | Excel Worksheet Functions | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |