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

Browse Open Actuarial Jobs

Life  Health  Casualty  Pension  Entry Level  All Jobs  Salaries


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 09-21-2018, 01:24 PM
glassy glassy is offline
Member
Non-Actuary
 
Join Date: May 2015
Posts: 3,802
Default Excel question

g-lassie recently had a problem to solve with her spreadsheet. Here's how the sheet is set up:

Column A is a series of product names. They are grouped in certain categories, but the number of products in each group is not uniform (some groups have 3 products, some have 10, etc). Column B has a count of each product. After each group, Column A will say "Total" and Column B will have the total # of products in the group. The goal is to have in a new column the % of total: count of the particular item in the group / total count of items in the group.

I solved this for her by having column C =VLOOKUP("Total", $A2:$A$1000**, 2, FALSE) and column D = IF(A2="Total","",B2/C2), but I know this isn't ideal and I can't think of how to word a google search to find a solution. I'm sure I'm missing something pretty easy here.

Any way to do this in one nice formula? Please no array formulas if possible, I'd rather not open up that can of worms to her.


**The total number of rows in the sheet

Example with desired output:
Code:
PRODUCT	COUNT	
x1	52	5%
x2	301	29%
x3	293	29%
x4	219	21%
x5	157	15%
Total	1,022	
y1	19	3%
y2	183	30%
y3	163	26%
y4	141	23%
y5	114	18%
Total	620

Last edited by glassy; 09-21-2018 at 01:28 PM..
Reply With Quote
  #2  
Old 09-21-2018, 01:32 PM
tommie frazier tommie frazier is offline
Member
 
Join Date: Aug 2003
Favorite beer: The kind with 2 e's
Posts: 22,926
Default

add a column C that tracks the grouping. So it captures if it is in x or y.

then have percent D2 = iferror(b2/ sumif(C:C,c2,B:B),0)
__________________
Removed a dated athletic reference under pressure from a friend. You can still give money to help fund research on neurofibromatosis (nf).

General info at www.ctf.org

Team donation page here.
Reply With Quote
  #3  
Old 09-21-2018, 01:42 PM
glassy glassy is offline
Member
Non-Actuary
 
Join Date: May 2015
Posts: 3,802
Default

Yeah, that's pretty much what I did (see OP), but seems like there must be a "better" way.
Reply With Quote
  #4  
Old 09-21-2018, 04:14 PM
tommie frazier tommie frazier is offline
Member
 
Join Date: Aug 2003
Favorite beer: The kind with 2 e's
Posts: 22,926
Default

your percent calculations require a tracking of the total which is in a different place relative to each cell bc the groupings are of different sizes. and the vlookup on "total" alone will have issues unless the total is identified as the total for whatever grouping.

don't get me wrong, these two ways aren't that different. but I don't actually follow your way that well (and accept it is possible you don't follow mine as well). more than one way to skin the cat
__________________
Removed a dated athletic reference under pressure from a friend. You can still give money to help fund research on neurofibromatosis (nf).

General info at www.ctf.org

Team donation page here.
Reply With Quote
  #5  
Old 09-21-2018, 04:40 PM
mathmajor's Avatar
mathmajor mathmajor is offline
Member
SOA AAA
 
Join Date: Dec 2010
Location: Nowhere in particular
Studying for Japanese
College: B.S. Applied Math
Favorite beer: La Croix Grapefruit
Posts: 9,617
Default

Cell B2 and below:
=IF(A2="Total",SUM($B1:B$2)-2*SUMIF($A1:A$2,"Total",$B1:B$2),RANDBETWEEN(1,100 ))

(added randbetween to simulate values)

Cell C2 and below:
=IF(A2="Total","",B2/OFFSET(C2,MATCH("Total",A3:$A$250,0),-1))

Product Count Percent
x1 96 35.6%
x2 97 35.9%
x3 69 25.6%
x4 8 3.0%
Total 270
y1 18 11.4%
y2 88 55.7%
y3 52 32.9%
Total 158
z1 53 17.7%
z2 81 27.0%
z3 96 32.0%
z4 54 18.0%
z5 16 5.3%
Total 300

your welcome
__________________
FSA
Opinions are provided for entertainment purposes only and are no substitute for professional guidance.
Reply With Quote
  #6  
Old 09-21-2018, 04:50 PM
|B|rad |B|rad is offline
Member
CAS
 
Join Date: Jul 2009
Posts: 1,026
Default

Why cant you just combine the two columns you've suggested already?

column C = IF(A2="Total","",B2/VLOOKUP("Total", $A2:$B$1000**, 2, FALSE))

or who cares about 100%'s next to totals?:
column C = B2/VLOOKUP("Total", $A2:$B$1000**, 2, FALSE)
Reply With Quote
  #7  
Old 09-21-2018, 05:05 PM
mathmajor's Avatar
mathmajor mathmajor is offline
Member
SOA AAA
 
Join Date: Dec 2010
Location: Nowhere in particular
Studying for Japanese
College: B.S. Applied Math
Favorite beer: La Croix Grapefruit
Posts: 9,617
Default

vlookup is going to capture the same total every time
__________________
FSA
Opinions are provided for entertainment purposes only and are no substitute for professional guidance.
Reply With Quote
  #8  
Old 09-21-2018, 05:18 PM
MayanActuary's Avatar
MayanActuary MayanActuary is offline
Member
SOA
 
Join Date: Jul 2010
Posts: 1,634
Default

Quote:
Originally Posted by mathmajor View Post
vlookup is going to capture the same total every time
The row isn't anchored in the formula so as you drag it down it gets the next highest "total"
Reply With Quote
  #9  
Old 09-21-2018, 05:18 PM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is offline
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 6,758
Blog Entries: 1
Default

Quote:
Originally Posted by tommie frazier View Post
add a column C that tracks the grouping. So it captures if it is in x or y.

then have percent D2 = iferror(b2/ sumif(C:C,c2,B:B),0)
Quote:
Originally Posted by glassy View Post
Yeah, that's pretty much what I did (see OP), but seems like there must be a "better" way.
I think tommie is saying something like:
C1= "Group"
C2= left(A2,1)= 'x'
D2= sumif on C2.

That assumes that like your example, you can actually recover the grouping from the product description.
__________________
Sredni Vashtar went forth,
His thoughts were red thoughts and his teeth were white.
His enemies called for peace, but he brought them death.
Sredni Vashtar the Beautiful.
Reply With Quote
  #10  
Old 09-21-2018, 05:21 PM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is offline
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 6,758
Blog Entries: 1
Default

Quote:
Originally Posted by |B|rad View Post
Why cant you just combine the two columns you've suggested already?

column C = IF(A2="Total","",B2/VLOOKUP("Total", $A2:$B$1000**, 2, FALSE))

or who cares about 100%'s next to totals?:
column C = B2/VLOOKUP("Total", $A2:$B$1000**, 2, FALSE)
I agree with this...


I assume the OP wants a way that's less janky than a vlookup a random azz word.
But sometimes that's just how the data rolls.
__________________
Sredni Vashtar went forth,
His thoughts were red thoughts and his teeth were white.
His enemies called for peace, but he brought them death.
Sredni Vashtar the Beautiful.
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 08:29 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.19813 seconds with 9 queries