making a formula with multiple criterias

**abrunstad** · 03-30-2009, 05:57 AM

Hello guys,

Iv'e got this formula;
=IF(B57=0,"",IF(B56="","",IF(A56="slakteklar","",IF(B56="slakteklar","slakteklar",IF(A56="sulting","slakteklar",(IF(B54<='Input og output verdier'!$E$13,((B56*(1+B60)^B65)),"sulting")))))))

Now, the part (IF(B54<='Input og output verdier'!$E$13,((B56*(1+B60)^B65)),"sulting"))))))) is accounting for a max value.

But i want the IF B54 to also consider another max value ('Input og output verdier'!$E$12).

So, how do I define this? I guess it will have to be some kind of OR function, bit I do not manage to complete it..

cheers

**DonkeyOte** · 03-30-2009, 06:03 AM

If I've understood, perhaps:

=IF(OR(B57=0,B56="",A56="slakteklar"),"",IF(OR(B56="slakteklar",A56="sulting"),"slakteklar",IF(B54<=MAX('Input og output verdier'!$E$12:$E$13),B56*(1+B60)^B65,"sulting")))

**abrunstad** · 03-30-2009, 06:38 AM

The answer to this one is;

=IF(B57=0,"",IF(B56="","",IF(A56="slakteklar","",IF(B56="slakteklar","slakteklar",IF(A56="sulting","slakteklar",IF(OR(C3<=6000)*OR(C4<=24),((B56*(1+B60)^B65)),"sulting")))))))

**DonkeyOte** · 03-30-2009, 06:43 AM

I'm glad you have resolved as your resolution bears no resemblance to your initial question so I suspect no one else could have.

However, as highlighted previously you can condense a lot of your IF's into ORs

For ex. these 3 IF's

=IF(B57=0,"",IF(B56="","",IF(A56="slakteklar","",...)))

Can simply become 1 IF with an OR:

=IF(OR(B57=0,B56="",A56="slakteklar"),"",...)

Also this:

IF(OR(C3<=6000)*OR(C4<=24),...,...)

will work but is inefficient and contains needless clauses, it should be

IF(OR(C3<=6000,C4<=24),...,...)

So based on your solution of 6 IFs:

=IF(B57=0,"",IF(B56="","",IF(A56="slakteklar","",IF(B56="slakteklar","slakteklar",IF(A56="sulting"," slakteklar",IF(OR(C3<=6000)*OR(C4<=24),((B56*(1+B60)^B65)),"sulting")))))))

This could I think be shortened to 3 IFs:

=IF(OR(B57=0,B56="",A56="slakteklar"),"",IF(OR(B56="slakteklar",A56="sulting"),"slakteklar",IF(OR(C3<=6000,C4<=24),B56*(1+B60)^B65,"sulting")))

**abrunstad** · 03-30-2009, 05:23 PM

Thanks!

Great help, the summary there! So much easier to stay on top of the formula when formulating the formula like that! =)

I am sorry for a poor formulated question i the begnning of this thread thoug.

andreas.