• 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.

If Or - portion not working

rpzzle

New Member
I have tried various formulas and none are giving me the result I would like under column H. I separated and bolded the part of the formula that is not working.

=IF(AND(D2>25,G2="F"),"Women",IF(AND(D2>25,G2="M"),"Men",IF(AND(D2>=20,D2<=25),"Young Adult", IF(OR(F2="Yes",D2<=19),"Teen"))))

I want to get H to result as Teen if age is less than or equal to 19 OR if H.S. indicates yes. Row 6 shows it will work when I have an age less than 19 but I would like it to show teen if D is blank as well. Any recommendations? Thank you.

SAMPLE.jpg
 

Attachments

  • IfOrHelp.xlsx
    13 KB · Views: 5
Try this,

=IF(AND(D2>25,G2="F"),"Women",IF(AND(D2>25,G2="M"),"Men",IF(AND(D2>=20,D2<=25),"Young Adult",IF(OR(F2="Yes",D2<=19,LEN(D2)<1),"Teen"))))
 
I tried and it still returns men where D5 is blank. I tried incorporating ISBLANK but I dont think I did so correctly.
 
ISBLANK will not work, since the cell isn't actually blank, it contains formula.

The above formula is working for me, have a look.

Are you looking for "Teen" whenever column D is blank? just like Row 5?

65811
 
Not sure what the correct logic is to apply. Row 4, column H.S. contains a YES and according to the last IF () that should be "TEEN". However age is 40 thus "WOMEN"? Row 5 should be returning "TEEN", because of a missing age and H.S. is "YES".
Also, when there is no name, should it return "TEEN"? Why?

Perhaps this is already a bit closer
=IF(AND(D2>25,G2="F"),"Women",IF(AND(D2<>"",D2>25,G2="M"),"Men",IF(AND(D2>=20,D2<=25),"Young Adult",IF(OR(F2="Yes",D2<=19,D2=""),"Teen"))))

EDIT: shorter it becomes =IF(AND(D2>25,G2="F"),"Women";IF(AND(D2<>"",D2>25,G2="M"),"Men",IF(OR(F2="Yes",D2<=19,D2=""),"Teen","Young Adult")))
 
Last edited:
Not sure what the correct logic is to apply. Row 4, column H.S. contains a YES and according to the last IF () that should be "TEEN". However age is 40 thus "WOMEN"? Row 5 should be returning "TEEN", because of a missing age and H.S. is "YES".
Also, when there is no name, should it return "TEEN"? Why?

Perhaps this is already a bit closer
=IF(AND(D2>25,G2="F"),"Women",IF(AND(D2<>"",D2>25,G2="M"),"Men",IF(AND(D2>=20,D2<=25),"Young Adult",IF(OR(F2="Yes",D2<=19,D2=""),"Teen"))))

EDIT: shorter it becomes =IF(AND(D2>25,G2="F"),"Women";IF(AND(D2<>"",D2>25,G2="M"),"Men",IF(OR(F2="Yes",D2<=19,D2=""),"Teen","Young Adult")))

You are totally right. Row 4 being a yes was my mistake. If the person is 26 and above I want them to be men or women. What I really need to differentiate is when someone is 18 - 19, if that person is still in High school "Yes" then that person should be Teen. If that person is no longer in High School "No" then they should be Young Adult. I'm not sure why from 9 on down I'm getting designation of Teen if I have not entered a graduation date or d.o.b.. Your suggestion worked - thanks!

I went back and saw this under F and I think that is why I am getting Teen when there is no information. I think I was trying to have a backup in case we did not get a graduation date. I tried removing the ISBLANK portion and everything stayed the same.
=IF(ISBLANK(E2),"No",IF(E2>TODAY(),"Yes","No"))
 
Last edited:
Seems you have changed the criteria between your initial post and this last one :-(
There is a reason why we ask when uploading a sample file to ensure all scenarios are present and that at least there is manually filled column including a result for each of the scenarios. Only then we can provide robust suggestions and solutions. We can only ask to consider these suggestions :)

When construction nested IFs statements, understand that the first evaluation that matches the criteria will throw in the result even if a "later" evaluation statement is actually better. So the order of the arguments does matter. Like in my example "Young adult" is the result for all that does not match any of the previous logical tests or evaluation arguments. Likewise to remove any result when there is no name given: start with = IF(A2="" , "" , ....

Though using a table construct it is not required. Simply resize the table the rows containing a name. When typing new names and adding new records, the auto-extend feature of the table kicks in and the formulas are populated automatically.
 
As Guido has observed there are a number of unexpected features in the workbook. The formulas are within a table but the features of the table such as structured referencing, formatting and its dynamic behaviour are not exploited. Like racehorses, all of your individuals share an official birthday of 1st Jan. If later birthdays are permitted
= IF( ISBLANK([@[D.o.B.]]),"", DATEDIF([@[D.o.B.]], TODAY(),"y") )
would give greater accuracy for the calculated age.

As was pointed out, ISBLANK will not work with a calculated age, but it is possible to test the D.o.B. Typically, if you have covered all possibilities for [@Age]>25, then by the time you are following the FALSE branch of the logical tests, it may be assumed that [@Age]<=24 is satisfied. You wouldn't have reached the test if it were not.
 
Back
Top