nested IF without repeating the condition to be tested


V

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
 
Ad

Advertisements

D

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
 
J

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)
 
V

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
 
V

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
 
J

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!
 
Ad

Advertisements

V

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top