

FlashChat  Actuarial Discussion  Preliminary Exams  CAS/SOA Exams  Cyberchat  Around the World  Suggestions 
Browse Open Actuarial Jobs 

Thread Tools  Search this Thread  Display Modes 
#35




Quote:
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; 10302018 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




Quote:

#37




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 15 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. Last edited by Hedges; 10312018 at 12:26 AM.. 
Thread Tools  Search this Thread 
Display Modes  

