Sumif & Countif

[LF.TEN]

Hi, I'm trying to use the countif & sumif to find which item driving th
80% sales.
Do you have any better formula to calculate the 80% accurately. I'v
attached my working here.

column D
=COUNTIF(C2:C10,">10.5%")

column E
=SUMIF(C2:C10,">10.5%")


Thank

+-------------------------------------------------------------------
|Filename: abc.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=457
+-------------------------------------------------------------------

 

[Spencer101]

Hi, I'm trying to use the countif & sumif to find which item driving th
80% sales.
Do you have any better formula to calculate the 80% accurately. I'v
attached my working here.

column D
=COUNTIF(C2:C10,">10.5%")

column E
=SUMIF(C2:C10,">10.5%")


Thanks

What do you mean by "driving the 80% sales"

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[LF.TEN]

What do you mean by "driving the 80% sales"?

I'm trying set a formul

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

I'm trying set a formula

I know that!
My question was "what do you mean by 'driving the 80% sales'"?

I don't understand what you're trying to achieve with a formula, an
until you can explain the intended results there's not a person out her
that is able to help you.

I saw you posted the same question on another Excel forum with far mor
users than this one and if they couldn't help you without an explanatio
then nobody can

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[joeu2004]

Hi, I'm trying to use the countif & sumif to find which
item driving the 80% sales.
Do you have any better formula to calculate the 80%
accurately. [....]
column D
=COUNTIF(C2:C10,">10.5%")
column E
=SUMIF(C2:C10,">10.5%") [....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=457|

And it appears that you like to find the number of items whose percentage of
items sold sum to about 80%.

(Note that column C is the percentage per item of total items sold.)

I believe your formulas work only by coincidence. Imagine a situation with
many more items to sell, and none represents more then 10.5% of the total
sold.

First, you need to specify some criteria for the solution. For example, do
you want to know the fewest items that sum to about 80%; or do you want to
know the most items; or do you want the number of items whose sum comes
closest to 80%? And do you want "about" 80% (which might be less); or do
you want "no less than" 80%?

In any case, this is a difficult problem to solve. With very few items,
there is a way to set up Solver to provide __an__ answer, not necessarily
the "best" answer.

But generally, it is requires an algorithm implemented using VBA (i.e. a
macro).

A couple have been mentioned in past discussions. I don't know if any of
them find the "best"; and I don't know if any of them find "no less than" or
if they find "about" (which might be less).

You might start with the VBA code mentioned at
http://www.sulprobil.com/html/accounts_receivable_problem.html.
Unfortunately, his webpage is difficult to understand, IMHO. But the code
is usable.

Nevertheless, I have not really vetted the algorithm other than to try one
or two simple examples.

Good luck!

 

[LF.TEN]

Hi, I'm trying to use the countif & sumif to find which
item driving the 80% sales.
Do you have any better formula to calculate the 80%
accurately.- [....]-
column D
=COUNTIF(C2:C10,">10.5%")
column E
=SUMIF(C2:C10,">10.5%")- [....]-
|Download

http://www.excelbanter.com/attachment.php?attachmentid=457|-

And it appears that you like to find the number of items whos
percentage of
items sold sum to about 80%.

(Note that column C is the percentage per item of total items sold.)

I believe your formulas work only by coincidence. Imagine a situatio
with
many more items to sell, and none represents more then 10.5% of th
total
sold.

First, you need to specify some criteria for the solution. For example
do
you want to know the fewest items that sum to about 80%; or do you wan
to
know the most items; or do you want the number of items whose sum come

closest to 80%? And do you want "about" 80% (which might be less); o
do
you want "no less than" 80%?

In any case, this is a difficult problem to solve. With very few items

there is a way to set up Solver to provide __an__ answer, no
necessarily
the "best" answer.

But generally, it is requires an algorithm implemented using VBA (i.e.

macro).

A couple have been mentioned in past discussions. I don't know if an
of
them find the "best"; and I don't know if any of them find "no les
than" or
if they find "about" (which might be less).

You might start with the VBA code mentioned at
http://www.sulprobil.com/html/accounts_receivable_problem.html.
Unfortunately, his webpage is difficult to understand, IMHO. But th
code
is usable.

Nevertheless, I have not really vetted the algorithm other than to tr
one
or two simple examples.

Good luck!

Hi, Sorry to make everyone confusing here.
Actually I want to know the fewest items that sum to about 80%. Attache
please find my current working workbook with formula

+-------------------------------------------------------------------
|Filename: sumif & countif.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=460
+-------------------------------------------------------------------

 

[joeu2004]

And do you want "about" 80% (which might be less); or
do you want "no less than" 80%? [....]
You might start with the VBA code mentioned at
http://www.sulprobil.com/html/accounts_receivable_problem.html.

[....]
Actually I want to know the fewest items that sum to about 80%.

The take-away from my previous posting is: this is a very difficult nut to
crack in general, and no simple Excel formula or single function is likely
to produce the desired result except by accident or by coincidence.

It requires an iterative algorithm, best implemented in VBA, IMHO. Even so,
I believe it is a very complex algorithm when designed correctly.

Think about it! Imagine that you have an empty box and a set of
oddly-shaped malleable shapes of varying size. You might start by picking
the largest ones (assuming they fit at all); but eventually you might need
to pick some of the smallest ones to fill the remaining space as best as
possible. Alternatively, you might find a set of shapes that overflows the
box, but by less than the unfilled space with the first set.

If you did this manually, there would be a lot of trial-and-error with a lot
of intuitive thinking going into the selection criteria. No different for a
computer algorithm.

Again, start with the aforementioned VBA code. It might do what you want as
is; if not, it might provide a good starting point for the desired
algorithm.

Good luck!