Forum Discussion
Not able to create a double unary formula on Excel for Mac v16.36
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) )
yes you need the N if B2 contains anything other than zero
What is in B2?
What result do you expect?
What result do you get?
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.
- "you need -- on the first sumproduct parameter but not on any of the others."
Can you please explain why? Just in case, if row 3 is occupied as
it could be
=(B14-LOOKUP(B14,$A$4:$A$8))*LOOKUP(B14,$A$4:$A$8,B4:B8)+SUMPRODUCT(($A$5:$A$8<B14)*($A$5:$A$8-$A$4:$A$7)*B4:B7)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) )
