• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple IF Function to Pick Errors & Blanks

R

Ravi Rajendran

Member
Hello All, Hope you all are having great day!

Need some help with Multiple IF function to produce result by comparing 2 values involving few scenarios. though i am struggling with Blanks & Errors. i hope one of you can be of kind help. Thank you - Pls see attached spreadsheet.

Cheers!
Ravi Rajendran
 

Attachments

Hi Ravi -

Paste the below formula in cell C2 and drag it down..

IFERROR(IF(AND($B2="",$D2=""),"No Dates",IF(AND($B2<>$D2,$B2<>"",$D2<>""),"Changed",IF(AND($B2="",$D2<>""),"New",IF(AND($B2=$D2),"No Change","Deleted")))),"Deleted")
 
Hi,

Here is another one:

=IF(AND(ISBLANK(B2),ISBLANK(D2)),"No Dates",IFERROR(IF(B2=D2,"No Change",IF(AND(ISBLANK(B2),ISNUMBER(D2)),"New","Changed")),"Deleted"))

Regards,
 
Asheesh said:
Hi Ravi -

Paste the below formula in cell C2 and drag it down..

IFERROR(IF(AND($B2="",$D2=""),"No Dates",IF(AND($B2<>$D2,$B2<>"",$D2<>""),"Changed",IF(AND($B2="",$D2<>""),"New",IF(AND($B2=$D2),"No Change","Deleted")))),"Deleted")
Click to expand...

Hi Asheesh / All - i need some help on this again please.

Basically i am using the same dates columns as per attached - but i dont want to mark the dates changed within same month as Changed. Highlighted the section which i am interested in applying this logic.

i am not sure how to apply the required logic even in the formula suggested by Khalid either. Sorry!

Any help here would be really appreciated. many thanks
RR
 

Attachments

Hi Ravi,
See if helps:

=IF(AND(ISBLANK(B2),ISBLANK(D2)),"No Dates",IFERROR(IF(OR(B2=D2,MONTH(B2)=MONTH(D2)),"No Change",IF(AND(ISBLANK(B2),ISNUMBER(D2)),"New","Changed")),"Deleted"))

Bold part updated.
Regards,
 
Oops sorry i didn't noticed that.
I think formula needs to be changed totally.

Let me go through this, I will be back with solution hopefully :)
 
Here it is:

=IF(AND(ISBLANK(B2),ISBLANK(D2)),"No Dates",IFERROR(IF(OR(B2=D2,AND(B2<>D2,MONTH(B2)=MONTH(D2),YEAR(B2)=YEAR(D2))),"No Change",IF(AND(ISBLANK(B2),ISNUMBER(D2)),"New","Changed")),"Deleted"))

Pardon if it also not works as required.

Regards,
 
Hi Ravi ,

I think we are eager to jump into formula development instead of planning it out by describing the logic.

1. If both Date 1 and Date 2 are blank - No Dates

2. If Date 1 is blank , and Date 2 has a date - New

3. If Date 1 has a date and Date 2 is #N/A- Deleted

4. If both Date 1 and Date 2 have dates , if they are equal - No Change

5. If both Date 1 and Date 2 have dates , if month and year of both dates are equal - No change

6. If both Date 1 and Date 2 have dates , if either month or year of both dates differ - Changed

You can implement this logic using IF statements the way it has been written , but you can optimize the formula if you rearrange them appropriately.

Try this :

=IF(ISERROR(D2),"Deleted",IF(ISBLANK(B2),IF(B2=D2,"No Dates","New"),IF(OR(MONTH(B2)<>MONTH(D2),YEAR(B2)<>YEAR(D2)),"Changed","No Change")))

Narayan
 
You must log in or register to reply here.
Back
Top