How To Count Conditionally Based On % Mix?

[xlmaniac]

Dear All,
I do have Stylewise Weekwise % Mix Sample data set across A1:I3 as follows:-

[TABLE="width: 576"]
<tbody>[TR]
[TD="width: 64"]Style[/TD]
[TD="width: 64"]Week-1[/TD]
[TD="width: 64"]Week-2[/TD]
[TD="width: 64"]Week-3[/TD]
[TD="width: 64"]Week-4[/TD]
[TD="width: 64"]Week-5[/TD]
[TD="width: 64"]Week-6[/TD]
[TD="width: 64"]Week-7[/TD]
[TD="width: 64"]Week-8[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="class: xl63, align: right"]12%[/TD]
[TD="class: xl63, align: right"]14%[/TD]
[TD="class: xl63, align: right"]18%[/TD]
[TD="class: xl63, align: right"]20%[/TD]
[TD="class: xl63, align: right"]10%[/TD]
[TD="class: xl63, align: right"]11%[/TD]
[TD="class: xl63, align: right"]8%[/TD]
[TD="class: xl63, align: right"]7%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="class: xl63, align: right"]10%[/TD]
[TD="class: xl63, align: right"]12%[/TD]
[TD="class: xl63, align: right"]14%[/TD]
[TD="class: xl63, align: right"]15%[/TD]
[TD="class: xl63, align: right"]16%[/TD]
[TD="class: xl63, align: right"]13%[/TD]
[TD="class: xl63, align: right"]10%[/TD]
[TD="class: xl63, align: right"]10%[/TD]
[/TR]
</tbody>[/TABLE]
I would like to find out how many weeks % Mix contribute to 45%-50%, 75%-80% & 100%
My desired Result across A5:D7 is as follows:-
[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"]Style[/TD]
[TD="width: 64"]45%-50%[/TD]
[TD="width: 64"]75%-80%[/TD]
[TD="class: xl65, width: 64, align: right"]100%[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8
[/TD]
[/TR]
</tbody>[/TABLE]
Above metrics indicates that for Style A, 5 Weeks sales contribute between 45%-50%, 5 Weeks sales contribute between 75%-80% & 8 weeks contribute 100%.

Similarly,Above metrics indicates that for Style B, 4 Weeks sales contribute between 45%-50%, 6 Weeks sales contribute between 75%-80% & 8 weeks contribute 100%.

Could somebody help to solve my issue?

Regards

 

[piotrekp1]

I'm not sure if I understand your question, You'd like to find minimum number of records, such that the distribution requirements (45%-50%) are satisfied, right?

 

[xlmaniac]

I'm not sure if I understand your question, You'd like to find minimum number of records, such that the distribution requirements (45%-50%) are satisfied, right?

Dear Piotrekp1,
Yes you are absolutely right in understanding the problem.
Pls suggest the solution.
Regards

 

[piotrekp1]

Then I'm right that if we have dataset with examples: 50%, 20%, 20%, 5%, 5%, then the result for 40%-45% should be 2, right? What if there is no such combination (15% with this dataset)

 

[xlmaniac]

Then I'm right that if we have dataset with examples: 50%, 20%, 20%, 5%, 5%, then the result for 40%-45% should be 2, right? What if there is no such combination (15% with this dataset)

Dear Piotrekp1,
Yes, as per your example it should yield 2.
But as per my requirement(45%-50%) it should be 3(based on your example).
I did not understand your last question. Could you pls elaborate?
Regards

 

[piotrekp1]

i meant dataset with examples: 50%. 20%, 20%, 5%, 5% and query like 12%-17%. But I guess it doesn't really matter. The issue is that this is NP-complete problem (generalization of a subset sum), what basically means that it's really hard computationally and the natural way of thinking about is by thinking about every possible subset, which is super unnatural for excel, i'm not really an expert at excel, but I'd recommend you using different tool for this one, like python for example, or you could search in the internet for the subset sum solver.

Cheers

 

[xlmaniac]

i meant dataset with examples: 50%. 20%, 20%, 5%, 5% and query like 12%-17%. But I guess it doesn't really matter. The issue is that this is NP-complete problem (generalization of a subset sum), what basically means that it's really hard computationally and the natural way of thinking about is by thinking about every possible subset, which is super unnatural for excel, i'm not really an expert at excel, but I'd recommend you using different tool for this one, like python for example, or you could search in the internet for the subset sum solver.

Cheers

Thanks a lot for all the support extended and time spent behind my problem.
As suggested, I will search on the topics that you have advised.