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

 

[fingermouse]

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

Brilliant Scott, ive tested and it works!!! many thanks for all your help and patience, its much appreciated.