1

This is a small part of my web scraping code.

wb = xw.Book("jockeyclub.xlsx")
rc1 = wb.sheets['Race Card 1']

besttime = 7

rc1.range('S7').formula = f'=MIN(IF({besttime}1:{besttime}150<>"", {besttime}1:{besttime}150))'
    
rc1.range('AA7').formula = f'=MATCH(MIN(IF({besttime}1:{besttime}150<>"", {besttime}1:{besttime}150)), {besttime}1:{besttime}150, 0) + ROW({besttime}1) - 1'

it does print the following formulas in cells S7 and AA7, but it adds an "@" in the middle.

for cell S7, it prints

=MIN(IF(@BQ1:BQ150<>"", BQ1:BQ150))

for cell AA7, it prints

=MATCH(MIN(IF(@BQ1:BQ150<>"", BQ1:BQ150)), BQ1:BQ150, 0) + ROW(BQ1) - 1

The formulas are both not working due to the "@" in the middle. Why is it there and how can i get rid of it through code?

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
NNBananas
  • 115
  • 6
  • 1
    It is called **[Implicit-Intersection-Operator](https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34)** it is added because you are using a `Racon` function to return a single value. – Mayukh Bhattacharya Jul 22 '23 at 13:04
  • Thanks, is there any way I can get rid of the implicit intersection operator? – NNBananas Jul 22 '23 at 13:08
  • Can't say what you are trying to accomplish without a screenshot or a copiable data. There are some examples here in SO. Look **[here](https://stackoverflow.com/q/74510691/8162520)** also are you using earlier versions of Excel then you may hit `CTRL SHIFT ENTER` by removing the `@` manually – Mayukh Bhattacharya Jul 22 '23 at 13:16
  • Im writing a program through python that uses excel though (xlwings), so I cant do control shift enter. – NNBananas Jul 22 '23 at 13:19
  • But what is your Excel Version? That i have understood from the tags, its been done using `Python` so it totally depends on the code which you are writing and the version of excel you are using – Mayukh Bhattacharya Jul 22 '23 at 13:21
  • Im currently using excel version 16.75.2 – NNBananas Jul 22 '23 at 14:05
  • 1
    @NNBananas when you say formulas aren't working, what's happening? Is there a `#`type erroer in Excel? – bugdrown Jul 22 '23 at 14:23
  • @bugdrown it says #N/A – NNBananas Jul 22 '23 at 14:42

1 Answers1

1

Range.Formula uses "Implicitly Intersection Evaluation" which means the formula is not evaluated on a spill array basis. This fact is why Excel inserts the "@" into the formula in one or more places.

Range.Formula2 is the "array version" of inserting a formula. If you use it, insertion of the "@" should only occur if that is the only way the formula will make sense to Excel (and perhaps not even then).

This link is a good explanation by Microsoft as to what is happening. (It is in the family of Microsoft documentation that one does not expect to end up with a broken link, and the above is a summary of the important part.)

https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2

I cannot say how you access Range.Formula2 (or any of the other "xxxx.xxxxx2" functions) in the various languages/tools you tagged your question with, but I am dead positive they all have a way.

Jeorje
  • 26
  • 2