Sumproduct + transpose + max

[moccasinus]

Hi, is there a way to modify formula {=SUMPRODUCT(Sheet2!K$2:K$242;TRANSPOSE($B38:$IH38))} for it not to multiply all together, but to return maximum value from array Sheet2!K$2:K$242?
Been trying various ways, multiple criteria index match etc., but none seem to work

 

[Fluff]

In that case I don't know. The only way I can get that formula to return #NUM is if that error is in one of the ranges

 

[moccasinus]

ok, thanks anyway

 

[Fluff]

If you remove the Max from the formula, what do you get? Make sure there are plenty of empty cells below the formula as it will spill

 

[moccasinus]

Without max this formula: {=SUMPRODUCT(Sheet2!K$2:K$242;TRANSPOSE($B38:$IH38))}
returns (Product1 in Sheet2!K$2:K$242 x Product1 in TRANSPOSE($B38:$IH38)) + (Product2 x Product2) + (Product3 x Product3)+ (Product4 x Product4) + ...
I'm trying to get MAX(Sheet2!K$2:K$242) to be multiplied by corresponding Product in TRANSPOSE($B38:$IH38)

 

[Fluff]

The formula I suggested does not use sumproduct.

 

[moccasinus]

=MAX(Sheet2!K$2:K$242*TRANSPOSE($B38:$IH38))
your formula without arrays returns #spill, with arrays #value

 

[moccasinus]

without arrays and without MAX

 

[Fluff]

That's why I said

Make sure there are plenty of empty cells below the formula as it will spill

 

[alansidman]

Have you considered using Power Query to unpivot your data?