

FlashChat  Actuarial Discussion  Preliminary Exams  CAS/SOA Exams  Cyberchat  Around the World  Suggestions 
DW Simpson 
Actuarial Salary Surveys 
Actuarial Meeting Schedule 
Contact DW Simpson 

Thread Tools  Search this Thread  Display Modes 
#1




Excel question
glassie 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; 09212018 at 12:28 PM.. 
#2




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. 
#3




Yeah, that's pretty much what I did (see OP), but seems like there must be a "better" way.

#4




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. 
#5




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 
#9




Quote:
Quote:
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




Quote:
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. 
Thread Tools  Search this Thread 
Display Modes  

