We’ll use advanced filtering to start thinking about predictive analytics in Excel Video 394. First we’ll compress the formulas that took four columns in Excel Video 393 to one column in Excel Video 394 by using the OR and AND functions. The formulas look complex, but once you understand how OR and AND work, the formulas aren’t too bad. It might help to know that you can separate components to sum with commas, such as SUM(5,4,2)=11.
Also watch for a brief discussion of predictive analytics. I’ll describe a way to weight no-show criteria so that factors that are more likely to lead to a patient no-show get a higher score. You can use the SUM function to assign a score that predicts the probability of a no show to each appointment. Appointments with a no-show score above a certain amount can then be double-booked, get an extra reminder or otherwise worked to optimize providers’ time in the clinic.
