Announcing LET

Ed Hansberry 

I look forward to seeing what you get up to with your new found freedom.

There are times when I wonder how far to push the idea.  For example, in response to a recent question on Chandoo I posted:

= 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 visit" ),
   IF(elapsed<=7, "No fee", [@[Gross Amount]]-[@[Discount Amount]]) )

The first lines just set one of the criterion ranges for the MAXIFS.  I then determine the last matching visit by a patient to establish whether the visit is a follow-up.  I then went on to calculate the days elapsed between the last appointment and tested it to determine whether it is within one week.  If so the fee is waived.  This would normally require a couple of helper ranges to make the formulas manageable but, with LET, the formula remains readable.

I think it may be a while before any consensus is achieved regarding best practice.

BTW. I confirmed that support for the "." within formula names had been intentionally withdrawn.  The formulas I wrote before the cut-off still work but there is no chance of writing similar formulas now.

lori_m 

The first two lines of the LET function work when defined within the function but not as defined names.  When the defined names are used within the MAXIFS function the criterion and value ranges truncate to a single cell.  To get round the problem, requires parameters of the form 

IF(1, criterionRange), IF(1, valueRange), 

Any thoughts on what is going on?