• 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

  • Multiple IF for Errors & Blanks.xlsx
    10 KB · Views: 7
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

  • Multiple IF for Errors & Blanks.xlsx
    10 KB · Views: 4
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