• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find out the previous date to a date if the criteria matches

anishms

Member
Hi Members,

Hope your near and dear ones are safe

I would seek your help in finding out the previous date to a date if the selected criteria matches
I have two sheets in the attached worksheet, wherein first sheet 'Follow-up Visit' refers to a clinic's bills to patients as free follow-up and the second sheet 'Paid Visit' refers to a paid bill to the patients. The practice followed in the clinic is free follow-up of 7 days from a paid visit date. I have found an array formula to find out the same (I have not used CSD ctrl+shift+enter as I'm using excel 365 with updated dynamic array functions). But here in the formula i also need to check criteria selected in the column K in sheet 'Follow-up Visit'.
For example, if doctor name is selected, the previous paid visit date should be paid visit for the particular doctor and
if Specialty is selected, previous date should be if the specialty matches irrespective of the doctors.
 

Attachments

  • Followup Visit Checker.xlsx
    362.6 KB · Views: 11
I have the option add a column in both the sheets to concat patient id and doctor name / specialty and then compare, but that won't be looking good.
 
Look at the attached and advise if this works for you. I merged your two files with Power Query
 

Attachments

  • Followup Visit Checker.xlsx
    703 KB · Views: 4
Look at the attached and advise if this works for you. I merged your two files with Power Query
Thanks for your effort. But, unfortunately this is not what i wanted
I would like to use this as a template for my future comparison as well where the data will keep on changing
 
The presentation can vary with taste. I have chosen to convert the data to tables and perform the calculation record by record. With MAXIFS, it is also possible to carry out the calculation outside the tables as a single spilt array. The alternative of using FILTER would required record by record calculation.
Code:
= MAXIFS( PaidVisit[Bill Date],
  PaidVisit[Bill Date], "<="&[@[Bill Date]],
  PaidVisit[Patient No.], [@[Patient No.]],
  PaidVisit[Speciality],  [@Speciality] )
 

Attachments

  • Followup Visit Checker.xlsx
    421.1 KB · Views: 9
The presentation can vary with taste. I have chosen to convert the data to tables and perform the calculation record by record. With MAXIFS, it is also possible to carry out the calculation outside the tables as a single spilt array. The alternative of using FILTER would required record by record calculation.
Code:
= MAXIFS( PaidVisit[Bill Date],
  PaidVisit[Bill Date], "<="&[@[Bill Date]],
  PaidVisit[Patient No.], [@[Patient No.]],
  PaidVisit[Speciality],  [@Speciality] )
Thanks!
I have made a small modification in the formula by using indirect function to activate the filter option
= MAXIFS( PaidVisit[Bill Date],
PaidVisit[Bill Date], "<="&[@[Bill Date]],
PaidVisit[Patient No.], [@[Patient No.]],
INDIRECT("PaidVisit["&$K$1&"]"), INDIRECT("followup_visit[@"&$K$1&"]"))
 
I would suggest CHOOSE rather than INDIRECT to avoid volatility it what is a pretty sizable calculation.
My solution was somewhat more primitive in that I used IF to select the appropriate formula.
Code:
= LET(
  dateBySpeciality,
    MAXIFS( PaidVisit[Bill Date],
    PaidVisit[Bill Date], "<="&FollowUp[Bill Date],
    PaidVisit[Patient No.], FollowUp[Patient No.],
    PaidVisit[Speciality], FollowUp[Speciality] ),
  dateByPractitioner,
    MAXIFS( PaidVisit[Bill Date],
    PaidVisit[Bill Date], "<="&FollowUp[Bill Date],
    PaidVisit[Patient No.], FollowUp[Patient No.],
    PaidVisit[Doctor Name], FollowUp[Doctor Name] ),
  IF(option="Speciality",
    dateBySpeciality,
    dateByPractitioner) )
 
I would suggest CHOOSE rather than INDIRECT to avoid volatility it what is a pretty sizable calculation.
My solution was somewhat more primitive in that I used IF to select the appropriate formula.
Code:
= LET(
  dateBySpeciality,
    MAXIFS( PaidVisit[Bill Date],
    PaidVisit[Bill Date], "<="&FollowUp[Bill Date],
    PaidVisit[Patient No.], FollowUp[Patient No.],
    PaidVisit[Speciality], FollowUp[Speciality] ),
  dateByPractitioner,
    MAXIFS( PaidVisit[Bill Date],
    PaidVisit[Bill Date], "<="&FollowUp[Bill Date],
    PaidVisit[Patient No.], FollowUp[Patient No.],
    PaidVisit[Doctor Name], FollowUp[Doctor Name] ),
  IF(option="Speciality",
    dateBySpeciality,
    dateByPractitioner) )
Thanks!
I don't have the LET function in my excel
Can you help me using Choose function
 
Last edited:
This version follows up the idea of using CHOOSE. The LET function is in the process of being released for versions of 365. With LET

Code:
= LET(
    criterionRange,
      CHOOSE(XMATCH(option, {"Speciality","Doctor Name"}),
         PaidVisit[Speciality],
         PaidVisit[Doctor Name]),
    criterionValue,
      CHOOSE( XMATCH(option, {"Speciality","Doctor Name"}),
         FollowUp[Speciality],
         FollowUp[Doctor Name] ),
    MAXIFS( PaidVisit[Bill Date],
       PaidVisit[Bill Date], "<="&FollowUp[Bill Date],
       PaidVisit[Patient No.], FollowUp[Patient No.],
       criterionRange, criterionValue )
   )
Without LET, one simply moves the formula for the 'criterionRange' and 'criterionValue' to become defined names. This leaves the formula
Code:
= MAXIFS( PaidVisit[Bill Date],
    PaidVisit[Bill Date], "<="&FollowUp[Bill Date],
    PaidVisit[Patient No.], FollowUp[Patient No.],
    criterionRange, criterionValue )

https://techcommunity.microsoft.com/t5/excel-blog/announcing-let/ba-p/1233572
 

Attachments

  • Followup Visit Checker.xlsx
    324.3 KB · Views: 8
Another thought. Since there are only two options IF can be used in place of CHOOSE
Code:
= IF(option="Speciality", PaidVisit[Speciality],  PaidVisit[Doctor Name])
 
Another thought. Since there are only two options IF can be used in place of CHOOSE
Code:
= IF(option="Speciality", PaidVisit[Speciality],  PaidVisit[Doctor Name])
This version follows up the idea of using CHOOSE. The LET function is in the process of being released for versions of 365. With LET

Code:
= LET(
    criterionRange,
      CHOOSE(XMATCH(option, {"Speciality","Doctor Name"}),
         PaidVisit[Speciality],
         PaidVisit[Doctor Name]),
    criterionValue,
      CHOOSE( XMATCH(option, {"Speciality","Doctor Name"}),
         FollowUp[Speciality],
         FollowUp[Doctor Name] ),
    MAXIFS( PaidVisit[Bill Date],
       PaidVisit[Bill Date], "<="&FollowUp[Bill Date],
       PaidVisit[Patient No.], FollowUp[Patient No.],
       criterionRange, criterionValue )
   )
Without LET, one simply moves the formula for the 'criterionRange' and 'criterionValue' to become defined names. This leaves the formula
Code:
= MAXIFS( PaidVisit[Bill Date],
    PaidVisit[Bill Date], "<="&FollowUp[Bill Date],
    PaidVisit[Patient No.], FollowUp[Patient No.],
    criterionRange, criterionValue )

https://techcommunity.microsoft.com/t5/excel-blog/announcing-let/ba-p/1233572
I just compared it with my previous function and I think maxifs it is not working properly. Please check the patient id 11138
change the option to specialty, the formula is taking the date of a different specialty
 
Sorry. It appears I was leading you astray when I suggested the defined names. Evaluate Formula shows everything as being correct, but only the first cell within the range is used in the MAXIFS formula (though it does work correctly in COUNTA?).
When I nest the range references within the MAXIFS formula, rather than using names, they appear to evaluate correctly.
Code:
= MAXIFS( PaidVisit[Bill Date],
    PaidVisit[Bill Date], "<="&FollowUp[Bill Date],
    PaidVisit[Patient No.], FollowUp[Patient No.],
    IF( option="Speciality", PaidVisit[Speciality], PaidVisit[Doctor Name] ),
    IF( option="Speciality", FollowUp[Speciality],  FollowUp[Doctor Name] ) )
Look out for LET. It could change spreadsheets out of all recognition!
 

Attachments

  • Followup Visit Checker.xlsx
    324.3 KB · Views: 1
Very interesting behaviour. I can't find any documentation on this, nor can I imagine why the simple act of defining criterionValue as a Name would force it to resolve to just its first element only within the main formula.

FWIW, we can coerce the correct array return, viz, for example, replacing

criterionValue

with

IF(1,criterionValue)

within the main formula.

Not sure why such coercion is required here. One for Lori, I believe! :)

Regards
 
Whatever the reason, we can be certain that the new dynamic array functionality plays a part: the 'non-dynamic' (and also non-CSE, FWIW):

=INDEX(MAXIFS(PaidVisit[Bill Date],PaidVisit[Bill Date],"<="&FollowUp[Bill Date],PaidVisit[Patient No.],FollowUp[Patient No.],criterionRange,criterionValue),ROWS(W$3:W3))

in row 3 and then copied down, gives correct results, which confirms that the array generated by MAXIFS and passed to INDEX in the above is processing all entries within criterionValue, not just the first.

Could this even be an undocumented bug related to the new dynamic array functionality? Further investigation required, in any case.

Regards
 
@XOR LX Maybe we need Lori back to use his Macro language knowledge in order to test whether
IF(1,criterionRange)
is a multi-cell range reference or an array!

It works in MAXIFS though, so I guess that proves my expectation that it would be an array wrong!
Code:
= MAXIFS( PaidVisit[Bill Date],
    PaidVisit[Bill Date], "<="&FollowUp[Bill Date],
    PaidVisit[Patient No.], FollowUp[Patient No.],
    IF(1, criterionRange),  IF(1, criterionValue) )

The catch with LET, though, is to know when to stop. The code
Code:
= LET(
    criterionRange, IF(option="Speciality", PaidVisit[Speciality], PaidVisit[Doctor Name] ),
    criterionValue, IF(option="Speciality", [@Speciality], [@[Doctor Name]] ),
    previousDate,
       MAXIFS( PaidVisit[Bill Date],
         PaidVisit[Bill Date], "<="&[@[Bill Date]],
         PaidVisit[Patient No.], [@[Patient No.]],
         criterionRange, criterionValue ),
   elapsed, IF( previousDate, [@[Bill Date]] - previousDate, "First vist" ),
   IF(elapsed<=7, "No fee", [@[Gross Amount]]-[@[Discount Amount]]) )
goes on to calculate the number of days elapsed, tests the result, and calculates the fee if it is not waived. All in one formula!

"undocumented bug"
It works the same as nesting the formulas in-line, so I am happy with it!
 

Attachments

  • Followup Visit Checker.xlsx
    339.7 KB · Views: 3
Maybe try

=IFERROR(1/(1/MAXIFS(Paid_Bill_Date,Paid_Bill_Date,"<="&$C$3:$C$2258,Paid_Patient_No.,$A$3:$A$2258)),"")
 

Attachments

  • Followup Visit Checker.xlsx
    386.2 KB · Views: 3
Back
Top