Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions



Reply
 
Thread Tools Search this Thread Display Modes
  #31  
Old 10-24-2018, 06:52 PM
IANAE IANAE is offline
Member
CAS AAA
 
Join Date: Oct 2017
Posts: 207
Default

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..
Reply With Quote
  #32  
Old 10-25-2018, 11:25 AM
Gareth Keenan's Avatar
Gareth Keenan Gareth Keenan is offline
Member
CAS
 
Join Date: Feb 2006
Location: Garden State
Studying for #9
Favorite beer: butter
Posts: 1,308
Default

I find mmult a little confusing, why would you use this instead of a sumproduct or some other function?
Reply With Quote
  #33  
Old 10-25-2018, 11:31 AM
IANAE IANAE is offline
Member
CAS AAA
 
Join Date: Oct 2017
Posts: 207
Default

Quote:
Originally Posted by Gareth Keenan View Post
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.
Reply With Quote
  #34  
Old 10-25-2018, 01:04 PM
dumples dumples is offline
Member
CAS
 
Join Date: Sep 2003
Posts: 1,249
Default

Quote:
Originally Posted by Gareth Keenan View Post
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.
Reply With Quote
  #35  
Old 10-29-2018, 11:31 PM
Hedges Hedges is offline
Member
SOA
 
Join Date: Apr 2002
Location: I've been Shanghaied!
Posts: 472
Default

Quote:
Originally Posted by DeepPurple View Post
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
Reply With Quote
  #36  
Old 10-30-2018, 10:13 AM
TooOldForThis TooOldForThis is offline
Member
 
Join Date: Nov 2001
Posts: 118
Default

Quote:
Originally Posted by Hedges View Post
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.
Reply With Quote
  #37  
Old 10-30-2018, 10:59 PM
Hedges Hedges is offline
Member
SOA
 
Join Date: Apr 2002
Location: I've been Shanghaied!
Posts: 472
Default

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..
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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:12 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, 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.36573 seconds with 10 queries