Formula Errors

How to Stop Excel from Adding @ to Dynamic Array Formulas

Emma Brown Oct 13, 2026 871 views

Question details

The user needs to prevent Excel from automatically inserting the @ symbol (implicit intersection operator) into a MEDIAN and IF array formula.

How to Stop Excel from Adding @ to a Dynamic Array Formula

Product: Microsoft Excel
Device & OS: not provided
Scenario: Entering a legacy array formula using MEDIAN and IF functions to calculate values based on conditions in Microsoft 365.
Observed behavior: Excel automatically inserts the @ symbol into the formula, which changes its calculation behavior and results in a #VALUE! error.

Before you start

Verify your version of Excel, as the @ implicit intersection operator and dynamic array behaviors are specific to Microsoft 365 and Excel 2021 or newer.

Solution 1Recommended

Replace the IF Function with the FILTER Function

The most robust way to avoid the implicit intersection operator (@) in Microsoft 365 is to replace legacy IF array constructs with the modern FILTER function.

Microsoft 365 handles arrays dynamically. When it detects a legacy array formula that might return multiple values to a single cell, it adds the @ symbol to force a single result (implicit intersection). Using the modern FILTER function inherently returns an array, completely avoiding this intersection conflict.

Select the target cell

Click on the cell where you want the median calculation to appear and delete the existing formula containing the @ symbol.

Enter the FILTER formula

Type the new formula using the FILTER function. For example, enter =MEDIAN(FILTER(B5:B13,C5:C13=C1)). Replace the ranges with your actual data.

Confirm the formula

Press the Enter key. Because Microsoft 365 supports dynamic arrays natively, you do not need to press Ctrl+Shift+Enter.

Replace the IF Function with the FILTER Function

Modern Formula Advantage: Using the FILTER function not only resolves the @ symbol issue but also calculates faster and is easier to read than legacy array formulas.

Solution 2

Use Legacy Array Confirmation

If you are opening the file in older versions of Excel or prefer to keep your original IF logic, you must explicitly define the formula as an array.

Use WPS Spreadsheet to Handle Array Formulas Seamlessly

WPS Office provides excellent support for advanced formulas, including modern array calculations and data filtering. You can seamlessly write, edit, and calculate complex dynamic arrays without struggling with legacy syntax issues.

1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx document.
2. Select the formula cell: Click the cell where you need to calculate the conditional median.
3. Input the array formula: Enter the modern dynamic formula, such as =MEDIAN(FILTER(B5:B13, C5:C13=C1)).
4. Calculate the result: Press Enter to instantly execute the dynamic array function and view the correct calculated result without errors.

100% compatible with Microsoft Excel (.xlsx) formats and legacy arraysFully supports modern dynamic array functions like FILTERFree, lightweight, and features a familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

What does the @ symbol mean in Excel formulas?

The @ symbol represents the implicit intersection operator. Excel automatically adds it to formulas that return an array of values when placed in a single cell, forcing the formula to extract and return only a single value to prevent the data from spilling into adjacent cells.

Why does my MEDIAN and IF formula return a #VALUE! error?

In Microsoft 365, if a legacy array formula is entered without Ctrl+Shift+Enter, Excel tries to evaluate it using implicit intersection (adding @). If the underlying data cannot logically be resolved to a single expected value for the MEDIAN function to process, it throws a #VALUE! error.

Can I just delete the @ symbol to fix the formula?

Simply deleting the @ symbol and pressing Enter will usually cause Excel to add it right back. To remove it permanently and fix the calculation, you must either rewrite the formula using dynamic array functions like FILTER or confirm your legacy formula by pressing Ctrl+Shift+Enter.

Excel & Data Problems