nested IF without repeating the condition to be tested

[viasarpimilano]

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(condition=3;true;....
and so on

is there a way to bring "condition" outside???
thanks a lot in advance

 

[Don Guillett]

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(condition=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

 

[joeu2004]

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(condition=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,falseResult)

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)

 

[viasarpimilano]

=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

 

[viasarpimilano]

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

 

[joeu2004]

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.

example:
IF(condition=1;true;(if(condition=2;true;(if(condition=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.

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!

 

[viasarpimilano]

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.