# What's the Numbers equivalent to Excel's "A1:A10-B1"

I'm switching from Microsoft Excel to Number.app, in Excel you can do

``````=SUMPRODUCT((A1:A5-A1)^2,B1:B5)
``````

the `a1:a10-b1` bit will return a cell range equivalent to `a1:a10` but with all the cells decremented by `b1`. What's the proper syntax in Numbers for that?

If I try to import an Excel workbook using that syntax into Numbers, that formula will be imported verbatim and Numbers will complain.

In numbers, SUMPRODUCT (range, range…) takes the following arguments:

range: A range of cells. range is a reference to a single range of cells containing values of any type. If string or Boolean values are included in range, they are ignored.

range…: Optionally include one or more additional ranges of cells. The ranges must all have the same dimensions.

So you'll need to set up a range to perform the initial calculation (and squaring) and then sumproduct two simple ranges.

• Also, Excel on the Mac was originally awesome, and although some of the last 8 years of versions were poor - the latest version is quite excellent as a Mac app as well as in implementing most of what a Windows user might expect like pivot tables and super complicated flexibility.
– bmike
Apr 13 '13 at 20:32
• well, I already know how to do it by having "extra" cell ranges with the intermediate results. I was wondering if there was something similar to the excel "shortcut" notation for it. Apr 15 '13 at 20:31
• There isn't but I wanted to at least explain why. The site doesn't work well when someone answers a question as "No." I suppose I could have commented and asked why you thought that this was something numbers could do, but that's also not so constructive.
– bmike
Apr 15 '13 at 22:57