Actuarial Outpost Excel: sum of squares(or any operation) on a set of cells.
 User Name Remember Me? Password
 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

 Enter your email to subscribe to DW Simpson weekly actuarial job updates. li.signup { display: block; text-align: center; text-size: .8; padding: 0px; margin: 8px; float: left; } Entry Level Casualty Health Life Pension All Jobs

 Thread Tools Search this Thread Display Modes
#31
10-24-2018, 06:52 PM
 IANAE Member CAS AAA Join Date: Oct 2017 Posts: 209

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 11:06 PM..
#32
10-25-2018, 11: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, 11:31 AM
 IANAE Member CAS AAA Join Date: Oct 2017 Posts: 209

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, 01:04 PM
 dumples Member CAS Join Date: Sep 2003 Posts: 1,249

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, 11:31 PM
 Hedges Member SOA Join Date: Apr 2002 Location: I've been Shanghaied! Posts: 476

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 10: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, 10: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, 10:59 PM
 Hedges Member SOA Join Date: Apr 2002 Location: I've been Shanghaied! Posts: 476

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)))
I should have had
=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-31-2018 at 12:26 AM..

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off

All times are GMT -4. The time now is 01:05 AM.

 -- Default Style - Fluid Width ---- Default Style - Fixed Width ---- Old Default Style ---- Easy on the eyes ---- Smooth Darkness ---- Chestnut ---- Apple-ish Style ---- If Apples were blue ---- If Apples were green ---- If Apples were purple ---- Halloween 2007 ---- B&W ---- Halloween ---- AO Christmas Theme ---- Turkey Day Theme ---- AO 2007 beta ---- 4th Of July Contact Us - Actuarial Outpost - Archive - Privacy Statement - Top

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.22512 seconds with 10 queries