+ Reply to Thread
Results 1 to 20 of 20

Count # of style in a range of Sale % Contribution

  1. #1
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

    Count # of style in a range of Sale % Contribution

    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

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

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

    May be like this?
    Attached Files Attached Files
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    Quote Originally Posted by Haseeb A View Post
    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
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

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

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    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?
    Attached Files Attached Files
    Last edited by goicuon; 06-07-2012 at 11:31 AM.

  6. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

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

    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.

  7. #7
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    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

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

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

    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.

  9. #9
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    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))

  10. #10
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

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

    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)

  11. #11
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    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

  12. #12
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    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
    Attached Files Attached Files
    Last edited by goicuon; 06-08-2012 at 11:34 AM.

  13. #13
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

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

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

  14. #14
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    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
    Attached Files Attached Files
    Last edited by goicuon; 06-11-2012 at 07:04 PM.

  15. #15
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

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

    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

  16. #16
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    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

  17. #17
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    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.

  18. #18
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

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

    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?

  19. #19
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    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
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    06-01-2012
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    23

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1