

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

Thread Tools  Search this Thread  Display Modes 
#12




This was a solid response, though the sumproduct on the same range twice was not somthing i'd considered either.
Thank you. Quote:

#16




I had one once, already redid it. I needed to change some of the formulas and Excel kept giving me unhelpful error messages, so I ended up deleting all the array stuff and rebuilding all of the formulas. Much easier to follow. I hope the person who is now responsible for that spreadsheet is happier with it that I was when I got it.
__________________
Carol Marler, "Just My Opinion" Pluto is no longer a planet and I am no longer an actuary. Please take my opinions as nonactuarial. My latest favorite quotes, updated Nov. 20, 2018. Spoiler: 
#17




Quote:
Agreed: Sumproduct(A1:A4,A1:A4) is better than an array function
__________________
Come on. Let's go space truckin'. Come on! 
#18




Quote:
Also, SUMSQ is a valid function such that Sumsq(range1) is the same as Sumproduct(range1, range1) according to ancient lor
__________________
Come on. Let's go space truckin'. Come on! Last edited by DeepPurple; 10222018 at 02:31 PM.. 
#19




Quote:
Challenge accepted! =SUMPRODUCT( MAX( (20*(A1:A4<20)+A1:A4*(A1:A4>=20))*B1:B4)) I am not saying my way is preferable. I am saying it is possible. Therefore your assertion that you can do MORE with array functions is still unproven and potentially false.
__________________
Come on. Let's go space truckin'. Come on! 
#20




Impressive, even in all it's ugliness! OK, counterchallenge accepted. (I shall be interested to see how my original assertion stands up.) How about the following:
1) I believe Excel now has MAXIFS /MINIFS functions but before that* I would have used an array formula, e.g. the following {=MAX(A1:A4*(B1:B4=C1:C4))} finds the maximum value in column A where for each row the value in column B equals that in column C 2) Find the number of unique occurrences in a list. e.g. if the first 10 rows in column A contain A, B, A, B, A, C, C, A, A, D, then the formula should produce 4, since there are 4 different letters that appear. {=SUM(1/COUNTIF(A1:A10,A1:A10))} Edited to update a solution to 2: It seems you can use a SUMPRODUCT to replace the braces, i.e. = SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)) Further edited to update a solution to 1: =SUMPRODUCT(MAX(A1:A4*(B1:B4=C1:C4))) Hmmm. So, it seems you can use SUMPRODUCT to convert a range to scalar. And it seems I'm unable to provide a counter example! *Actually I still would, since I don't have the newest version of Excel. Last edited by Hedges; 10232018 at 12:48 AM.. 
Thread Tools  Search this Thread 
Display Modes  

