3

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.

2

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. – ecerulm 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .