Sumproduct Function

Question

Level 1

4 points

Sumproduct Function

I am porting a Sumproduct function from Excel to Numbers (v4.3) and would need help writing and formatting the statement/syntax in Numbers.

Excel Statement:

=SUMPRODUCT(--(B1>$A$1:$A$4);--(B1-$A$1:$A$4);$C$1:$C$4)

How would the Numbers statement and syntax look like for the Excel formula above?

I have 2 arrays/lists and 1 variable:

Array/List 1

Values

A1: 0%

A2: 10%

A3: 20%
A4: 30%

Array/List 2

Values

C1: 20%

C2: 15%

C3: 75%
C4: -15%

Variable

Value

B1: 22%

Appreciate it.

MacBook Pro with Retina display, macOS High Sierra (10.13), null

Posted on Oct 23, 2017 3:37 PM

Reply

Answer 1

SGIII

Level 8

37,562 points

Oct 24, 2017 7:15 AM in response to SGIII

The direct equivalent in Numbers would be this:

Columns A thru C are the values in your example.

The formula in D1, filled down to D4, is:

=IF($B$1>A1,1,0)

or =IF($B$1>A1;1;0) in your region.

The formula in E1, filled down to E4, is:

=$B$1−A1

The formula in E6 is:

=SUMPRODUCT(C1:C4,D1:D4,E1:E4)

or =SUMPRODUCT(C1:C4;D1:D4;E1:E4) in your region

SG

Reply

Answer 2

SGIII

Level 8

37,562 points

Oct 23, 2017 6:16 PM in response to mroceans

Numbers doesn't support most array functions (this form of SUMPRODUCT is an array function). Could you describe what the function is doing in your particular case, perhaps even with a screenshot? The general approach in Numbers will be to break the calculation into steps, using an extra column or columns, but I'm not sure I follow exactly what is happening in your example.

SG

Reply