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

Chuchie

New Member
Joined
Sep 26, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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!!!!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.

Screenshot 2024-09-27 000118.jpg

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

Come on ROCKSTARS!!!!
 
Upvote 0
Welcome back!

Try:

Excel Formula:
=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".
 
Upvote 0

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top