Combine two working formulas

[fingermouse]

Hi, hopefully someone can help with my problem as per the title:

I have 2 working formulas I want to combine with a bit of adjusting:

This formula works (Formula 1)

=IF(AND('Data Area'!BH2="N/A",'Data Area'!AU2="DS"),"Pass","Fail - POMI cannot be N/A")


So does this one: (Formula 2)

=IF(OR(ISBLANK('Data Area'!BH2),ISBLANK('Data Area'!CM2)),"Fail-POPT or POPI cannot be blank",IF('Data Area'!AR2="EWI_Parkhomes",IF(VALUE('Data Area'!CM2)<>VALUE('Data Area'!BH2),"Fail - POMI and POPT for park homes insulation measures must always be equal","Pass"),IF('Data Area'!BH2="N/A","Fail - POMI cannot be N/A",IF(VALUE('Data Area'!CM2)>VALUE('Data Area'!BH2),"Fail - POPT cannot be greater than POMI","Pass"))))

I want to adapt it slightly and replace the red part in formula 2 with formula 1. Like this:


=IF(OR(ISBLANK('Data Area'!BH2),ISBLANK('Data Area'!CM2)),"Fail-POPT or POPI cannot be blank",IF('Data Area'!AR2="EWI_Parkhomes",IF(VALUE('Data Area'!CM2)<>VALUE('Data Area'!BH2),"Fail - POMI and POPT for park homes insulation measures must always be equal","Pass"),IF(AND('Data Area'!BH2="N/A",'Data Area'!AU2="DS"),"Pass","Fail - POMI cannot be N/A",IF(VALUE('Data Area'!CM2)>VALUE('Data Area'!BH2),"Fail - POPT cannot be greater than POMI","Pass"))))

Error message says I have too many arguments. Can someone please provide the correct formula? Many thanks in advance, Cal

 

[Scott T]

Re: need help to combine two working formulas

The part you have in red is its own IF statement with the logical test, value if true, and value if false. When nesting If statements at lease one of the value is should be another IF statement.

 

[Roxxien]

Re: need help to combine two working formulas

You are changing an If containing 2 arguments (test and if true) for an If containing 3 arguments (test, if true and if false). That create that your If now contain a total of arguments which is impossible.

 

[fingermouse]

Re: need help to combine two working formulas

Thanks for responding Scott, but Im sorry I dont understand what you mean.

 

[fingermouse]

Re: need help to combine two working formulas

Thanks Roxxien - how do i change the formula so that it works?

 

[Scott T]

Re: need help to combine two working formulas

Thanks Roxxien - how do i change the formula so that it works?

What do you want to happen when this is false?

AND('Data Area'!BH2="N/A",'Data Area'!AU2="DS")

 

[fingermouse]

Re: need help to combine two working formulas

What do you want to happen when this is false?

AND('Data Area'!BH2="N/A",'Data Area'!AU2="DS")

if false return "Fail - POMI cannot be N/A"

if true return "Pass"

 

[Scott T]

Re: need help to combine two working formulas

If you return "pass" or "fail-POMI cannot be N/A" then the part in purple will never be tested. Without knowing what you are doing we can not rewrite your formula.

[B]=IF(OR(ISBLANK('Data Area'!BH2),ISBLANK('Data Area'!CM2)),"Fail-POPT or POPI cannot be blank",IF('Data Area'!AR2="EWI_Parkhomes",IF(VALUE('Data Area'!CM2)<>VALUE('Data Area'!BH2),"Fail - POMI and POPT for park homes insulation measures must always be equal","Pass"),[/B][B][COLOR=#ff0000]IF(AND('Data Area'!BH2="N/A",'Data Area'!AU2="DS"),"Pass","Fail - POMI cannot be N/A"[/COLOR],[/B][B][COLOR=#800080]IF(VALUE('Data Area'!CM2)>VALUE('Data Area'!BH2),"Fail - POPT cannot be greater than POMI","Pass")[/COLOR])))[/B]

 

[fingermouse]

Re: need help to combine two working formulas

If you return "pass" or "fail-POMI cannot be N/A" then the part in purple will never be tested. Without knowing what you are doing we can not rewrite your formula.

[B]=IF(OR(ISBLANK('Data Area'!BH2),ISBLANK('Data Area'!CM2)),"Fail-POPT or POPI cannot be blank",IF('Data Area'!AR2="EWI_Parkhomes",IF(VALUE('Data Area'!CM2)<>VALUE('Data Area'!BH2),"Fail - POMI and POPT for park homes insulation measures must always be equal","Pass"),[/B][B][COLOR=#ff0000]IF(AND('Data Area'!BH2="N/A",'Data Area'!AU2="DS"),"Pass","Fail - POMI cannot be N/A"[/COLOR],[/B][B][COLOR=#800080]IF(VALUE('Data Area'!CM2)>VALUE('Data Area'!BH2),"Fail - POPT cannot be greater than POMI","Pass")[/COLOR])))[/B]

Im sorry, I know you are trying to help me. im failing to create the code and failing to articulate what I need too....

OK...The first part of the code returns a fail message if either of the two cells referenced are blank.

IF(OR(ISBLANK('Data Area'!BH2),ISBLANK('Data Area'!CM2)),"Fail-POPT or POPI cannot be blank"

The next part of the code checks that if AR2 is 'EWI_Parkhomes', and if the value of CM2 and bh2 are not equal, then its a fail. Otherwise pass.

IF('Data Area'!AR2="EWI_Parkhomes",IF(VALUE('Data Area'!CM2)<>VALUE('Data Area'!BH2),"Fail - POMI and POPT for park homes insulation measures must always be equal","Pass")

The next part (the part I cant do) is that if CM2 is 'N/A' AND if cell AU2 is ‘DS’, then "Pass", otherwise its a "Fail" So basically the only combination of CM2 and AU2 that can result in a pass is 'N/A' and 'DS'. Does that make sense?

IF(AND('Data Area'!BH2="N/A",'Data Area'!AU2="DS"),"Pass","Fail - POMI cannot be N/A"

The final part of the formula produces a fail message if the value of CM2 is greater than BH2. If the value isnt greater, its a "pass".

IF(VALUE('Data Area'!CM2)>VALUE('Data Area'!BH2),"Fail - POPT cannot be greater than POMI","Pass"

The formula has to do all of the things ive broke down- i just dont know how to piece it all together.

 

[Scott T]

Re: need help to combine two working formulas

Looks like you are trying to get "pass" returned before checking all the conditions. the part you have in red should be coded to check for failure and return the fail message then the false argument should be your last if statement. Untested but something like this maybe what you want

=IF(OR(ISBLANK('Data Area'!BH2),ISBLANK('Data Area'!CM2)),"Fail-POPT or POPI cannot be blank",IF('Data Area'!AR2="EWI_Parkhomes",IF(VALUE('Data Area'!CM2)<>VALUE('Data Area'!BH2),"Fail - POMI and POPT for park homes insulation measures must always be equal","Pass"),IF(AND('Data Area'!BH2<>"N/A",'Data Area'!AU2<>"DS"),"Fail - POMI cannot be N/A",IF(VALUE('Data Area'!CM2)>VALUE('Data Area'!BH2),"Fail - POPT cannot be greater than POMI","Pass"))))