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
  #11  
Old 10-18-2018, 01:07 PM
ALivelySedative's Avatar
ALivelySedative ALivelySedative is offline
Member
CAS
 
Join Date: Dec 2013
Location: Land of the Pine
College: UNC-Chapel Hill Alum
Favorite beer: Red Oak
Posts: 2,686
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
Array functions are not ever my first solution. Last resort. I'd rather waste space in a separate column with a Min(20,A1) function, because it's easier to document and for a first-time viewer to follow.
I have one spreadsheet that uses array functions, and i will redo it entirely one day...but not today.
Reply With Quote
  #12  
Old 10-18-2018, 02:52 PM
Noonien Soong Noonien Soong is offline
CAS
 
Join Date: Sep 2018
Posts: 17
Default

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

Thank you.

Quote:
Originally Posted by Hedges View Post
Aren't array formulae what the OP is looking for? SUMPRODUCT is a built in function which will handle a specific type of manipulation but array formulae will handle a much wider range of calculations.

e.g. {=SUM(A1:A4*A1:A4)} is the array formula equivalent of SUMPRODUCT(A1:A4, A1:A4)

However, with array formulae, you can do much more. For example, you can do the following: for each cell in column A, take the greater of 20 or the cell value and then multiply it by the corresponding cell in column B. Finally take the greatest value of each of these.
{=MAX(IF(A1:A4<20,20,A1:A4)*(B1:B4))}

(Note that 20 could also be a reference to another single cell which contains the value 20. Or for something slightly different, it be another range of cells, e.g. C1:C4)

Note that you don't actually type in the curly braces. You use Ctrl+Shift+Enter to enter the formula and the braces appear.* So array formulae are sometimes known as CSE formulae.

Also, note that an array formula return an array result. However, apply a function that returns a single value such as SUM or MAX will give a a single number.
Thus {=(A1:A4*A1:A4)} is actually returning a range of 4 rows x 1 column. The SUM in {=SUM(A1:A4*A1:A4)} is then adding these up to produce a scalar value.
To see the full results of {=(A1:A4*A1:A4)}, you'd need to CSE the formula in range of 4x1. But entering this formula in a single cell won't produce an error - it will just give you the first row's result.

If you accidentally enter {=(A1:A4*A1:A4)} in a range of 5x1, the last row will produce a #N/A error. However, you cannot delete part of a range of a CSE formula. You have to delete (or change) the entire range. But there is a way to get rid of that annoying error. In order to delete just the offending fifth row, you need to "undo" the CSE formula by highlighting the whole range and then using Ctrl+Enter (NO Shift) to re-enter the formula - you'll see that the curly braces have disappeared.** Then delete the fifth row (allowable because its now longer part of a CSE formula) and then finally re-enter the first rows as a CSE formula again. This sounds messy but you get used to it quickly and you only have to worry about it if you've selected the "wrong" output range.

CSE formulae also work with matrices, not just vectors.

Overall, I don't use CSE formulae very often but they can be extremely useful.

I hope this was what you were looking for.

* The technical explanation is that behind the scenes, Excel releases a small, invisible troll which quickly puts in the curly braces.
** The troll collects them again, obviously.
Reply With Quote
  #13  
Old 10-18-2018, 03:08 PM
CuriousGeorge CuriousGeorge is offline
Member
CAS SOA
 
Join Date: Dec 2005
Posts: 1,259
Default

Quote:
Originally Posted by Noonien Soong View Post
This was a solid response, though the sumproduct on the same range twice was not somthing i'd considered either.

Thank you.
I don't think I've ever used SUMPRODUCT() for anything BUT sum of squares or weighted averages.
Reply With Quote
  #14  
Old 10-18-2018, 04:17 PM
IANAE IANAE is offline
Member
CAS AAA
 
Join Date: Oct 2017
Posts: 201
Default

A5 = {sum(A1:A4^2)}
Reply With Quote
  #15  
Old 10-18-2018, 04:19 PM
IANAE IANAE is offline
Member
CAS AAA
 
Join Date: Oct 2017
Posts: 201
Default

Quote:
Originally Posted by Hedges View Post
However, with array formulae, you can do much more.
This.
Reply With Quote
  #16  
Old 10-19-2018, 02:06 PM
JMO's Avatar
JMO JMO is offline
Carol Marler
Non-Actuary
 
Join Date: Sep 2001
Location: Back home again in Indiana
Studying for Nothing actuarial.
Posts: 37,605
Default

Quote:
Originally Posted by ALivelySedative View Post
I have one spreadsheet that uses array functions, and i will redo it entirely one day...but not today.
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 non-actuarial.


My latest favorite quotes, updated Apr 5, 2018.

Spoiler:
I should keep these four permanently.
Quote:
Originally Posted by rekrap View Post
JMO is right
Quote:
Originally Posted by campbell View Post
I agree with JMO.
Quote:
Originally Posted by Westley View Post
And def agree w/ JMO.
Quote:
Originally Posted by MG View Post
This. And everything else JMO wrote.
And this all purpose permanent quote:
Quote:
Originally Posted by Dr T Non-Fan View Post
Yup, it is always someone else's fault.
MORE:
All purpose response for careers forum:
Quote:
Originally Posted by DoctorNo View Post
Depends upon the employer and the situation.
Quote:
Originally Posted by Sredni Vashtar View Post
I feel like ERM is 90% buzzwords, and that the underlying agenda is to make sure at least one of your Corporate Officers is not dumb.
Reply With Quote
  #17  
Old 10-19-2018, 04:26 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,210
Default

Quote:
Originally Posted by Dr T Non-Fan View Post
Array functions are not ever my first solution. Last resort. I'd rather waste space in a separate column with a Min(20,A1) function, because it's easier to document and for a first-time viewer to follow.

Agreed: Sumproduct(A1:A4,A1:A4) is better than an array function
__________________
Come on. Let's go space truckin'. Come on!
Reply With Quote
  #18  
Old 10-22-2018, 03:12 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,210
Default

Quote:
Originally Posted by Noonien Soong View Post
Say in A1:A4 I have 1, 2, 3, and 4 respectively.

Is it possible to take the sum of each of those #s squared in one cell? Sure, I could set B1=A1^2, copy that down, then take sum(B1:B4), but is there any way to do this directly in cell A5? Maybe with an array?

Peace,

Noonien Soong.

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; 10-22-2018 at 03:31 PM..
Reply With Quote
  #19  
Old 10-22-2018, 03:24 PM
DeepPurple's Avatar
DeepPurple DeepPurple is offline
Member
 
Join Date: Jun 2004
Posts: 4,210
Default

Quote:
Originally Posted by Hedges View Post

However, with array formulae, you can do much more. For example, you can do the following: for each cell in column A, take the greater of 20 or the cell value and then multiply it by the corresponding cell in column B. Finally take the greatest value of each of these.
{=MAX(IF(A1:A4<20,20,A1:A4)*(B1:B4))}
Oh Really?!?!?!?

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!
Reply With Quote
  #20  
Old 10-22-2018, 10:56 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
=SUMPRODUCT(MAX((20*(A1:A4<20)+A1:A4*(A1:A4>=20))* B1:B4))
Impressive, even in all it's ugliness! OK, counter-challenge 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; 10-23-2018 at 01:48 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 04:15 AM.


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.30727 seconds with 10 queries