Get an Excel formula that survives contact with your actual messy spreadsheet, not just the happy path

Act as a senior data analyst who has been burned before by Excel formulas that work perfectly in a demo and break the moment someone opens the real workbook. Build me a formula for the task below, and stress-test it before handing it back.

WHAT THE FORMULA SHOULD CALCULATE
The average order value for each region, excluding any orders marked as refunded, ignoring blank rows.

COLUMN LAYOUT
Column B = region, Column D = order status, Column F = order amount, data starts row 2, roughly 3,000 rows.

EXCEL VERSION
Microsoft 365 desktop, so dynamic array functions like FILTER and UNIQUE are available.

KNOWN MESSINESS IN THE SHEET
Column D sometimes has 'Refunded' and sometimes 'refunded' typed manually, and roughly 40 rows have a blank order amount pending finance entry.

Do the following in order:
1. Write the formula.
2. Stress-test it yourself, in writing, against these specific failure modes before presenting it as final: blank cells in any referenced range, text accidentally sitting in a numeric column, a #N/A or #REF! propagating from an upstream cell, and (if relevant to the version given) whether it needs to be an array formula or will spill automatically.
3. If the formula as first written would break under any of those conditions, revise it and show the fixed version, explaining what changed and why.
4. Note any behavior difference if this workbook might later be opened in an older Excel version or Google Sheets.

WHAT NOT TO DO
Do not hand back a formula that only works on clean, complete data without saying so — if a genuinely more robust version exists but is meaningfully more complex, give me both and let me choose, rather than silently picking simplicity over robustness or vice versa.

OUTPUT FORMAT
1. Final formula, ready to paste, with cell references matching the column layout given.
2. Plain-English explanation of what it does, one sentence per logical part.
3. Stress-test results: which failure modes it now handles and how, listed explicitly.
4. Compatibility note (older Excel / Google Sheets), only if relevant.