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


Upload your resume securely at https://www.dwsimpson.com
to be contacted when our jobs meet your skills and objectives.


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 01-27-2020, 12:26 PM
mch375 mch375 is offline
CAS SOA
 
Join Date: Mar 2019
College: Postgraduate
Posts: 19
Default Excel Pivot Table

Hello All,

In reference to the attached image, when columns A and B (w/ filter on blanks) are placed in Columns field and Amount in Values field, the pivot table is taking the intersection of the 2 columns, which is on row 3, and show the sum amount of 5. I want it to show 15 for A and 5 for B. Is there a way to show distinct sum amounts?

Thank you.
Attached Images
 
Reply With Quote
  #2  
Old 01-27-2020, 12:40 PM
KS6392's Avatar
KS6392 KS6392 is offline
Member
CAS
 
Join Date: Nov 2016
Studying for Exam 9
Posts: 319
Default

I'm not sure I follow. Does each X represent $5? It doesn't seem to me that a pivot table will be helpful. If you want a sum at the bottom of row A for example, use this formula in cell A6:

=SUMPRODUCT( --(A$2:A$5="X"), $E$2:$E$5 )
__________________
ACAS | 7 | 8 | 9
Reply With Quote
  #3  
Old 01-27-2020, 12:43 PM
mch375 mch375 is offline
CAS SOA
 
Join Date: Mar 2019
College: Postgraduate
Posts: 19
Default

Quote:
Originally Posted by KS6392 View Post
I'm not sure I follow. Does each X represent $5? It doesn't seem to me that a pivot table will be helpful. If you want a sum at the bottom of row A for example, use this formula in cell A6:

=SUMPRODUCT( --(A$2:A$5="X"), $E$2:$E$5 )
Thank you for the reply. X represents a check mark. Column B has 1 check mark that is $5. Can I still tweak the table to show the distinct sum amounts?
Reply With Quote
  #4  
Old 01-27-2020, 12:50 PM
KS6392's Avatar
KS6392 KS6392 is offline
Member
CAS
 
Join Date: Nov 2016
Studying for Exam 9
Posts: 319
Default

Would just making a new table be acceptable?
Attached Images
 
__________________
ACAS | 7 | 8 | 9
Reply With Quote
  #5  
Old 01-27-2020, 12:54 PM
mch375 mch375 is offline
CAS SOA
 
Join Date: Mar 2019
College: Postgraduate
Posts: 19
Default

Quote:
Originally Posted by KS6392 View Post
Would just making a new table be acceptable?
Was looking for a way to show it through a pivot table. Appreciate your time and feedbacks
Reply With Quote
  #6  
Old 01-27-2020, 02:26 PM
Vorian Atreides's Avatar
Vorian Atreides Vorian Atreides is offline
Wiki/Note Contributor
CAS
 
Join Date: Apr 2005
Location: As far as 3 cups of sugar will take you
Studying for CSPA
College: Hard Knocks
Favorite beer: Most German dark lagers
Posts: 67,615
Default

Pivot table isn't the best "structure" for something that might be reused.

They're good for exploring ideas for data layout, IMO. But not for "report building".
__________________
I find your lack of faith disturbing

Why should I worry about dying? Itís not going to happen in my lifetime!


Freedom of speech is not a license to discourtesy

#BLACKMATTERLIVES
Reply With Quote
  #7  
Old 01-27-2020, 03:59 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: 3,538
Default

The sumproduct formula above would be my approach, but you may be able to structure a calculated field that is the product of the 'count' of X's times the amounts in the rows.
__________________
Stuff | 6 | ACAS | FCAS stuff

Quote:
Originally Posted by Abelian Grape View Post
You can still spit in my mouth.
Reply With Quote
  #8  
Old 01-27-2020, 04:05 PM
KS6392's Avatar
KS6392 KS6392 is offline
Member
CAS
 
Join Date: Nov 2016
Studying for Exam 9
Posts: 319
Default

Quote:
Originally Posted by ALivelySedative View Post
The sumproduct formula above would be my approach, but you may be able to structure a calculated field that is the product of the 'count' of X's times the amounts in the rows.
You're right you could. But unless I'm overlooking something, I think you'd need a calculated field for each column (A_amount, B_amount, etc.). Seems like a terrible way to do anything but if he's determined to use a pivot I think it may be his only option.
__________________
ACAS | 7 | 8 | 9
Reply With Quote
  #9  
Old 01-27-2020, 05:30 PM
IMP's Avatar
IMP IMP is offline
= I Must Pass
 
Join Date: Mar 2004
Location: in my pants, and yours
Studying for drinking
Favorite beer: blue moon....cuz i'm a female
Posts: 31,928
Blog Entries: 11
Default

Name:  8F3C84FD-2844-4305-94D0-09B3356FDE2C.jpg
Views: 61
Size:  9.0 KB
__________________
Quote:
Originally Posted by Patience View Post
that's why they invented doggy style
Reply With Quote
  #10  
Old 01-28-2020, 09:09 AM
Vorian Atreides's Avatar
Vorian Atreides Vorian Atreides is offline
Wiki/Note Contributor
CAS
 
Join Date: Apr 2005
Location: As far as 3 cups of sugar will take you
Studying for CSPA
College: Hard Knocks
Favorite beer: Most German dark lagers
Posts: 67,615
Default

Quote:
Originally Posted by KS6392 View Post
You're right you could. But unless I'm overlooking something, I think you'd need a calculated field for each column (A_amount, B_amount, etc.). Seems like a terrible way to do anything but if he's determined to use a pivot I think it may be his only option.
I agree. Replace the "X" with "5". The calculated field on the right could simply be "max" of the row; and PivotTable has an option to provide summary information for columns as well.
__________________
I find your lack of faith disturbing

Why should I worry about dying? Itís not going to happen in my lifetime!


Freedom of speech is not a license to discourtesy

#BLACKMATTERLIVES
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 02:44 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, 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.23883 seconds with 12 queries