excel - Top third, next third of items by sales -

excel - Top third, next third of items by sales -

i have excel sheet shown below. need top third/ next 3rd items sales count. there way done in excel?

item count 1 100 2 90 3 80 4 60 5 55 6 50 7 45 8 35 9 25

dividing 3 buckets, 540/3 = ~180 items in each –

bucket 1 – items 1 , 2 (count = 190) bucket 2 – items 3, 4 , 5 (count = 195) bucket 3 - items 6, 7, 8, 9 (count = 155)

there multiple ways accomplish this. assuming item , count info in columns , b, shortest path utilize next formula in cell c2:

=round(3*sum($b$2:$b2)/sum($b$2:$b$10),0)

after entering c2, select cell , drag downwards right-bottom corner of cell way lastly row. note $ sign "missing" on purpose before sec 2. takes care of auto-fill behavior needed when dragging downwards corner.

if allowed utilize helper column, can create computationally more efficient method using next layout:

if want to, can hide column c. contains cumulative values of different sales counts. cell c1 set 0, cell c2 contains formula =$c1+$b2. column d approximates buckets using formula =round(3*$c2/$c$10,0) in cell d2, , 1 time again dragging downwards bottom-right corner. might improve approach if have many rows on sheet.

note both solutions yield same results. value in 1 or more buckets become 0, not right. can avoided using roundup in stead of round, since have not indicated want boundaries of buckets fall in different situations, thought leave exercise :-).

excel