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



 
 
Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 09-21-2018, 01:24 PM
glassy glassy is offline
Member
Non-Actuary
 
Join Date: May 2015
Posts: 3,888
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
 

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 07:25 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.61094 seconds with 10 queries