Forum Discussion
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})
I keep getting the "There's a problem with this formula" pop-up. The formula is correct and works perfectly fine in Google Sheets.
What am I doing wrong? Thank you.
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) )
15 Replies
Excel does not allow range references inside { } - you can only put constants inside the {} syntax.
You need to convert your formula to an Excel-style array formula. If you have Dynamic Array Excel it will automagically be enetered as an array formula, otherwise you need to use Control-Shift-Enter to enter the bformula
Thank you. Would you kindly walk me through the steps? I would be very grateful.
- 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})
