Forum Discussion
Excel Formula
Hello I am needing help with calculating a formula that will tell me if some is a resident, and over the age of 65 and has received 2 bivalent boosters (either moderna Bivalent or Pfixer bivalent) ...
Since the first bivalent booster or the last?
since first bivalent.
It's confusing. Does this do what you want?
=IF(AND(E2>65,G2="Resident"),LET(m,MATCH(1,(W2:AA2="Moderna")+(W2:AA2="Pfizer Bivalent Booster"),0),IF(ISNUMBER(m),IF(INDEX(X2:AB2,m)<EDATE(TODAY(),-4),"First bivalent booster more than 4 months ago","First bivalent booster 4 months ago or less"),"No bivalent booster")),"Not eligible")
I hope that someone else can help you.
- I will attach the workbook, might make it easier to see, so the second tab has the actual data and the 1st tab is a dashboard I am trying to create to essentially summarize what's going on. if possible what I would like to be calculated is for the vaccine portion to calculate a number that represents the number of residents that are "up to date" with vaccination and then a total that would include both staff and residents. to be considered "up to date" can be broken down in 2 ways, anyone who is under 65 years old AND has either 1 "pfizer or moderna bivalent" vaccine (this would be in the vaccine fields). if they are over 65 then they can be considered up to date in 2 ways either they have received 2 (either "pzifer or moderna bivalent" vaccines or they have received only one vaccine AND its within 4 months from todays date. if it doesnt make sense i get it!