2

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.

JMP
  • 4,417
  • 17
  • 30
  • 41
Jakub
  • 23
  • 1
  • 3
  • 2
    Since the new version of Excel including dynamic formulas you should use `.formula2` instead of `.formula` – P.b Jun 26 '21 at 08:06

1 Answers1

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.

JMP
  • 4,417
  • 17
  • 30
  • 41
  • 2
    `@` 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-". – GSerg Jun 26 '21 at 13:21