Forum Discussion

Brass Contributor

Sep 06, 2023

Solved

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

Formulas and Functions

OliverScheurich
Sep 06, 2023
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

jaolvera

Brass Contributor

Sep 06, 2023

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.

OliverScheurich

Gold Contributor

Sep 06, 2023

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.

jaolvera
Brass Contributor
Sep 06, 2023
I get a spill error, when I evaluate the formula it shows the lambda portion is causing it?
- OliverScheurich
  Gold Contributor
  Sep 06, 2023
  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
  - jaolvera
    Brass Contributor
    Sep 06, 2023
    that worked ! thank you so much for the help! really appreciate it