Take the 2-minute tour ×
Ask Different is a question and answer site for power users of Apple hardware and software. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.