Count # of style in a range of Sale % Contribution

**goicuon** · 06-05-2012, 04:09 PM

Please help.
I would like to build a formula or a macro to count how many styles that I have sold in different bucket of % contribution: 0% to 20%, 21% to 25%, 26% to 50%, 51% to 75%, 76% to 90%, 91% to 100%
Thank you.

#of Style Sales $
1 10,000
1 9,000
1 8,000
1 7,000
1 6,000
1 5,000
1 4,000
1 3,000
1 2,000
1 1,000
Total 10 55,000

Sales bucket From ($) To ($) How many Styles?
0%-20% 0 11,000 ? How many Styles from O% to 20% or from $0 to $11000
21%-25% 11550 13,750 ? How many Styles from 21% to 25% or from $11550 to $13750
26%-50% 14300 27,500 ? How many Styles from 26% to 50% or from $14300 to $27500
51%-75% 28050 41,250 ? How many Styles from 51% to 75% or from $28050 to $41250
76%-90% 41800 49,500 ? How many Styles from 76% to 90% or from $41800 to $49500
91%-100% 50050 55,000 ? How many Styles from 91% to 100% or from $50050 to $55000

**Haseeb Avarakkan** · 06-05-2012, 11:07 PM

May be like this?

**goicuon** · 06-06-2012, 04:29 PM

Originally Posted by Haseeb A

May be like this?

Haseeb, thank you sooo much! Yes it resolves part of my problem.
But now I am stuck on something else, can you please help me
Thanks a million.

Please see attached:
Book1-Test.xls

**Haseeb Avarakkan** · 06-06-2012, 08:50 PM

I have used Dynamic range to limit the calculation only for used ranges. Whenever you enter a new style will include those records ie in A633, also moved reports to a separate sheet.

I am not sure about last TWO part (highlighted in yellow) is actually your output.

See the attached.

**goicuon** · 06-07-2012, 10:56 AM

Haseeb,

You are a genius! I don't know how to thank you.
I have move the 2 sheets to my flowchart. In the new flowchart, sheet "Data" is "Data Contribution" and sheet "Report" is "Contribution"

I don't know by moving this, did I mess any formula?

I also add some lines for cumulative.

Please feel free to change any formulas if needed.

I explain more on the yellow highlited rows for the last 2 rows formulas. See attached

Again, thank you in advance

I don't know if the 3rd attachment went thru, the new file name is Book3-Test. The file is 1.86MB. If not how can I send it to you?

**Haseeb Avarakkan** · 06-07-2012, 04:10 PM

Changing a sheet name WILL NOT affect the formula.

About yellow highlighted,

Eg: Avg WOS 8.4

Condition for Styles under Avg WOS:

If Wos column in Data tab <=8.4, Then SUM Style.

Condition for Styles over Avg WOS:

If Wos column in Data tab >8.4, Then SUM Style.

If these are your conditions, then it is already calculating on the previous attachment by SUMIF.

**goicuon** · 06-07-2012, 04:30 PM

Haseeb,
I mean for that bucket 0 to 20% (12 styles), how many styles that have under AVG WOS (in this case: 8.4) and how many styles that have equal and over AVG WOS (8.4). The total of these 2 lines should equal to 12.

Thank you

**Haseeb Avarakkan** · 06-07-2012, 04:41 PM

OK. finally I am on the track

B20,

=SUMIF(INDEX(Wos,1,1):INDEX(Wos,B6,1),"<="&B19,INDEX(Style,1,1):INDEX(Style,B6,1))

B21,

=SUMIF(INDEX(Wos,1,1):INDEX(Wos,B6,1),">"&B19,INDEX(Style,1,1):INDEX(Style,B6,1))

Then copy across.

**goicuon** · 06-07-2012, 04:52 PM

Haseeb,

I think it should be B14 instead of B6, is it possible?
=SUMIF(INDEX(Wos,1,1):INDEX(Wos,B14,1),"<="&B19,INDEX(Style,1,1):INDEX(Style,B14,1))

**Haseeb Avarakkan** · 06-07-2012, 05:00 PM

No that should be B6. But I am sorry, I forgot to subtract the previous % results through a SUM

This should work.

B20,

=SUMIF(INDEX(Wos,1,1):INDEX(Wos,B6,1),"<="&B19,INDEX(Style,1,1):INDEX(Style,B6,1))-SUM($A20:A20)

B21,

=SUMIF(INDEX(Wos,1,1):INDEX(Wos,B6,1),">"&B19,INDEX(Style,1,1):INDEX(Style,B6,1))-SUM($A21:A21)

**goicuon** · 06-07-2012, 05:11 PM

Yes, it works perfectly

Thank you so much for your help. I really learn a lot from you, especially those formulas with Index or Offset. And also the way that you defined some rows like Sale, WOS, Style... I still don't understand, because usually if you define something, you should see it in the left hand corner on the top with the arrow, but in this case, I don't see anything.

Anyway, thanks again, your help is greatly appreciated

**goicuon** · 06-08-2012, 09:17 AM

Haseeb,

Can you take another look on the formula of the Styles Under or Over WOS, I recheck and only the 1 bucket 0% to 20% gives me the right count. All other buckets are not. But the total count is right.
I think the reason is each of the bucket does not have the same AVG WOS, so the formula to substract cannot apply to this case (see attachment Book3)

I also think of another way to build the formula, but it takes longer, can you take a look on Book 4 Test attachment

Thank you

**Haseeb Avarakkan** · 06-08-2012, 12:49 PM

Yes. I am sorry. my mistake. I should have realize Avg WOS are different in each %, so -SUM wouldn't work.

Leave B20:B21 formulas as it is. Change C20 to,

Please Login or Register  to view this content.

C21,

Please Login or Register  to view this content.

Then select C20:C21 copy across

This formula only looking in to appropriate ranges NOT ALL ranges.

eg:

0-20% in E2:E13

21-25% - E14:E18

26-50% - E19:E55 etc...

**goicuon** · 06-11-2012, 06:57 PM

Haseeb,

I try to put the formulas you gave me, but it's not working. Is it because I add more formulas in the sheet DataContribution?

See attachment Book5.Test

Thank you

**Haseeb Avarakkan** · 06-11-2012, 08:03 PM

It should be "<"&C20 AND ">"&C20 instead of "<"&C19. You are looking for "<"&AvgWos, currently this looking "<"&EohUnits

It is better to use either ">=" & "<" OR ">" & "<=". If you have equal value of AvgWos & Wos this wouldn't count it.

Eg: Avg WOS = 12.5 in under 0 - 20% and value E3 is 12.5, so this may give you 6 & 5. So better to use under Avg Wos <=12.5 or <12.5 above Avg Wos >12.5 or >=12.5 respectively.

Copy_Book5-Test.xls

**goicuon** · 06-11-2012, 10:13 PM

Yes, but I still did not have the right result:
B20 & B21 , C20 & C21 are OK, but starting D20 & D21, I count that there are more than 9 styles in this bucket that have over AVG WOS.
Thank you

**goicuon** · 06-11-2012, 10:27 PM

Cells B21&B22, C21&C22 are OK
But starting Cell D21&D22 are not OK, I count the # of Styles more than AVG WOS, there are more than 9 styles.

**Haseeb Avarakkan** · 06-11-2012, 10:44 PM

D21 & d22 is under 26-50%, so 50% reach at row# 61 in Style which is Row 62 in the spreadsheet. So the EOH is in D19:D62. I only can see see 9 cells >10.9121676411591. D19:D22, D25, D48, D54, D56, D59. Are you sure you are at the correct %? Or am I miss something?

**goicuon** · 06-12-2012, 08:30 AM

Please look at DataContribution sheet.

The WOS is in column K. We know that AVG WOS is 10.9. From row 19 to row 62 for this bucket 26% to 50%, there are 44 styles in Total, and I Count 21 styles that have more than AVG WOS 10.9,23 styles under AVG WOS 10.9

**goicuon** · 06-12-2012, 05:27 PM

I found the mistake. I have moved the column WOS in sheet DataContribution to another column, I just have to move it back or redefine it and everything should be OK.

Count # of style in a range of Sale % Contribution

LinkBack

Thread Tools

Rate This Thread

Display

Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Re: Count # of style in a range of Sale % Contribution

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions