Forum Discussion

jayaramv's avatar
jayaramv
Copper Contributor
Jun 17, 2020
Solved

Not able to create a double unary formula on Excel for Mac v16.36

Hi,    I am trying to create a basic SUMPRODUCT formula in Excel for Mac v16.36:    =SUMPRODUCT(--(B16>{$A3,$A4,$A5,$A6,$A7}),--(B16-{$A3,$A4,$A5,$A6,$A7}),{B$3,B$4-B$3,B$5-B$4,B$6-B$5,B$7-B$6}) ...
excel
Excel on Mac
Formulas and Functions
  • fastexcel's avatar
    fastexcel
    Jun 17, 2020

    jayaramv 

    Thanks for the screenshot: I had a mistake in the formula and also forgot that N() does not return an array. And you don't need the N if B3 is zero.

    I think this formula gives you the correct result.

    You do not need to array-enter it since SUMPRODUCT always calculates in array fashion. Also you don't actually need the -- in --(B14-$A4:$A8) since there are no True/False to convert to 1s and zeros.

     

    =SUMPRODUCT(--(B14>$A4:$A8),--(B14-$A4:$A8),(B$4:B$8-B$3:B$7) )

jayaramv's avatar
jayaramv
Copper Contributor
Jun 18, 2020
"you need -- on the first sumproduct parameter but not on any of the others."

Can you please explain why?
fastexcel's avatar
fastexcel
Brass Contributor
Jun 19, 2020

jayaramv 

The first parameter has a > operator which means that the result of the expression will be an array of True or False. SUMPRODUCT will error with True/False so the True/False needs to be converted to numbers, which is done using the --. The other parameters do not have a comparison operator so do not need this.

Resources