Forum Discussion
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}) ...
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) )
Can someone please help me convert this formula into an "Excel style array formula"? I do not think I have Dynamic Array Excel.
=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})
=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})
You can check for dynamic array version by seeing if you have the XLOOKUP function.
If B2 contains text or zero try this
=SUMPRODUCT(--(B16<$A3:$A7),--(B16-$A3:$A7),(B$3:B$7-N(B$2:B$6)) )