SOLVED

Formula for excel

Brass Contributor

Hello! I needed assistance with a formula that will be a "yes or no" in column A, that will determine if the dtcreate (column o)  within each group of patient IDs is within 28 days of each other. 

jaolvera_0-1694017169490.png

 

6 Replies

@jaolvera 

=IF(LARGE(IF($C$2:$C$13=C2,$O$2:$O$13-$P$2:$P$13),1)<=28,"Yes","No")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

patientid.png

 

hello! thank you, for the dates its only the dates in column o, so within each patient ID, there are multiple "date created" and within those I need to determine if any of them are within 28 days.

@jaolvera 

=BYROW(C2:C14,LAMBDA(x,IF(SMALL(IFNA(DROP(SORT(FILTER(O2:O14,C2:C14=x)),1)-SORT(FILTER(O2:O14,C2:C14=x)),0),2)<=28,"Yes","No")))

 

Does this return the intended result? I've added sample data where 2 dates in column O are within 28 days for a group of IDs.

within 28 days.png

I get a spill error, when I evaluate the formula it shows the lambda portion is causing it?
best response confirmed by Hans Vogelaar (MVP)
Solution

@jaolvera 

Did you enter the formula in cell A2? If you expect the formula to return results from cell A2 to cell A14 (as shown in the screenshot of my last reply) then select range A3:A14 and delete all data from these cells.

 

Here is additional information on how to correct a #SPILL error.

How to correct a #SPILL! error - Microsoft Support

that worked ! thank you so much for the help! really appreciate it
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@jaolvera 

Did you enter the formula in cell A2? If you expect the formula to return results from cell A2 to cell A14 (as shown in the screenshot of my last reply) then select range A3:A14 and delete all data from these cells.

 

Here is additional information on how to correct a #SPILL error.

How to correct a #SPILL! error - Microsoft Support

View solution in original post