Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested IF without repeating the condition to be tested
hi everyone
is there a nice elegant way to avoid repeating the same test condition when nesting multiple IF? I mean, my condition is a long formula which must be equal to something. 4 different results are possible. So 3 nested IF. At the moment I write again my long formula to be tested in each IF. If there is a way to use "OR" instead, I could avoid it example: IF(condition=1;true;(if(condition=2;true;(if(condi tion=3;true;.... and so on is there a way to bring "condition" outside??? thanks a lot in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested IF without repeating the condition to be tested
On Monday, March 5, 2012 9:32:30 AM UTC-6, wrote:
hi everyone is there a nice elegant way to avoid repeating the same test condition when nesting multiple IF? I mean, my condition is a long formula which must be equal to something. 4 different results are possible. So 3 nested IF. At the moment I write again my long formula to be tested in each IF. If there is a way to use "OR" instead, I could avoid it example: IF(condition=1;true;(if(condition=2;true;(if(condi tion=3;true;.... and so on is there a way to bring "condition" outside??? thanks a lot in advance Look in the help index for OR |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested IF without repeating the condition to be tested
wrote:
is there a nice elegant way to avoid repeating the same test condition when nesting multiple IF? I mean, my condition is a long formula which must be equal to something. 4 different results are possible. So 3 nested IF. At the moment I write again my long formula to be tested in each IF [....] IF(condition=1;true;(if(condition=2;true;(if(condi tion=3;true;.... and so on If you want to return the same "true" result in all cases, you can write: =IF(OR(expression={1,2,3,4}),trueResult,falseResul t) Note: That formula can be entered normally by pressing just Enter. It does __not__ need to be array-entered by pressing ctrl+shift+Enter. More generally, if you are sure that "condition" (expression) will equal only 1 through 4, you can write: =CHOOSE(expression,result1,result2,result3,result4 ) If "expression" might have other results, but you are content to default to "result1" or "result4" in that case, then perhaps you need: =CHOOSE(MAX(1,MIN(4,ROUND(expression,0))),result1, result2,result3,result4) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested IF without repeating the condition to be tested
On Monday, 5 March 2012 18:35:20 UTC+1, joeu2004 wrote:
=CHOOSE(expression,result1,result2,result3,result4 ) thanks but I dont see how this would solve my problem. I'd still need a nested IF to find the "position" of CHOOSE. in your example, the 'expression' is determined by a nested IF |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested IF without repeating the condition to be tested
let's make it more clear.
User has a worksheet with a database with many records/rows. For each row, there is a cell with a value with can be one of these four options: A, AA, AAA, AAAA. According to which option is selected for each record, a different SUM has to be performed. Now on a different worksheet, I need a formula which performs the proper SUM, according to what is written in the database. So the "nested if" came to my mind. IF('somethingLong'=A;sumTHIS;if('somethingLong'=AA ;sumThat;if('somethingLong'=AAA;sumThose;sumthese) )) I am looking for a way to avoid writing 'somethingLong' 3 times. hope is clearer thanks a lot |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested IF without repeating the condition to be tested
wrote:
On Monday, 5 March 2012 18:35:20 UTC+1, joeu2004 wrote: =CHOOSE(expression,result1,result2,result3,result4 ) thanks but I dont see how this would solve my problem. Well, if you totally mispresent the problem in the first place, I cannot be expected to provide solutions that apply to you. Originally, you wrote: example: IF(condition=1;true;(if(condition=2;true;(if(condi tion=3;true; .... and so on is there a way to bring "condition" outside? I answered that very question. First, I replaced the word "condition" with the word "expression" because the __condition__ is "something=1", "something=2", etc. Then I wrote: "__if__ you are sure that "condition" (expression) will equal only 1 through 4, you can write: =CHOOSE(expression,result1,result2,result3,result4 )". CHOOSE solves the problem __if__ "condition" (expression) returns 1 through 4. It does not require any other "nested IF". The only problem was: you misrpresented the problem. GIGO. Later, you wrote: IF('somethingLong'=A;sumTHIS;if('somethingLong'=AA ;sumThat; ('somethingLong'=AAA;sumThose;sumthese))) I am looking for a way to avoid writing 'somethingLong' 3 times. Of course CHOOSE does not solve __that__ problem. It is a completely different problem. But even this new description is misleading and wrong. Perhaps you mean something="A" and something="AA" -- quoted strings. But then again, perhaps that's not what you mean at all. Who the hell knows?! I, for one, am tired of trying to second guess the real problem you are trying to solve. It is a waste of time, yours as well as mine. Good luck! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested IF without repeating the condition to be tested
Il giorno marted́ 6 marzo 2012 08:42:55 UTC+1, joeu2004 ha scritto:
<ote: But even this new description is misleading and wrong. Perhaps you mean something="A" and something="AA" -- quoted strings. well, I am sorry for not providing clear info. I apologize for that, no intention to waste people time. You're right, I meant "A", "AA", and "AAA", quoted string, because this is what User has in the input database. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What are the most important Excel concepts I should know (will be tested soon)? | Excel Discussion (Misc queries) | |||
Please Help Error 400 when running tested code | Excel Discussion (Misc queries) | |||
Any Nested Function & Condition For Calculating Date & Time | Excel Worksheet Functions | |||
Trouble with condition nested formula | New Users to Excel | |||
What are the most important Excel concepts I should know (will be tested soon)? | Excel Discussion (Misc queries) |