Actuarial Outpost Excel: sum of squares(or any operation) on a set of cells.
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Search Actuarial Jobs by State @ DWSimpson.com:
AL AK AR AZ CA CO CT DE FL GA HI ID IL IN IA KS KY LA
ME MD MA MI MN MS MO MT NE NH NJ NM NY NV NC ND
OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY

#31
10-24-2018, 05:52 PM
 IANAE Member CAS AAA Join Date: Oct 2017 Posts: 215

OK. Since...why not?

mmult( transpose( a1:a4 ), a1:a4 )

ETA: entered as an array formula i.e., with CTRL+Shift+Enter

Last edited by IANAE; 10-24-2018 at 10:06 PM..
#32
10-25-2018, 10:25 AM
 Gareth Keenan Member CAS Join Date: Feb 2006 Location: Garden State Studying for #9 Favorite beer: butter Posts: 1,308

I find mmult a little confusing, why would you use this instead of a sumproduct or some other function?
#33
10-25-2018, 10:31 AM
 IANAE Member CAS AAA Join Date: Oct 2017 Posts: 215

Quote:
 Originally Posted by Gareth Keenan I find mmult a little confusing, why would you use this instead of a sumproduct or some other function?
MMult provides generalized array functionality e.g., as could be used for regression etc.
#34
10-25-2018, 12:04 PM
 dumples Member CAS Join Date: Sep 2003 Posts: 1,250

Quote:
 Originally Posted by Gareth Keenan This thread has gone off the rails in the most delightful of ways.
I agree, this is very entertaining. The only thing that I have to contribute is that ~12 years ago I needed a ProductIf formula and I used a sumproduct with LN's and EXP's. That was as clever as I've ever felt at work.
#35
10-29-2018, 10:31 PM
 Hedges Member SOA Join Date: Apr 2002 Location: I've been Shanghaied! Posts: 482

Quote:
 Originally Posted by DeepPurple I think the inner part as you call it returns a vector of length 1 which is different than a scalar. SUMPRODUCT can convert that vector into a scalar.
Thanks, DeepPurple. That makes sense.

It seems that the behaviour of Excel is inconsistent here. It's not so much that there is a problem with SUMPRODUCT as that the formula =MAX(A1:A4*(B1:B4=C1:C4)) should be able to work directly, without being entered as an array formula. if you enter it directly, you get a #VALUE! error. But you then evaluate it to try and find what is producing the error, you produce the correct value.

(Edited first line below to remove SUMPRODUCT, after TooOldForThis's post below made me realise I'd entered it incorrectly.)
=MAX(A1:A4*(B1:B4=C1:C4)) //produces error if entered directly
=MAX({1;2;3;4}*({TRUE;TRUE;TRUE;FALSE})) //1st step in F9 evaluation
=MAX({1;2;3;0}) //further evaluating
=3 //and finally (or you could evaluate the whole lot directly and come up with this straight away

Last edited by Hedges; 10-30-2018 at 09:46 PM.. Reason: Mistake in original entry: removed SUMPRODUCT from =SUMPRODUCT(MAX(A1:A4*(B1:B4=C1:C4))) //produces error if entered directl
#36
10-30-2018, 09:13 AM
 TooOldForThis Member Join Date: Nov 2001 Posts: 118

Quote:
 Originally Posted by Hedges Thanks, DeepPurple. That makes sense. It seems that the behaviour of Excel is inconsistent here. It's not so much that there is a problem with SUMPRODUCT as that the formula =MAX(A1:A4*(B1:B4=C1:C4)) should be able to work directly, without being entered as an array formula. if you enter it directly, you get a #VALUE! error. But you then evaluate it to try and find what is producing the error, you produce the correct value. =SUMPRODUCT(MAX(A1:A4*(B1:B4=C1:C4))) //produces error if entered directly =MAX({1;2;3;4}*({TRUE;TRUE;TRUE;FALSE})) //1st step in F9 evaluation =MAX({1;2;3;0}) //further evaluating =3 //and finally (or you could evaluate the whole lot directly and come up with this straight away
You don't get a value error if all the cells have numbers. If A1:A4=B1:B4= 1,2,3,4, and C1:C4=1,2,3,5 (to keep the T,T,T,False the same) then the formula evaluates to 2. Stepping through with F9 you get Max(2*(2=2))! Now that is odd.
#37
10-30-2018, 09:59 PM
 Hedges Member SOA Join Date: Apr 2002 Location: I've been Shanghaied! Posts: 482

Hi TooOldForThis. Oops. I had entered the incorrect first line. There should have been no enclosing SUMPRODUCT, i.e. instead of
=SUMPRODUCT(MAX(A1:A4*(B1:B4=C1:C4)))
=MAX(A1:A4*(B1:B4=C1:C4))

I've corrected the post.

But I think the behaviour of =MAX.... is even more odd than you or I initially reported. The results seems to depend where you enter the formula! The attachment below shows the numbers entered in columns A, B and C with the offending MAX formula entered separately (and not as an array formula) in rows 1-5 of column E. The formula in each row is exactly the same in each row. As you can see the identical formula produces a different result in depending on the row it's entered in.
Attached Images

Last edited by Hedges; 10-30-2018 at 11:26 PM..