Menu
Sumerian Voter Problem [IF formula homework]
Excel Challenges - 135 comments
Here is a simple IF formula challenge for you. Go ahead and post your answers in the comments section.
Can this person vote in Sumeria?
Imagine you are the chief election officer in the great country of Sumeria. You have introduced a new eligibility criteria for voters just before the grand presidential elections of 2016. In order to vote,
- Male citizens must be 21 years or older
- Female citizens must be 18 years or older
- Non-citizens can vote if they are 24 years or older and have been living in Sumeria since 1st of Jan 2006.
- Age should be calculated as of TODAY. For the purpose of age calculation, one year = 365 days.
So on one snowy April morning (it snows every month in Sumeria!), you find yourself staring at the Sumerian voter list. You need to find out if each person on that list can actually vote.
What formula would you write in the can vote? column.
Feel free to use below structural names or cell references in your answer.
- [@Gender] or C4
- [@[Date of Birth]] or D4
- [@Citizen] or E4
- [@[Resident Since]] or F4
Go ahead and figure out the formula
And share your answers in the comments section.
Download the Sumerian voter list if you want practice data.
NOTE: When posting >= or > symbols in the formula, replace them with GTE or GT. Otherwise, wordpress (my blogging software) will eat up your > symbols.
Related: An IF formula challenge for you and How to write business logic in Excel formulas
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Excel Tips, Tricks, Cheats & Hacks – Microsoft MVP Edition | Earth Venus cosmic dance – Animated chart in Excel » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
135 Responses to “Sumerian Voter Problem [IF formula homework]”
I've gone for this:
=IF(E4="no",IF(AND(D4=DATEVALUE("01/01/2006")),"yes","no"),IF(OR(AND(C4="male",(D4<=(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-(21*365)))),AND(C4="female",(D4<=(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-(18*365))))),"yes","no"))
Seems to work, although I'm sure someone can work out something that’s less complicated!
Correction, should be:
=IF(E4="no",IF(D4=DATEVALUE("01/01/2006"),"yes","no"),IF(OR(AND(C4="male",(D4<=(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-(21*365)))),AND(C4="female",(D4<=(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-(18*365))))),"yes","no"))
@Jake your formula doesn't take account of when non-citizens took up residence in the country. To fix that, you'd need to change the first part of your formula as follows:
=IF(E4="no",IF(F4<=DATEVALUE("01/01/2006"),...
However, even with this, I think your formula is still wrong. You need to ask if the non-citizen is over 24, which you haven't done. In other words, someone who entered the country on or before 1/1/2006 can vote, even if they're only 10 years of age!
most raw formula which I can come up with is:
=IF([@Citizen]="Yes",IF(OR(AND([@Gender]="Male",TEXT(TEXT(TODAY()-[@[Date of Birth]],0)/365,0)>="21"),AND([@Gender]="Female",TEXT(TEXT(TODAY()-[@[Date of Birth]],0)/365,0)>="18")),"Yes","No"),IF(TEXT(TEXT(TODAY()-[@[Resident Since]], 0)/365, 0)>="24","Yes.","No"))
@gsvidri This formula doesn't work correctly. If someone is not a citizen, your formula only checks if they have been in the country more than 24 years. However it should check if they've been here since 1/1/2006 and if their date of birth is more than 24 years ago.
@gsvirdi I got your name wrong in the previous comment. Apologies.
=SUMPRODUCT(
(--([@Citizen]="Yes")*--([@Gender]="Male")*--(DATEDIF([@[Date of Birth]],TODAY(),"y")>=21))+
(--([@Citizen]="Yes")*--([@Gender]="Female")*--(DATEDIF([@[Date of Birth]],TODAY(),"y")>=18))+
(--([@Citizen]="No")*--(DATEDIF([@[Date of Birth]],TODAY(),"y")>=24)*--([@[Resident Since]]<=DATEVALUE("01/01/2006"))))
I went the SUMPRODUCT route too, and started off with (effectively) the same as James.
Then I reduced it down, eliminating duplication by moving the resident check and gender check to the DOB section, and the citizen check also to the Resident Since section (ensuring this section always returns TRUE for citizens).
The resulting formula is:
=IF(SUMPRODUCT(
(([@[Date of Birth]]<=(TODAY()-(
IF([@Citizen]="No",24,
IF([@Gender]="Male",21,18))*365))))
*([@[Resident Since]]<=
IF([@Citizen]="Yes",[@[Resident Since]],DATE(2006,1,1)))
)=0,"No","Yes")
If 1 or 0 are sufficient, can omit the outer IF(...=0,"No","Yes").
=IF([@Citizen]="Yes",OR(AND([@Gender]="Male",((TODAY()-[@[Date of Birth]])/365) GTE 21),AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/365) GTE 18)),(AND((TODAY()-[@[Date of Birth]])/365 GTE 24,[@[Resident Since]] GTE DATE(2006,1,1))))
Somehow the GTE did not convert to >=
@Amit Goyal I think your formula is almost correct, apart from at the very end, it should check if the [@[Resident Since]] is LTE Date(2006,1,1), i.e., before or on that date.
=IF([@Citizen]="Yes",IF([@Gender]="Male",IF(((TODAY()-[@[Date of Birth]])/365)>20,"Yes","No"),IF(((TODAY()-[@[Date of Birth]])/365)>17,"Yes","No")),IF([@[Resident Since]]<NUMBERVALUE(DATE(2006,1,1)), IF(((TODAY()-[@[Date of Birth]])/365)>23,"Yes","No"),"No"))
using > & < instead of GT & GTE et al.
=(DATEDIF([@[Date of Birth]],TODAY(),"Y")>=IF([@Gender]="Male",21,18))*([@Citizen]="Yes")+([@Citizen]="No")*([@[Resident Since]]=24
I did put an IF in my formula 🙂
Hey, wordpress did not eat up the ">="...
but it ate every thing in between .....
my formula should be
=(DATEDIF([@[Date of Birth]],TODAY(),"Y")GTE.IF([@Gender]="Male",21,18))*([@Citizen]="Yes")+([@Citizen]="No")*([@[Resident Since]]LTE.(DATE(2006,1,1)))*DATEDIF([@[Date of Birth]],TODAY(),"Y")GTE.24
where GTE. >=
LTE. <=
=IF(AND([@Citizen]="YES",OR(AND([@Gender]="MALE",[@[Date of Birth]]<=TODAY()-(365*21)),AND([@Gender]="Female",[@[Date of Birth]]<=TODAY()-(365*18)))),1,IF(OR([@[Date of Birth]]<=TODAY()-(365*24),[@[Resident Since]]<=1/1/2006),1,0))
=IF([@Citizen]="No",IF(AND(((TODAY()-[@[Date of Birth]])/365>24),[@[Resident Since]]21)),"Yes",IF(AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/36Usman5>18)),"Yes","No")))
=IF([@Citizen]="No",IF(AND(((TODAY()-[@[Date of Birth]])/365>24),[@[Resident Since]]21)),"Yes",IF(AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/365>18)),"Yes","No")))
=IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. 18,IF([@Citizen]="Yes",IF([@Gender]="female","VOTE",IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. 21,"VOTE","")),IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. 24,IF([@[Resident Since]]=18
Next, are you a citizen? If yes, are you female? is so, you can VOTE.
IF([@Citizen]="Yes",IF([@Gender]="female","VOTE"
If not female, then you're Male, but are you over 21? If so, you can VOTE, if not, No Vote.
IF(INT((TODAY()-[@[Date of Birth]])/365)>=21,"VOTE",""))
For those who aren't citizens, are you over 24?
IF(INT((TODAY()-[@[Date of Birth]])/365)>=24
If not, No Vote.
If you are over 24, have you lived here since 1/1/2006?
IF([@[Resident Since]]<=DATE(2006,1,1)
If so, you can VOTE.
Otherwise, No Vote.
Sorry, messed up on inserting that formula. Here it is again:
=IF(INT((TODAY()-[@[Date of Birth]])/365)>=18,IF([@Citizen]="Yes",IF([@Gender]="female","VOTE",IF(INT((TODAY()-[@[Date of Birth]])/365)>=21,"VOTE","")),IF(INT((TODAY()-[@[Date of Birth]])/365)>=24,IF([@[Resident Since]]=18
Next, are you a citizen? If yes, are you female? is so, you can VOTE.
IF([@Citizen]="Yes",IF([@Gender]="female","VOTE"
If not female, then you're Male, but are you over 21? If so, you can VOTE, if not, No Vote.
IF(INT((TODAY()-[@[Date of Birth]])/365)>=21,"VOTE",""))
For those who aren't citizens, are you over 24?
IF(INT((TODAY()-[@[Date of Birth]])/365)>=24
If not, No Vote.
If you are over 24, have you lived here since 1/1/2006?
IF([@[Resident Since]]<=DATE(2006,1,1)
If so, you can VOTE.
Otherwise, No Vote.
Third time lucky, I don't know what's happening here, but if this doesn't work, I'm giving up:
=IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. =18,IF([@Citizen]="Yes",IF([@Gender]="female","VOTE",IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. 21,"VOTE","")),IF(INT((TODAY()-[@[Date of Birth]])/365) .GTE. 24,IF([@[Resident Since]]<=DATE(2006,1,1),"VOTE",""),"")),"")
My suggested formula resides within the linked picture.
http://s31.postimg.org/jbbfr9juj/NONAME.png
=IF(OR((E4="No")*(TODAY()-D4 GTE 24*365)*(F4 STE --"1/1/2006"),((C4="Male")*(TODAY()-D4 GTE 21*365))+((C4 NOT EQUAL "Male")*TODAY()-D4>=18*365)),"Yes","No")
Michael Avidan
ISRAEL
Hi Michael
Your formula does not seems to work properly for non-citizen. I suspect it is the RESIDENT SINCE date calculation where you put the double negative to force the text date 1/1/2006 to a value for comparison.
Eg.
Voter ID Citizen Resident Since Can vote?
SV-000.041 No 4 Apr / 2006 Yes
SV-000.086 No 17 Apr / 2008 Yes
SV-000.106 No 12 Oct / 2010 Yes
SV-000.126 No 29 Jul / 2007 Yes
The answers should be No instead of Yes.
This looks good and works on the demo excel sheet.. Please check & confirm.. Thank you for this Home work, Chandoo 🙂
=IF([@Citizen]="Yes",IF(OR(AND([@Gender]="Male",(TODAY()-[@[Date of Birth]])/365 >= 21),(AND([@Gender]="Female",(TODAY()-[@[Date of Birth]])/365 >= 18))),"Yes","No"),IF(AND((TODAY()-[@[Date of Birth]])/365 >= 24, ([@[Resident Since]]<= DATE(2006,1,1))),"Yes","No"))
Used the R1C1 cell reference to create a Formula > Name “ageyears”. “Ageyears” referred to:
=DATEDIF(data!RC[-3],TODAY(),"y").
In the “Can Vote?” column, then used this formula: =IF(OR(AND([@Citizen]="yes",[@Gender]="male",ageyears>=21),AND([@Citizen]="yes",[@Gender]="female",ageyears>=18),AND([@Citizen]="no",[@[Resident Since]]=24)),"Yes","No")
Not sure why but when formula was copied and pasted a key element was left out. Here is revised formula;
Used the R1C1 cell reference to create a Formula > Name “ageyears”. “Ageyears” referred to:
=DATEDIF(data!RC[-3],TODAY(),"y").
In the “Can Vote?” column, then used this formula: =IF(OR(AND([@Citizen]="yes",[@Gender]="male",ageyears>=21),AND([@Citizen]="yes",[@Gender]="female",ageyears>=18),AND([@Citizen]="no",[@[Resident Since]]=24)),"Yes","No"
Unsure why but when I post, even though it appears correct in the message area, there is a piece of the final "AND" clause that is left out. This clause looks to see if the resident was there prior to 1/1/06 (I assumed that it was inclusive of 1/1/06).
The final "AND" clause should read;
"AND([@Citizen]="no",[@[Resident Since]]=24)"
Ok this looks right in the message window, Now I'll submit.
Didn't work again, but think you get idea.
Now i understand Steve. Happened to me too; twice!
Used the R1C1 cell reference to create a Formula > Name “ageyears”. “Ageyears” referred to:
=DATEDIF(data!RC[-3],TODAY(),"y").
In the “Can Vote?” column, then used this formula: =IF(OR(AND([@Citizen]="yes",[@Gender]="male",ageyears>=21),AND([@Citizen]="yes",[@Gender]="female",ageyears>=18),AND([@Citizen]="no",[@[Resident Since]]=24)),"Yes","No")
D2 = TODAY()
F2 = 1/1/2016
G2 = Male
=IF(E4="No",F4>$F$2,($D$2-D4)/365>IF(C4=$G$2,20,17))
Regards
@Elias,
Assuming you meant 1/1/2006 in cell F2 (not 1/1/2016) please check voter ID. SV-000.001
He is a MALE, a CITIZEN and "only" 20.15 Years old.
Your suggested formula returns TRUE for him.
Voter ID: SV-000.011 in NOT a Citizen but she is "only" 12.31 Years old.
Your suggested formula returns TRUE for her.
--------------------------------------
Michael Avidan
ISRAEL
Fixed
=IF(E4="No",AND(F4+1>$F$2,($D$2-D4)/365 GTE 24),($D$2-D4)/365 GTE IF(C4=$G$2,21,18))
Elias
@Elias,
Your current formula also returns TRUE for the voter ID. SV-000.001 who did not reach the age of 21 yet.
Assuming my suggested formula returns the expected results - please check/compare at leat 200 Voters to be sure your formula returns the same.
--------------------------------------
Michael Avidan
ISRAEL
@Michael
For that particular voter and all the Citizens my formula is returning the correct results. However, it needs a little change to work correctly with No citizens.
=IF(E44="No",AND(F44 LTE $F$2,($D$2-D44)/365 GTE 24),($D$2-D44)/365 GTE IF(C44=$G$2,21,18))
Also, you may want to review your formula because it is returning wrong results for No citizens
Regards
=AND(((TODAY()-[@[Date of Birth]])/365) GTE IF([@Citizen]="Yes",IF([@Gender]="Male",21,18),24),OR([@Citizen]="Yes",AND([@[Resident Since]] NE "",[@[Resident Since]] LTE DATE(2006,1,1))))
new simpler formula:
=AND(((TODAY()-[@[Date of Birth]])/365)>=IF([@Citizen]="Yes",IF([@Gender]="Male",21,18),24),OR([@Citizen]="Yes",[@[Resident Since]]<=DATE(2006,1,1)))
=IF(OR(
AND([@Citizen]="No",(TODAY()-[@[Date of Birth]])/365 GTE 24,[@[Resident Since]] GTE DATE(2006,1,1)),
AND([@Citizen]="Yes",[@Gender]="Male",(TODAY()-[@[Date of Birth]])/365 GTE 21),
AND([@Citizen]="Yes",[@Gender]="Female",(TODAY()-[@[Date of Birth]])/365 GTE 18)),
"Yes","No")
With reason if can't vote (MC<18, FC<18, NC21*365,"Y","MC18*365,"Y","FC24*365,"Y","NC<24"))
Forgot the Non Citizen resident clause (and forgot to convert > to GT:
=IF(Citizen="Yes",IF(Gender="Male",IF(TODAY()-[@[Date of Birth]] GT 21*365,"Y","MC<21"),IF(TODAY()-[@[Date of Birth]] GT 18*365,"Y","FC<18")),IF(TODAY()-[@[Date of Birth]] GT 24*365,IF([@[Resident Since]]-42370 GTE 0,"Y","NCNR"),"NC<24"))
So many ways to get there! Here's mine...
=IF(OR(AND([@Citizen]="yes",[@Gender]="male",(TODAY()-[@[Date of Birth]])/365 GTE 21),AND([@Citizen]="yes",[@Gender]="female",(TODAY()-[@[Date of Birth]])/365 GTE 18),AND([@Citizen]="no",(TODAY()-[@[Date of Birth]])/365 GTE 24,[@[Resident Since]] LTE DATE(2006,1,1))),"Yes","No")
=IF(AND(voters[[#This Row],[Gender]]="Male",DATEDIF(voters[[#This Row],[Date of Birth]],TODAY(),"Y")>=21,voters[[#This Row],[Citizen]]="Yes"),"Yes",IF(AND(voters[[#This Row],[Gender]]="Female",DATEDIF(voters[[#This Row],[Date of Birth]],TODAY(),"Y")>=18,voters[[#This Row],[Citizen]]="Yes"),"Yes",IF(AND(voters[[#This Row],[Resident Since]]<=DATE(2006,1,1),voters[[#This Row],[Resident Since]]"",DATEDIF(voters[[#This Row],[Date of Birth]],TODAY(),"Y")>=24),"Yes","No")))
Here's my first attempt below.
=IF(AND(Gender&Citizen="MaleYes",DATEDIF(Date_of_Birth,TODAY(),"y")>=21),"YES",IF(AND(Gender&Citizen="FemaleYes",DATEDIF(Date_of_Birth,TODAY(),"y")>=18),"YES",IF(AND(Citizen="No",Resident_Since=24),"YES","NO")))
Too bad shorter and simpler formulas already proposed? Working to shorten it.
=OR(AND([@Gender]="Male",[@Citizen]="Yes",INT(TODAY()-[@[Date of Birth]])/365>=21),AND([@Gender]="Female",[@Citizen]="Yes",INT(TODAY()-[@[Date of Birth]])/365>=18),AND([@Citizen]="No",[@[Resident Since]]=24))
A portion of the last IF statement was cut-off for some reasons. Here's the full portion of the last IF statement:
...IF(AND(Citizen="No",Resident_Since=24),"YES","NO")))
Previous one was an error.
=OR(AND([@Gender]="Male",[@Citizen]="Yes",INT(TODAY()-[@[Date of Birth]])/365>=21),AND([@Gender]="Female",[@Citizen]="Yes",INT(TODAY()-[@[Date of Birth]])/365>=18),AND([@Citizen]="No",[@[Resident Since]]=24))
=IF(SUMPRODUCT((IF([@Citizen]="Yes",1,IF([@[Resident Since]]>=38718,1,0)))*(IF(AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/365)>18),1,IF( (TODAY()-[@[Date of Birth]])/365>21,1,0)))),"Yes","No")
The simplest approach is with nested IFs:
=IF([@Citizen]="Yes";IF([@Gender]="Male";DATEDIF([@[Date of Birth]];TODAY();"y") GT 20;DATEDIF([@[Date of Birth]];TODAY();"y") GT 17);AND(DATEDIF([@[Date of Birth]];TODAY();"y") GT 23;[@[Resident Since]] LE DATE(2016;1;1)))
Here is formula without IFs:
=CHOOSE(LEN([@Citizen]);;AND(DATEDIF([@[Date of Birth]];TODAY();"y") GT 23;[@[Resident Since]] LE DATE(2016;1;1));CHOOSE(LEN([@Gender])/2;;DATEDIF([@[Date of Birth]];TODAY();"y") GT 20;DATEDIF([@[Date of Birth]];TODAY();"y") GT 17))
My solution is user-defined excel function as follows:
=CanVote(B4,C4,D4,E4,F4)
where CanVote is bolean type private funtion
Private Function CanVote (x as bolean)
.
.
.
End Function
This answer tells the reason why (or why not) the person can vote, rather than just yes/no. It could be used for statistics. 🙂
=IF([@Citizen]="Yes", IF([@Gender]="Male", IF((TODAY()-[@[Date of Birth]])/365 GTE 21,"Yes- male citizen can vote","No- male citizen too
young"), IF([@Gender]="Female", IF((TODAY()-[@[Date of Birth]])/365 GTE 18, "Yes- woman citizen ok to vote","No- woman citizen too
young"))), IF([@Citizen]="No", IF((TODAY()-[@[Date of Birth]])/365 LTE 24, "No- non-citizen too young", IF([@[Resident Since]] LT DATE
(2006,1,1), "Yes- non-citizen meets requirements to vote", "No- non-citizen hasn't been resident long enough"))))
Those crazy Sumerians. In the sample data file, 25 had Residency before they were born.
Good find. I blame the Excel's random data generators 😉
@Chandoo,
For my bad English knowledge (and it has onlt to do with GTE or STE) - a NON Citizen will be allowed to vote if he/she are 24 years or older and have been living in Sumeria since 1st. of Jan, 2006.
Does this mean that if one is 26 years old and was living in Sumeria on the 25th, Dec. 2005 he/she will not be allowed - or one should have been living since 1st. Jan, 2006 up to Today BUT NOT BEFORE the 1st. Jan, 2006 ?
Thanks,
Michael Avidan
ISRAEL
In this case, the non citizen can vote.
Sorry but I still don't fully understand.
Please refer to the voters in rows: 44, 49 snd 52.
Which one, of them, can vote and who cannot ?
Thanks,
Michael Avidan
ISRAEL
Chandoo,
please do not blame the RNG. You could use a formula something like,
RANDBETWEEN([@[Date of Birth]],TODAY())
Of course, the blame is with me. Excel simply produced data based on the formulas I wrote 🙂
OR(AND([@Citizen]="Yes",OR(AND([@Gender]="Male",TODAY()-[@[Date of Birth]] GTE 21*365),AND([@Gender]="Female",TODAY()-[@[Date of Birth]] GTE 18*365))),AND([@Citizen]="No",TODAY()-[@[Date of Birth]] GTE 24*365,NOT(ISBLANK([@[Resident Since]])),[@[Resident Since]] LTE DATE(2006,1,1)))
=IF(SUM(SUMPRODUCT(([@Citizen]="Yes")*([@Gender]="Male")*(INT((TODAY()-[@[Date of Birth]])/365)>=21)),SUMPRODUCT(([@Citizen]="Yes")*([@Gender]="Female")*(INT((TODAY()-[@[Date of Birth]])/365)>=18)),SUMPRODUCT(([@Citizen]="No")*([@[Resident Since]]>=DATE(2006,1,1)))*([@age]>=24)),"Yes","No")
=IF(E4="Yes",IF(C4="Male",21,18),IF(F4<=DATE(2006,1,1),24,999))<=(TODAY()-D4)/365
@David N,
From the few voters on whom I checked your suggested formula I can only say: "Chapeau bas vous !!!"
Michael Avidan
ISRAEL
@ David,
I like this one!
Regards
=IF(AND((voters[[#This Row],[Gender]]="Male"),(H4>=21),(voters[[#This Row],[Citizen]]="Yes")),"Yes",IF(AND((voters[[#This Row],[Gender]]="Female"),(H4>=18),(voters[[#This Row],[Citizen]]="Yes")),"Yes",IF(AND((H4>=24),(voters[[#This Row],[Citizen]]="No"),(I4>=3765)),"Yes","No")))
H4 : =DATEDIF(voters[[#This Row],[Date of Birth]],TODAY(),"y")
I4: =IFERROR(DATEDIF(voters[[#This Row],[Resident Since]],TODAY(),"d"),0)
=IF([@Citizen]="Yes",IF(AND([@Gender]="Male",YEAR(TODAY())-YEAR([@[Date of Birth]])>21),"Yes",IF(AND([@Gender]="Female",YEAR(TODAY())-YEAR([@[Date of Birth]])>18),"Yes","No")),IF(AND(YEAR(TODAY())-YEAR([@[Date of Birth]])>21,[@[Resident Since]]<DATE(2006,1,1)),"Yes","No"))
Hi
Chandoo,
Just small change in formula,
=IF([@Citizen]="Yes",IF(AND([@Gender]="Male",((TODAY()-[@[Date of Birth]])/365)>21),"Yes",IF(AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/365)>18),"Yes","No")),IF(AND(((TODAY()-[@[Date of Birth]])/365)>21,[@[Resident Since]]<DATE(2006,1,1)),"Yes","No"))
=OR(AND(([@Gender]="Male"),(TODAY()-[@[Date of Birth]] GT 365*21),[@Citizen]="Yes"),AND(([@Gender]="Female"),(TODAY()-[@[Date of Birth]] GT 365*18),[@Citizen]="Yes"),AND((TODAY()-[@[Date of Birth]] GT 365*24),[@Citizen]="No"))
Hi Chandoo,
Please accept my modified version.
=OR(AND(([@Gender]="Male"),(TODAY()-[@[Date of Birth]] GT 365*21),[@Citizen]="Yes"),AND(([@Gender]="Female"),(TODAY()-[@[Date of Birth]] GT 365*18),[@Citizen]="Yes"),AND((TODAY()-[@[Date of Birth]] GT 365*24),[Resident Since] LT 38718))
IF(AND(D12="No",VALUE((TODAY()-C12)/365)>=24,E12>=1/1/2006),"Yes",IF(AND(D12="Yes",B12="Male",VALUE((TODAY()-C12)/365)>=21),"Yes",IF(AND(D12="Yes",B12="Female",VALUE((TODAY()-C12)/365)>=18),"Yes","No")))
The formula to solve the problem is as under :
=IF(OR(AND(@Gender="M",(TODAY()-@Date Of Birth)/365 GTE 21),AND(@Gender="F",(TODAY()-@Date Of Birth)/365 GTE 18),AND(@Resident Since GTE "01-01-2006",(TODAY()-@Date Of Birth)/365 GTE 24)),"Yes","No")
I am not a big expert, but I have tried to solve the problem. GTE is used for Greater than or equal to and GT is used for Greater Than as advised by you
I have noticed an error in the formula sent by me. Am working on it and will resend after correction. Kindly Excuse.
Hi Chandoo,
nice challenge which very much reminded me of Daniel Ferry's amazing post about I HEART IF.
I wanted to avoid nested IFs by any mean so I chose his approach by applying the numerical values of 0 and 1 for FALSE and TRUE and simply add up all three preconditional stages (male citizen, female citizen, non-citizen)
Here are the steps I took:
To apply readability to the formulas used I first created a few names. First set was the "variables" for the ages.
1. sumeria.age18 has been defined as =18*365
2. sumeria.age21 has been defined as =21*365
3. sumeria.age24 has been defined as =24*365
I could have used the absolute numbers as well but this helps the future use of this file a little more, because these definitions (CTRL+F3 for the name manager) help better understand what I was doing
Then I placed the cursor in the first row (4) and again hit CTRL+F3 to define another variable for "todays age in days":
4. age.in.days has been defined as =TODAY()-$D4
Now that I have these "readability issues" solved, it's time to create the formula itself. Here's how I approached it:
As several conditions need to be matched I applied AND - as my fellow Excel buddies did - for each of the three condition sets and added them up. I first checked for male citizens, then female citizens and then non-citizens
=AND([@Gender]="Male",age.in.days GTE sumeria.age21,[@Citizen]="Yes")+AND([@Gender]="Female",age.in.days GTE sumeria.age18,[@Citizen]="Yes")+AND([@Citizen]="no",age.in.days>=sumeria.age24,[@[Resident Since]]=sumeria.age24,[@[Resident Since]]<="01.01.2006")
Remarks: One could expand the naming part even further by applying names for the other checks - such as defining is.male as =[@Gender]="Male" - as well but that was a little "over the top" for me 😉
Hope you like my solution 🙂
Looks like WOrdpress has cut down some of my content, because I also added a breakdown of the evaluation for each condition set, so here's that missing part 😉
Here are the three parts broken down for each condition set:
MALE CITIZENS (first part, initiated by the = sign):
=AND([@Gender]="Male",age.in.days GTE sumeria.age21,[@Citizen]="Yes")
FEMALE CITIZENS (second part, initiated by the + sign):
+AND([@Gender]="Female",age.in.days GTE sumeria.age18,[@Citizen]="Yes")
NON-CITIZENS (third part, initiated by another + sign):
AND([@Citizen]="no",age.in.days>=sumeria.age24,[@[Resident Since]] LTE "01.01.2006")
I went with a few bits to say WHY they couldn't vote...
IF([@[Resident Since]]="",
IF([@Gender]="male",
IF(DATEDIF([@[Date of Birth]],TODAY(),"y") GTE 21,"Yes","No (male, too young)"),
IF([@Gender]="Female",IF(DATEDIF([@[Date of Birth]],TODAY(),"y") GTE 18,"Yes","No (female, too young)"))),
IF([@Citizen]="No",
IF([@[Resident Since]]<="1/1/2006",
IF(DATEDIF([@[Date of Birth]],TODAY(),"y") GTE 24,"Yes","No (non-citizen, too young)"),
"No (non-citizen, residency too short)"),
"No (not citizen and/or failed other rules)"))
One more without IFs
D2 = TODAY()
F2 = 1/1/2006
G2 = Male
=AND(($D$2-D4)/365 GTE MAX(24*(E4="No"),21*(E4="Yes")*(C4=$G$2),18*(E4="Yes")*(C4$G$2)),(E4="Yes")+(F4 LTE $F$2))
Regards
@Elias,
1) I know my formula is not perfect and in fact I gave up and stopped altering it after I saw David,s formula.
2) Please check your last formula for (line 46) voter:
-----------------------------------------------------
SV-000.043 Female 01/09/2001 Yes
-----------------------------------------------------
She is only 14.65 years old, therefore the result should read: FALSE.
Michael Avidan
ISRAEL
@Michael
I don't know how you are applying my formulas because my last formula and the one I replied to you in a previous comments return FALSE for that voter.
Regards
@Elias,
As there is a limit of replies in each post - with your permission after this reply, of mine, I will rest my case.
I do hope I didn't made any mistake in copying your formula nor changing the GTE respectively.
Here is a picture of row 46 voter + your formula + result (shoud be FALSE).
---------------------------------------------------------
http://s31.postimg.org/facmsw123/NONAME.png
---------------------------------------------------------
Michael Avidan
ISRAEL
@Elias,
Please forget about my 7:34 PM reply.
Check out the linked picture - where in row 7589 the result should be FALSE because the voter residency started on: 02/07/2001 which is prior to: 1/1/2006.
------------------------------------------------------
http://s31.postimg.org/cjht2qgwr/NONAME.png
------------------------------------------------------
Thanks for your patience,
Michael Avidan
ISRAEL
@Michael,
Sorry you were right. My last formula without IF was not correct. Here is the new version without IF, OR & AND.
((E4="No")*24+(E4="Yes")*(21*(C4=$G$2)+18*(C4$G$2)))*365<=($D$2-D4)*((E4="Yes")+(E4="No")*(F4<=$F$2))
Regards
Formula will be as follows
IF([@Gender]="Male",IF(AND([@Citizen]="Yes",[@Age]GTE21),"Yes",IF(AND([@Citizen]="No",[@Age]GTE24,[@[Resident Since]]LTEDATE(2006,1,1)),"Yes","No")),IF(AND([@Citizen]="Yes",[@Age]GTE18),"Yes",IF(AND([@Citizen]="No",[@Age]GTE24,[@[Resident Since]]GTEDATE(2006,1,1)),"Yes","No")))
Note: GTE Greater Than or Equal to & LTE Less Than or Equal to
For Age Calculation I have used following Formula:
INT(YEARFRAC([@[Date of Birth]],TODAY()))
@Elias,
With all due respect this is my last check/response as for your formula(s).
I hope Chandoo will check all the suggestions and reply accordingly.
Your last formula returns FALSE for Voter: SV-000.004 although he is a Male & a Citizen & Older than 24 years.
To my opinion - an Equal sign (=) is missing within the: C4$G$2 section.
I have added it in order for the formula to work.
Kind regards,
Michael Avidan
ISRAEL
@Michael, I didn't notice the missing sign. It is a different than . Also, my formula returns exactly the same results as David N formula for all the records.
Regards
OPTION 1
=IF(E11="Sumeria",IF(OR(AND(C11="Male",TODAY()-D11 GT 365*21),AND(C11="Female",TODAY()-D11 GT 365*18)),"Eligible","Not Eligible"),IF(AND(TODAY()-D11 GT 365*24,F11 LT DATE(2006,1,1)),"Eligible","Not Eligible"))
OPTION 2 (Using SUMPRODUCT)
=IF(OR(SUMPRODUCT((C10="Male")*(E10="Sumeria")*(TODAY()-D10 GT 365*21)),SUMPRODUCT((C10="Female")*(E10="Sumeria")*(TODAY()-D7 GT 365*18)),SUMPRODUCT(((E10LTGT"Sumeria")*(TODAY()-D10 GT 365*24)*(F10LTDATE(2006,1,1))))),"Eligible","Not Eligible")
=(IF(AND(C4="Male",DATEDIF(D4,TODAY(),"Y")>=21),"Y",(IF(AND(C4="Female",DATEDIF(D4,TODAY(),"Y")>=18),"Y",IF(E4="No",IF((DATEDIF(D4,TODAY(),"Y")>=24),"Y","N"),"N")))))
=(((TODAY()-D4)/365GTE21)*(C4="Male")+((TODAY()-D4)/365GTE18)*(C4="Female"))*(E4="Yes")+((TODAY()-D4)/365GTE24)*(F4<=DATE(2006,1,1))
=IF(F4="",IF(OR(AND(C4="Male",(TODAY()-D4)/365GE21),AND(C4="Female",(TODAY()-D4)/365GE18)),"Yes","No"),IF(AND(F4LE38718,(TODAY()-D4)/365GE24),"Yes","No"))
My suggested solution:
=+IF(OR(AND([@Citizen]="Yes",[@Gender]="Male",DATEDIF([@[Date of Birth]],TODAY(),"y")GTE21),AND([@Citizen]="yes",[@Gender]="Female",DATEDIF([@[Date of Birth]],TODAY(),"y")GTE18),AND([@Citizen]="No",DATEDIF([@[Date of Birth]],TODAY(),"y")GTE24,[@[Resident Since]]LTEDATE(2006,1,1))),"Yes","No")
=IF((--(voters[@Citizen]="Yes")*(--(OR(AND(voters[@Gender]="male",((TODAY()-voters[@[Date of Birth]])/365) GT21),AND(voters[@Gender]="Female",((TODAY()-voters[@[Date of Birth]])/365) GT24)))))+(--(voters[@Citizen]="No")*(--(AND("01-01-2006"GTvoters[@[Resident Since]],((TODAY()-voters[@[Date of Birth]])/365)>21))))=1,"Yes","No")
IF(AND(E4="Yes",C4="Male",((TODAY()-D4)/365)>=21),"Y",IF(AND(E4="Yes",C4="feMale",((TODAY()-D4)/365)>=18),"Y",IF(AND(E4="No",((TODAY()-D4)/365)>=24,F4>=1/1/2006),"Y","N")))
Suggested solution:
=IF([@Citizen]="Yes",IF([@Gender]="Male",IF((TODAY()-[@[Date of Birth]])/365GTE21,"Yes","No"),IF((TODAY()-[@[Date of Birth]])/365GTE18,"Yes","No")),IF([@[Resident Since]]GTE38718,"No",IF((TODAY()-[@[Date of Birth]])/365GTE24,"Yes","No")))
or:
=IF(OR(AND([@Citizen]="Yes",[@Gender]="Male",(TODAY()-[@[Date of Birth]])/365GTE21),AND([@Citizen]="Yes",[@Gender]="Female",(TODAY()-[@[Date of Birth]])/365GTE18),AND([@Citizen]="No",[@[Resident Since]]<=38718,(TODAY()-[@[Date of Birth]])/365GTE24)),"Yes","No")
=IF(@Citizen="No",
IF(@Residentsince>DATE(2006,1,1),"No Foreign Young",IF(TODAY()-@0,"Yes","No"))
=IF(voters[[#This Row],[Citizen]]="Yes",OR(AND(voters[[#This Row],[Gender]]="Male",((TODAY()-voters[[#This Row],[Date of Birth]])/365)>21),AND(voters[[#This Row],[Gender]]="Female",((TODAY()-voters[[#This Row],[Date of Birth]])/365)>18)),OR(((TODAY()-voters[[#This Row],[Date of Birth]])/365)>=24,voters[[#This Row],[Resident Since]]<DATE(2006,1,1)))
=AND(OR([@Citizen]="Yes",[@[Resident Since]]<=DATE(2006,1,1)),YEARFRAC([@[Date of Birth]],TODAY(),3)>=IF([@Citizen]="No",24,IF([@Gender]="Male",21,18)))
=IF(((E4="yes")*((C4="Male")*(((TODAY()-D4)/365)>21))+((C4="Female")*(((TODAY()-D4)/365)>18)))+((E4="No")*(((TODAY()-D4)/365)>24)*(AND(F4"",F4>DATE(2006,1,1))))>0,"Yes","No")
=IF(AND(data!$E4="Yes",data!$C4="Male",YEARFRAC(data!$D4,TODAY(),3)>=21),"Can Vote",IF(AND(data!$E4="Yes",data!$C4="Female",YEARFRAC(data!$D4,TODAY(),3)>=18),"Can Vote",IF(AND(E4="No",YEARFRAC(D4,TODAY(),3)>=24,F4<38718),"Can Vote",0)))
Hi Chandoo,
Here's the formula I came up with:
=IF([@Citizen]="Yes",IF(AND([@Gender]="Male",((TODAY()-[@[Date of Birth]])/365)>=21),"Yes",IF(AND([@Gender]="Female",((TODAY()-[@[Date of Birth]])/365)>=18),"Yes","No")),IF(AND((TODAY()-[@[Date of Birth]])/365>=24,[@[Resident Since]]<=DATE(2006,1,1)),"Yes","No"))
=OR(AND([@[Resident Since]]>DATE(2015,12,31),DATEDIF([@[Date of Birth]],TODAY(),"y")>23),AND([@[Resident Since]]="",DATEDIF([@[Date of Birth]],TODAY(),"y")>17+N([@Gender]="Male")*3))
I did it, took me a while, I liked this one, 🙂
=IF(AND(voters[@Gender]="male",INT(TEXT(TODAY()-voters[@[Date of Birth]],"yy"))>21,voters[@Citizen]="Yes"),"YES",IF(AND(voters[@Gender]="Female",INT(TEXT(TODAY()-voters[@[Date of Birth]],"yy"))>18,voters[@Citizen]="Yes"),"YES",IF(AND(voters[@Citizen]="No",voters[@[Resident Since]]>DATE(2006,1,1)),"YES","NO")))
forgot to put =
there it is
=IF(AND([@Gender]="male",INT(TEXT(TODAY()-[@[Date of Birth]],"yy"))>=21,[@Citizen]="Yes"),"YES",IF(AND([@Gender]="Female",INT(TEXT(TODAY()-[@[Date of Birth]],"yy"))>=18,[@Citizen]="Yes"),"YES",IF(AND([@Citizen]="No",[@[Resident Since]]>=DATE(2006,1,1)),"YES","NO")))
=IF(AND([@Gender]="male",INT(TEXT(TODAY()-[@[Date of Birth]],"yy"))>=21,[@Citizen]="Yes"),"CAN VOTE",IF(AND([@Gender]="Female",INT(TEXT(TODAY()-[@[Date of Birth]],"yy"))>=18,[@Citizen]="Yes"),"CAN VOTE",IF(AND([@Citizen]="No",INT(TEXT(TODAY()-[@[Date of Birth]],"yy"))>=24,[@[Resident Since]]>=DATE(2006,1,1)),"CAN'T VOTE","CAN'T VOTE")))
=IF(AND(C88="Male",E88="Yes",((TODAY()-D88)/365)>=21),"Yes",IF(AND(C88="Female",E88="Yes",((TODAY()-D88)/365)>=18),"Yes",IF(AND(E88="No",((TODAY()-D88)/365)>=24,F88>=DATE(2006,1,1)),"Yes","No")))
I accidentally took the formula from row 88 lol
=IF(OR(AND(((TODAY()-TEXT([@[Date of Birth]],"mm/dd/yyyy"))/365)>=21,[@Gender]="Male",[@Citizen]="Yes"),(AND(((TODAY()-TEXT([@[Date of Birth]],"mm/dd/yyyy"))/365)>=18,[@Gender]="Female",[@Citizen]="Yes"))),"Yes",IF(AND([@Citizen]="No",TEXT([@[Resident Since]],"mm/dd/YYYY")>1/1/2006,((TODAY()-TEXT([@[Date of Birth]],"mm/dd/yyyy"))/365)>=24),"Yes","No"))
114 Female and 127 Male Non-Citizens Cannot vote
361 Female and 657 Male Citizens Cannot vote
227 Female and 255 Male Non-Citizens CAN vote
3050 Female and 2746 Male Citizens CAN vote
Update-Did not handle DOB correctly for Citizenry
=IF(OR(AND(((TODAY()-TEXT([@[Date of Birth]],"mm/dd/yyyy"))/365)>=21,[@Gender]="Male",[@Citizen]="Yes"),(AND(((TODAY()-TEXT([@[Date of Birth]],"mm/dd/yyyy"))/365)>=18,[@Gender]="Female",[@Citizen]="Yes"))),"Yes",IF(AND((TODAY()-(TEXT([@[Date of Birth]],"mm/dd/yyyy")))/365>24,DATE(2006,1,1)>=[@[Resident Since]]FALSE),"Yes","No"))
=IF(OR(AND([@Gender]="Male",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)GTE21,[@Citizen]="Yes"),AND([@Gender]="Female",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)GTE18,[@Citizen]="Yes"),AND([@Citizen]="No",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)GTE24,38718GTE[@[Resident Since]]))=TRUE,"YES","No")
I see most of the post did not include the "GTE", so I'm wondering if I did well to include it.
=IF([@Citizen]="No",IF(AND((TODAY()-[@DOB])/365>=24,[@[Resident Since]]=21),"Yes",IF(AND([@Gender]="Female",(TODAY()-[@DOB])/365>=18),"Yes","No")))
Wow! That is definitely not what I pasted into my comment!
Here is what I meant to post, with GTE and LTE instead of the symbols.
=IF([@Citizen]="No",IF(AND((TODAY()-[@DOB])/365 GTE 24,[@[Resident Since]] LTE DATE(2006,1,1)),"Yes","No"),IF(AND([@Gender]="Male",(TODAY()-[@DOB])/365 GTE 21),"Yes",IF(AND([@Gender]="Female",(TODAY()-[@DOB])/365 GTE 18),"Yes","No")))
=IF(OR(AND(C4="Female";E4="Yes";YEAR(TODAY())-YEAR(D4)>=18);AND(C4="Male";E4="Yes";YEAR(TODAY())-YEAR(D4)>=21);AND(E4="No";YEAR(TODAY())-YEAR(D4)>=24;TEXT(F4;"dd/mm/aaaa")>="01/01/2006"));"Can Vote";"Cannot")
=IF(E4="No",AND(E4&F4 LTE "No38718",(TODAY()-D4)/365.25 GTE 24),(TODAY()-D4)/365.25 GTE LOOKUP(E4&C4,{"N","YesF","YesM"},{24,18,21}))
A little modification
=(NOW()-C4)/365 GTE IF(D4="No",IF(E4 LE 38719,24),IF(B4="Male",21,18))
By the way, credit to that last one should go to my friend and colleague Debraj. I just took out a few characters from his formula. 😀
Here's my final version with 60 characters, but not with IF
=(NOW()-C4)/365 GTE LOOKUP(D4&B4,{"N","YesF","YesM"},{24,18,21})
On further analysis, realized that my last formula is flawed. But good challenge Chandoo
=IF(E3="No",IF(AND((TODAY()-D3)>8760,F3>1/1/2006),"Yes","No"),IF(C3="Male",IF((TODAY()-D3)>7665,"Yes","No"),IF((TODAY()-D3)>6570,"Yes","No")))
C3 = Male/Female Column
D3 = DOB
E3 = Citizen
F3 = Resident Date
No Ifs used
=CHOOSE(1+(([@Citizen]="yes")*([@Gender]="Male")*(TODAY()-[@[Date of Birth]] GT 21*365))+(([@Citizen]="yes")*([@Gender]="Female")*(TODAY()-[@[Date of Birth]] GT 18*365))+(([@Citizen]="No")*(TODAY()-[@[Date of Birth]] GT 24*365)*([@[Resident Since]] LTE DATEVALUE("01/01/2006"))),"No","Yes")
=IF(E4="no",IF(YEARFRAC(D4,TODAY())>=24,"yes","no"),IF(C4="male",IF(YEARFRAC(D4,TODAY())>=21,"yes","no"),IF(C4="female",IF(YEARFRAC(D4,TODAY())>=18,"yes","no"))))
C4 = Gender
D4 = Date
E4 = Citizen
Revised:
=IF(E4="no",IF(AND(YEARFRAC(D4,TODAY())>=24,(F4=21,"yes","no"),IF(C4="female",IF(YEARFRAC(D4,TODAY())>=18,"yes","no"))))
C4 = Gender
D4 = Date of Birth
E4 = Citizen
F4 = Resident Since
hi
What is the formula to add "/" symbol between date month and year automatically?
e.g 04072016 (4th July 2016) automatically converted to 04/07/2016 ??
pls help
thanks in advance
@Rakesh
If the cell contains a true date you can use a Custom Number Format such as dd/mm/yyyy
If the cell contains text, which i suspect it does
Use a helper column and put =DATE(RIGHT(A2,4),MID(A2,3,2),LEFT(A2,2))
Then apply a Custom Number Format such as dd/mm/yyyy
Then copy/paste as values
or you can use Text To Columns
Select the text
Goto the data Tab, select Text to Columns
Select Delimitered
Next
Next
Select date, then select DMY
apply
@Rakesh,
If all you need is for displaying the dates but NOT for calculations and you want to see: 04/07/2016 upon hitting 'Enter' after typing: 04072016 – try to 'Custom Format' those cells with the following pattern:
00\/00\/0000
*** The symbols, between the 'Zeros', are not the character V but one 'Back slash' & one 'Forward slash'.
Thanks a ton Hui..
really I was looking for this type of formula for the last 5-6 Months.
during this I got some related formulas but problem was not solved according to my requirements (dd/mm/yyyy) format.
But with this one I am fully satisfied and my long-standing problem is resolved now.
Thanks a lot again dear Hui..
I would also like to thanks candoo.org
Thanks.
sorry
its chandoo.org not candoo.org
sorry
Thank you very much dear Micky for your helpful tips. This is wonderful
when it is only for displaying and not for calculating.
Thanks again
@Rakesh,
You are more than welcome.
If you need to perform calculations use a helper-column and type: =TEXT(A1,"00\/00\/0000") (Same pattern as above)
=IF(OR(AND([@Gender]="Male",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=21,[@Citizen]="Yes"),AND([@Gender]="Female",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=18,[@Citizen]="Yes"),AND([@Citizen]="No",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=24,[@[Resident Since]]>38718))=TRUE,"Can Vote","Can not Vote")
I don't know if this formula is perfect, but it gave me satisfactory answer.
=IF(OR(AND([@Gender]="Male",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=21,[@Citizen]="Yes"),AND([@Gender]="Female",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=18,[@Citizen]="Yes"),AND([@Citizen]="No",ROUNDDOWN((TODAY()-[@[Date of Birth]])/365,0)>=24,[@[Resident Since]]<38718))=TRUE,"Can Vote","Can not Vote")
the earlier formula had a minor mistake, kindly comment if this formula is correct.
Hello friends
I am a bit new on this forum still I have used my very tiny brain to solve this challenge for The great Chandoo, think it works fine for the problem
=IF(OR(AND(C2="MALE",DATEDIF(D2,TODAY(),"Y")GTE21,E2="YES"),AND(C2="FEMALE",DATEDIF(D2,TODAY(),"Y")GTE18,E2="YES"))),"YES",IF(AND(E2="NO",DATEDIF(D2,TODAY(),"Y")GTE24,DATEDIF(F2,TODAY(),"Y")GTE10,)"YES","NO"))
=IF(OR(AND([@Citizen]="yes",[@Gender]="Male",(TODAY()-[@[Date of Birth]])>=7665),AND([@Citizen]="yes",[@Gender]="Female",(TODAY()-[@[Date of Birth]])>=6570),AND([@Citizen]="No",(TODAY()-[@[Date of Birth]])>=8760,(TODAY()-IF([@[Resident Since]]="",0,[@[Resident Since]]))>=4089)),"Yes","No")
=IF(OR(AND(voters[@Citizen]="Yes",voters[@Gender]="Male",DATEDIF(voters[@[Date of Birth]],TODAY(),"Y")>=21),AND(voters[@Citizen]="Yes",voters[@Gender]="Female",DATEDIF(voters[@[Date of Birth]],TODAY(),"Y")>=18),AND(voters[@Citizen]="No",DATEDIF(voters[@[Date of Birth]],TODAY(),"Y")>=24,voters[@[Resident Since]]>DATE(2006,1,1))),"Can Vote","Can't Vote")
=IF(AND([@Gender]="Male",YEAR(TODAY())-YEAR([@[Date of Birth]]) GTE 21,[@Citizen]="Yes"),"Yes",IF(AND([@Gender]="Female",YEAR(TODAY())-YEAR([@[Date of Birth]]) GTE 18,[@Citizen]="Yes"),"Yes",IF(AND(YEAR(TODAY())-YEAR([@[Date of Birth]]) GTE 24,[@Citizen]="No",[@[Resident Since]]<=DATE(2006,1,1)),"Yes","No")))
=IF(AND([@Gender]="Male",YEAR(TODAY())-YEAR([@[Date of Birth]])>=21,[@Citizen]="Yes"),"Yes",IF(AND([@Gender]="Female",YEAR(TODAY())-YEAR([@[Date of Birth]])>=18,[@Citizen]="Yes"),"Yes",IF(AND(YEAR(TODAY())-YEAR([@[Date of Birth]])>=24,[@Citizen]="No",[@[Resident Since]]<=DATE(2006,1,1)),"Yes","No")))