Already functioning calc, Just can't figure out this last required addition.

[Chuchie]

Hi excel gurus!!!!
I had to re register after years on this site then due to inactivity during disability, my account here was suspended. Grateful to finally get back in amongst the brainyacks.

I am helping an anonymous group and designed an excel sheet tracking their home group members status; joined/new, 2 months inactive and the regularly active members.
We have been using this calc with no issues but now they want to change one thing and for hours and many adjustments, i cannot figure to work it correctly.
CAN ANYONE HELP?

ORIGINAL CALC WORKS AS INTENDED: this column with others, helps us determine active members for a quorum to vote at monthly group conscious meetings.
=IF(AND(AI15:AJ15="ABSENT"),"INACTIVE",IF(OR(AJ15:AK15="*joined*"),"NEW",IF(OR(AK15:AK15="NOTICE"),"*NOTICE","")))

HOWEVER A CHANGE IS NOW NEEDED. I need to add in a NEW caveat to this statement: IF(OR(AJ15:AK15="*joined*"), "NEW", already in the calc,
ADD IN***(AJ15:AK15, = JOINED BUT does NOT Contain IN THE SAME COULMNS "Present" to ALSO return "New"****

OBJECTIVE is this, if they show joined in LAST 3 months, but have NOT YET BEEN PRESNT in those 3 months, they are still New.

Can't wait to see how I'm missing this.

Come on ROCKSTARS!!!!

 

[Chuchie]

Hi excel gurus!!!!
I had to re register after years on this site then due to inactivity during disability, my account here was suspended. Grateful to finally get back in amongst the brainyacks.

I am helping an anonymous group and designed an excel sheet tracking their home group members status; joined/new, 2 months inactive and the regularly active members.
We have been using this calc with no issues but now they want to change one thing and for hours and many adjustments, i cannot figure to work it correctly.
CAN ANYONE HELP?

ORIGINAL CALC WORKS AS INTENDED: this column with others, helps us determine active members for a quorum to vote at monthly group conscious meetings.
=IF(AND(AI15:AJ15="ABSENT"),"INACTIVE",IF(OR(AJ15:AK15="*joined*"),"NEW",IF(OR(AK15:AK15="NOTICE"),"*NOTICE","")))

HOWEVER A CHANGE IS NOW NEEDED. I need to add in a NEW caveat to this statement: IF(OR(AJ15:AK15="*joined*"), "NEW", already in the calc,
ADD IN***(AJ15:AK15, = JOINED BUT does NOT Contain IN THE SAME COULMNS "Present" to ALSO return "New"****

OBJECTIVE is this, if they show joined in LAST 3 months, but have NOT YET BEEN PRESNT in those 3 months, they are still New.

Can't wait to see how I'm missing this.

Come on ROCKSTARS!!!!

 

[Eric W]

Welcome back!

Try:

=IF(AND(AI15:AJ15="ABSENT"),"INACTIVE",IF(OR(AJ15:AK15="*joined*",COUNTIF(AJ15:AK15,"Present")=0),"NEW",IF(OR(AK15:AK15="NOTICE"),"*NOTICE","")))

On the last part, did you mean OR(AK15:AK15="NOTICE") ? If the range really just is AK15, you can simply use AK15="NOTICE".