initial looks

[Ted Craven]

Charlie - if you are comfortable enough in Excel to replace the existing retrieval formula in both the Filter Example and Leverage/Pace Sheets - following is a replacement formula which will return data from lines which have no ProfitLine or PrimePower values (i.e. blank). Somehow, during testing, we tested on races where those values were ZERO (= 0) but not blank 🤔.


Use a copy of your Model file (i.e. make a backup).


On the 2 retrieval sheets, in cell A13 (where the formula is stored), delete the existing formula and replace with the formula below.

If not comfortable doing that, I will issue an updated Model Sheet with the final public update next week which people can use. I haven't figured out the best advice/method yet to migrate existing Model sheets and formulas to an updated one (like this formula update/migration). I'll work on it.

--------------------------------------------------------------------------------
=IFERROR(UNIQUE(SORT(FILTER(datarange,
(finish>=$B$5) *
(IF($C$5="",finish=$B$5,finish<=$C$5)) *
(IF($B$7="",1,ISNUMBER(MATCH(surface,TRIM(TEXTSPLIT($B$7,",")),0)))) *
(distance>=$B$6) *
(IF($C$6="",distance=$B$6,distance<=$C$6)) *
(IF($B$8="",1,ISNUMBER(MATCH(track,TRIM(TEXTSPLIT($B$8,",")),0)))) *
(IFERROR(SWITCH($B$9,"W",LEFT(racetype,1)<>"M","M",SEARCH("M? ",racetype),"G",SEARCH("G? ",racetype),SEARCH($B$9,racetype)),0)) *
(total_energy>0) *
(adj_bl_final<99) *
(contender_group<99) *
((ISBLANK(primepower)) + ((primepower>0)*(primepower<=99))) *
((ISBLANK(profitline)) + (profitline<=99)) *
IF(B10<>"", IF(C10<>"", IF(B10=C10,date=B10, (date>=B10)*(date<=C10)), date>=B10), IF(C10<>"",date<=C10,1)),
"No records found!"), {1,2,3}, {-1,1,1})), "No data available!")
--------------------------------------------------------------------------------

Do this for both the Filter Example and Leverage/Pace sheets - cell A13. Then your missing AQU race date should populate in the models because we now test whether the PL or BPP values are actually blank (i.e. missing).

I hope that helps!

Ted