SOLVED

# Formula for excel

Brass Contributor

# Formula for excel

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.

6 Replies

# Re: Formula for excel

``=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.

# Re: Formula for excel

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.

# Re: Formula for excel

=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.

# Re: Formula for excel

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

# Re: Formula for excel

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

# Re: Formula for excel

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

# Re: Formula for excel

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