Actuarial Outpost Excel question
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

#1
09-21-2018, 12:24 PM
 glassy Member Non-Actuary Join Date: May 2015 Posts: 4,349
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 12:28 PM..
#2
09-21-2018, 12:32 PM
 tommie frazier Member Join Date: Aug 2003 Favorite beer: The kind with 2 e's Posts: 23,140

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)
#3
09-21-2018, 12:42 PM
 glassy Member Non-Actuary Join Date: May 2015 Posts: 4,349

Yeah, that's pretty much what I did (see OP), but seems like there must be a "better" way.
#4
09-21-2018, 03:14 PM
 tommie frazier Member Join Date: Aug 2003 Favorite beer: The kind with 2 e's Posts: 23,140

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
#5
09-21-2018, 03:40 PM
 mathmajor 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: 10,996

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

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

__________________
FSA
it/its
#6
09-21-2018, 03:50 PM
 |B|rad Member CAS Join Date: Jul 2009 Posts: 1,050

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)
#7
09-21-2018, 04:05 PM
 mathmajor 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: 10,996

vlookup is going to capture the same total every time
__________________
FSA
it/its
#8
09-21-2018, 04:18 PM
 MayanActuary Member SOA Join Date: Jul 2010 Posts: 1,674

Quote:
 Originally Posted by mathmajor 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"
#9
09-21-2018, 04:18 PM
 Sredni Vashtar Member Join Date: Mar 2010 Favorite beer: pilseners Posts: 7,706 Blog Entries: 1

Quote:
 Originally Posted by tommie frazier 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 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.
#10
09-21-2018, 04:21 PM
 Sredni Vashtar Member Join Date: Mar 2010 Favorite beer: pilseners Posts: 7,706 Blog Entries: 1

Quote:
 Originally Posted by |B|rad 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.