3

I would like to ask regarding "@" in excel formulas, I have written formula below that calculated median based on certain conditions. The formula works when I write it manually directly in cells, but every time I try to populate the formula by VBA code below

ThisWorkbook.Sheets("Pivot").Range("S3").Formula = "=MEDIAN(IF($L$4:$L$" & x & "=$Q3,IF($M$4:$M$" & x & "=S$2,$N$4:$N$" & x & ")))"

the excel puts "@" in IF condition and then, it doesn't return vales but returns 0, if I remove the "@" the in formula it starts working again.

=MEDIAN(IF(@$L$4:$L$190=$Q3,IF(@$M$4:$M$190=R$2,$N$4:$N$190)))

I googled but could not find explanation of someone having same issue.

Improve this question
1
  • 2
    Since the new version of Excel including dynamic formulas you should use .formula2 instead of .formula Commented Jun 26, 2021 at 8:06

1 Answer 1

Reset to default
2

@ is a new feature called Implicit intersection designed to simplify Excel's Dynamic Arrays.

https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34

Your function will only check row 3 (which is empty) and so returns 0.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@ is not a new feature. It's a new symbol that from now on signifies Implicit Intersection - the old way the formulas worked. The new way the formulas work is Dynamic Arrays, which is the default now and thus does not have a symbol. Thus, the presence of @ means "You are viewing this historic formula in Excel 365, but it works like it used to in Excel 2016-".

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.